Google Fly Cup Challenge: Recruit
Hi, guys I am Keshav from TECH_ED, just make sure to subscribe the channel if you find this helpful.
Task 1:
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 = 'Mexico' (Make sure to do the changes as I am doing in video TECH_ED)
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 = '' (Make sure to do the changes as I am doing in video TECH_ED)
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 = '' AND name = 'Time Trials'
(Make sure to do the changes as I am doing in video TECH_ED)
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 != 'DNF'
AND
minimum_time != ''
(Make sure to do the changes as I am doing in video)
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' 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
(Make sure to do the changes as I am doing in video)
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' 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
(Make sure to do the changes as I am doing in video TECH_ED)
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
(Make sure to do the changes as I am doing in video TECH_ED)
Congrats for completing this lab, make sure to subscribe TECH_ED on youtube, If you've any further queries then please comment on our latest video will surely help :)