GCP – Big Query SQL

Posted on by admin

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:

SELECT *
FROM `portfolio-website-smb.OCT_STATS.oct_stats`
WHERE DISTANCE > 20.0
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

SELECT stats.DATE
,ROUTE
,RIDE_TYPE
,DISTANCE
,AVG_SPEED
,vitals.WEIGHT
,vitals.BMI__ as BMI
,vitals.LAST_SLEEP
 FROM `portfolio-website-smb.OCT_STATS.oct_stats` as stats
 JOIN `portfolio-website-smb.OCT_VITALS.oct_vitals` as vitals
 ON (
stats.DATE = vitals.DATE
 )
WHERE vitals.DATE > ‘2022-10-13’ AND LAST_SLEEP > 6
ORDER BY DATE
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