The Google Cloud Fly Cup : Recruit Lab Solution

 

Google Cloud Fly Cup

Challenge : Recruit Lab Solution

Register Now:-  The Google Cloud Fly Cup Challenge 2022

    Watch Now:- https://www.youtube.com/watch?v=k3ZlewVy6rE

Recruit Lab Solution:-

 

Task1:
	bq mk drl
	for file in `gsutil ls gs://spls/gsp394/tables/*.csv`; do TABLE_NAME=`echo $file | cut -d '/' -f6 | cut -d '.' -f1`; bq load --autodetect --source_format=CSV --replace=true drl.$TABLE_NAME $file; done
	Task 2:
	SELECT name FROM `drl.events` WHERE city = 'Phoenix' (Make changes as Your Lab)
	Task 3:
	SELECT `drl.pilots`.name, `drl.event_pilots`.id FROM `drl.event_pilots` LEFT JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id
	Task 4:
	SELECT `drl.pilots`.name, `drl.events`.name AS event_name FROM `drl.event_pilots` LEFT OUTER JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id LEFT OUTER JOIN `drl.events` ON `drl.events`.id = `drl.event_pilots`.event_id WHERE `drl.events`.name = 'California Nights' (Make changes as your Lab)
	Task 5:
	WITH cte AS (SELECT `drl.round_standings`.minimum_time FROM `drl.round_standings` WHERE `rank` = 1)
	SELECT time
	(timestamp_seconds
	(CAST
	(AVG
	(UNIX_SECONDS
	(PARSE_TIMESTAMP('%H:%M.%S', minimum_time))
	)
	AS INT64)
	)
	)
	AS avg FROM cte
	Task 6:
	CREATE TABLE drl.time_trial_cleaned AS (
	SELECT
	`drl.time_trial_group_pilot_times`.id AS time_trial_group_pilot_times_id,
	`drl.time_trial_group_pilots`.id AS time_trial_group_pilot_id,
	`drl.time_trial_groups`.id AS time_trial_group_id,
	round_id,
	CASE
	WHEN `drl.time_trial_group_pilot_times`.time_adjusted IS NOT null then `drl.time_trial_group_pilot_times`.time_adjusted
	WHEN `drl.time_trial_groups`.racestack_scoring = 0 then `drl.time_trial_group_pilot_times`.time
	ELSE`drl.time_trial_group_pilot_times`.racestack_time
	END
	AS time
	FROM `drl.time_trial_group_pilot_times` LEFT OUTER JOIN `drl.time_trial_group_pilots` ON `drl.time_trial_group_pilot_times`.time_trial_group_pilot_id = `drl.time_trial_group_pilots`.id LEFT OUTER JOIN `drl.time_trial_groups` ON `drl.time_trial_group_pilots`.time_trial_group_id = `drl.time_trial_groups`.id
	)
	Task 7 :
	WITH cte AS
	(SELECT
	`drl.rounds`.event_id,
	`drl.rounds`.name,
	`drl.events`.name AS event_name,
	time
	FROM `drl.time_trial_cleaned`
	LEFT OUTER JOIN `drl.rounds` ON `drl.time_trial_cleaned`.round_id = `drl.rounds`.id
	LEFT OUTER JOIN `drl.events` ON `drl.events`.id = `drl.rounds`.event_id)
	SELECT MIN(time) as fastest_time FROM cte WHERE event_name = 'California Nights' (Make changes as your Lab) AND name = 'Time Trials'
	Task 8:
	SELECT
	`drl.pilots`.name AS pilot_name,
	`drl.heat_standings`.heat_id AS heat_id,
	`drl.heat_standings`.minimum_time,
	`drl.heat_standings`.points
	FROM `drl.heat_standings`
	LEFT JOIN `drl.event_pilots` ON `drl.event_pilots`.id = event_pilot_id
	LEFT JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id
	WHERE
	name = 'NURK'
	AND
	minimum_time != 'NURK'
	AND
	minimum_time != ''
	(Make changes as your Lab)
	Task 9 :
	WITH cte AS
	(SELECT `drl.pilots`.name, `drl.heat_standings`.heat_id, `drl.heat_standings`.points, `drl.heat_standings`.minimum_time
	FROM `drl.heat_standings`
	LEFT JOIN `drl.event_pilots` ON `drl.event_pilots`.id = event_pilot_id
	LEFT JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id
	WHERE name = 'NURK' (Make changes as described in the video) AND minimum_time != 'DNF' AND minimum_time != '')
	SELECT
	name AS pilot_name,
	heat_id
	minimum_time,
	points,
	time
	(timestamp_seconds
	(CAST
	(AVG
	(UNIX_SECONDS
	(PARSE_TIMESTAMP('%H:%M.%S', minimum_time))
	)
	OVER (ORDER BY heat_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
	AS INT64)
	)
	)
	AS running_avg
	FROM cte
	Task 10:
	WITH cte AS
	(SELECT
	`drl.pilots`.name,
	`drl.heat_standings`.heat_id,
	`drl.heat_standings`.points,
	`drl.heat_standings`.minimum_time
	FROM `drl.heat_standings`
	LEFT JOIN `drl.event_pilots` ON `drl.event_pilots`.id = event_pilot_id
	LEFT JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id
	WHERE name = 'NURK' (Make changes as your Lab) AND minimum_time != 'DNF' AND minimum_time != ''),
	cte2 AS
	(SELECT
	name AS pilot_name,
	heat_id,
	minimum_time,
	points,
	time
	(timestamp_seconds
	(CAST
	(AVG
	(UNIX_SECONDS
	(PARSE_TIMESTAMP('%H:%M.%S', minimum_time))
	)
	OVER (ORDER BY heat_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
	AS INT64)
	)
	)
	AS running_avg FROM cte)
	SELECT *,
	TIME_DIFF(PARSE_TIME('%H:%M.%S', minimum_time), running_avg, SECOND) as time_diff_from_avg FROM cte2
	Task 11:
	WITH cte AS
	(SELECT
	`drl.pilots`.name,
	`drl.heat_standings`.heat_id,
	`drl.heat_standings`.points,
	`drl.heat_standings`.minimum_time
	FROM `drl.heat_standings`
	LEFT JOIN `drl.event_pilots` ON `drl.event_pilots`.id = event_pilot_id
	LEFT JOIN `drl.pilots` ON `drl.pilots`.id = `drl.event_pilots`.pilot_id
	WHERE points != 0 AND minimum_time != 'DNF' AND minimum_time != ''),
	cte2 AS
	(SELECT
	name AS pilot_name,
	heat_id,
	minimum_time,
	points,
	time
	(timestamp_seconds
	(CAST
	(AVG
	(UNIX_SECONDS
	(PARSE_TIMESTAMP('%H:%M.%S', minimum_time))
	)
	OVER (ORDER BY heat_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
	AS INT64)
	)
	)
	AS running_avg FROM cte)
	SELECT *,
	TIME_DIFF(PARSE_TIME('%H:%M.%S', minimum_time), running_avg, SECOND) as time_diff_from_avg FROM cte2
	Footer Please Like and share Pallaviwebmaster