|
/*
Describe the data in the all_geoclustered table.
*/
SELECT
COUNT(*) AS total_rows,
COUNT(*) FILTER (WHERE date IS NOT NULL) AS total_rows_with_date,
COUNT(*) FILTER (WHERE wban IS NOT NULL) AS total_rows_with_wban,
COUNT(*) FILTER (WHERE stn IS NOT NULL) AS total_rows_with_stn,
COUNT(*) FILTER (WHERE year IS NOT NULL) AS total_rows_with_year,
COUNT(*) FILTER (WHERE mo IS NOT NULL) AS total_rows_with_mo,
COUNT(*) FILTER (WHERE da IS NOT NULL) AS total_rows_with_da,
COUNT(*) FILTER (WHERE temp IS NOT NULL) AS total_rows_with_temp,
COUNT(*) FILTER (WHERE count_temp IS NOT NULL) AS total_rows_with_count_temp,
COUNT(*) FILTER (WHERE dewp IS NOT NULL) AS total_rows_with_dewp,
COUNT(*) FILTER (WHERE count_dewp IS NOT NULL) AS total_rows_with_count_dewp,
COUNT(*) FILTER (WHERE slp IS NOT NULL) AS total_rows_with_slp,
COUNT(*) FILTER (WHERE count_slp IS NOT NULL) AS total_rows_with_count_slp,
COUNT(*) FILTER (WHERE stp IS NOT NULL) AS total_rows_with_stp,
COUNT(*) FILTER (WHERE count_stp IS NOT NULL) AS total_rows_with_count_stp,
COUNT(*) FILTER (WHERE visib IS NOT NULL) AS total_rows_with_visib,
COUNT(*) FILTER (WHERE count_visib IS NOT NULL) AS total_rows_with_count_visib,
COUNT(*) FILTER (WHERE wdsp IS NOT NULL) AS total_rows_with_wdsp,
COUNT(*) FILTER (WHERE count_wdsp IS NOT NULL) AS total_rows_with_count_wdsp,
COUNT(*) FILTER (WHERE mxpsd IS NOT NULL
/*
How often do hail and thunder happen together?
*/
SELECT
COUNT(*) AS count,
SUM(IF(hail=1 AND thunder=1, 1, 0)) AS sum_hail_thunder
FROM
`fh-bigquery.weather_gsod.all`
WHERE
year = 2019
AND mo = 11
AND da = 14
AND stn = '725030'
|