GCP – Big Query SQL
GCP – Big Query Samples
Below are some SQL samples where I have run some queries in Google Big Query against some of the current data I have (2022-10-19). I also wrote a Database (Hack My Health) in MS SQL Server 2019. I created 3 tables, inserted and queried the data. You can view more code samples and results on my Github Page – The Project
From the Vitals & Rides Data
— Sample ‘WHERE’ clause:
DATE | RIDE_TYPE | ROUTE | TIME | DISTANCE | AVG_SPEED | MAX_SPEED | AVG_CADENCE | MAX_CADENCE | AVG_HR | MAX_HR | BRPM_AVG_ | BRPM_MAX_ | ELAVATION_GAIN | AVG_POWER | MAX_POWER | KJ | LOAD | CALORIES_BURNED |
10/1/2022 | ROAD CYCLING | ZUBE GROUP | 1:54:52 | 29.41 | 15.4 | 21.7 | 77 | 106 | 331 | 87 | 1424 | |||||||
10/8/2022 | ROAD CYCLING | DJP | 2:15:31 | 32.14 | 14.2 | 26.4 | 147 | 175 | 620 | 84 | 1330 | |||||||
10/9/2022 | ROAD CYCLING | SUN SKI | 1:52:20 | 30.51 | 16.3 | 23.2 | 158 | 179 | 354 | 108 | 1483 | |||||||
10/15/2022 | ROAD CYCLING | ZUBE STOKES | 2:49:06 | 42.99 | 15.3 | 24.4 | 78 | 107 | 156 | 175 | 36 | 46 | 512 | 88 | 889 | 2096 | ||
10/19/2022 | TRAINER | AMSTERDAM | 1:16:38 | 21.72 | 17 | 22.6 | 95 | 114 | 164 | 175 | 0 | 0 | 157 | 102 | 218 | 472 | 101 | 471 |
— Sample JOIN
DATE | ROUTE | RIDE_TYPE | DISTANCE | AVG_SPEED | WEIGHT | BMI | LAST_SLEEP |
10/15/2022 | ZUBE STOKES | ROAD CYCLING | 42.99 | 15.3 | 173.1 | 25.6 | 7 |
10/18/2022 | BARCELONA | TRAINER | 13.06 | 15 | 171.5 | 25.4 | 7 |
10/19/2022 | AMSTERDAM | TRAINER | 21.72 | 17 | 172.6 | 25.6 | 6.25 |
— Big Query YouTube Samples
Join
SELECT DISTINCT
combined.date
,combined.video_id
,combined.country_code
,traffic_source_detail
,traffic.traffic_source_type
,traffic.views
,traffic.watch_time_minutes
FROM `portfolio-website-smb.YouTubeData.channel_combined_a2_YTD` AS combined
JOIN `portfolio-website-smb.YouTubeData.channel_traffic_source_a2_YTD` AS traffic
ON
(
Combined.date = traffic.date
)
ORDER BY combined.video_id
–– Multiple Joins
SELECT DISTINCT
combined.date
,combined.video_id
,combined.country_code
,traffic_source_detail
,traffic.traffic_source_type AS source_type
,traffic.views
,traffic.watch_time_minutes AS watch_minutes
,device.operating_system AS os
,device.device_type AS device
FROM `portfolio-website-smb.YouTubeData.channel_combined_a2_YTD` AS combined
JOIN `portfolio-website-smb.YouTubeData.channel_traffic_source_a2_YTD` AS traffic
JOIN `portfolio-website-smb.YouTubeData.channel_device_os_a2_YTD` AS device
ON
(
traffic.date = device.date
)
ON
(
combined.date = device.date
)
WHERE traffic.traffic_source_detail = ‘scottmbecker.com’
ORDER BY combined.video_id
To hire me click HERE
MY BRANDS:
To learn more about the Freelance Data Analyst and Cloud Engineering work I do, please visit XL Mobile Media
To learn more about the Digital Marketing and Analytics work I do, please visit XL Digital Media