Skip to content

Instantly share code, notes, and snippets.

@Mr--John-Doe
Mr--John-Doe / WindowFunctions-QueryC-TimeSinceEvent.sql
Created Jan 18, 2022
for every accident, show how much time (in hours) has passed since the last time the road became Wet/Damp.
View WindowFunctions-QueryC-TimeSinceEvent.sql
WITH relevant_set AS (
SELECT *
-- has_started_raining means: (previous!=Wet AND current=Wet)
, LAG(road_cond) OVER (ORDER BY accident_ts, police_ref) AS prev_road_cond
, prev_road_cond != '2. Wet/Damp' AND road_cond = '2. Wet/Damp' AS has_started_raining
, CASE WHEN has_started_raining THEN accident_ts END AS time_started_raining
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
)
SELECT accident_ts
@Mr--John-Doe
Mr--John-Doe / WindowFunctions-QueryB-WithRoundingIssues.sql
Last active Jan 17, 2022
WindowFunctions-QueryB-WithRoundingIssues
View WindowFunctions-QueryB-WithRoundingIssues.sql
SELECT day AS day_of_week
, COUNT(*) AS dow_accidents
, SUM(dow_accidents) OVER (PARTITION BY 0) AS total_accidents
, ROUND(dow_accidents / total_accidents, 4) AS dow_accidents_percent
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
WHERE accident_ts BETWEEN '2017-01-01'::date AND '2017-01-31'::date
GROUP BY 1
@Mr--John-Doe
Mr--John-Doe / snowflake_table_lineage.sql
Last active Jan 14, 2022
Gets a list of source/target tables for any query (only available if bundle 2021_10 is enabled)
View snowflake_table_lineage.sql
WITH table_lineage_history AS (
SELECT r.value:"objectName"::varchar AS upstream_table_name
, r.value:"objectDomain"::varchar AS upstream_table_domain
, r.value:"columns" AS upstream_table_columns
, w.value:"objectName"::varchar AS downstream_table_name
, w.value:"objectDomain"::varchar AS downstream_table_domain
, w.value:"columns" AS downstream_table_columns
, t.query_start_time AS query_start_time
, t.QUERY_ID AS query_id
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY t
View load-cambridge-road-traffic-collisions.sql
create or replace TEMPORARY TABLE temp.CAMBRIDGESHIRE_ACCIDENTS (
DATE VARCHAR,
TIME VARCHAR,
POLICE_REF VARCHAR,
SEVERITY VARCHAR,
ROAD_COND VARCHAR,
VISIBILITY VARCHAR,
CASUALTIES VARCHAR,
PEDESTRIAN VARCHAR,
CYCLES VARCHAR,
View WindowFunctions-QueryA2-AggLag.sql
WITH prep AS (
SELECT *
, LAG(accident_ts) OVER (PARTITION BY severity ORDER BY accident_ts) AS previous_accident_ts
, DATEDIFF(HOUR, previous_accident_ts, accident_ts) AS time_between_accidents
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
)
SELECT severity
, AVG(time_between_accidents) AS avg_time_between_accidents_by_severity
FROM prep
GROUP BY severity
View WindowFunctions-QueryB-ResolvingRoundingIssues.sql
WITH prep AS (
SELECT day AS day_of_week
, COUNT(*) AS dow_accidents
, SUM(dow_accidents) OVER (PARTITION BY 0) AS total_accidents
/* Create a column with the percentage calc, but make it null on Saturday */
, CASE
WHEN day_of_week != '7. Saturday'
THEN ROUND(dow_accidents / total_accidents, 4) END AS prep_accidents_percent
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
@Mr--John-Doe
Mr--John-Doe / WindowFunctions-QueryA-WithWindowFunc.sql
Last active Jan 14, 2022
WindowFunctions-QueryA-WithWindowFunc
View WindowFunctions-QueryA-WithWindowFunc.sql
SELECT DATE AS DATE
, police_ref AS police_ref
, casualties AS casualties
, SUM(casualties) OVER (PARTITION BY DATE) AS day_total_casualties
, casualties / day_total_casualties AS casualties_percent_of_day --using lateral alias: day_total_casualties
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
@Mr--John-Doe
Mr--John-Doe / WindowFunctions-QueryA-NoWindowFunc.sql
Last active Jan 14, 2022
WindowFunctions-QueryA-NoWindowFunc
View WindowFunctions-QueryA-NoWindowFunc.sql
SELECT row_level.DATE AS date
, row_level.police_ref AS police_ref
, row_level.casualties AS casualties
, agg.day_total_casualties AS day_total_casualties
, row_level.casualties / agg.day_total_casualties AS casualties_percent_of_day
FROM temp.CAMBRIDGESHIRE_ACCIDENTS row_level
INNER JOIN (SELECT date, SUM(casualties) AS day_total_casualties
FROM temp.CAMBRIDGESHIRE_ACCIDENTS
GROUP BY DATE) agg
ON row_level.date = agg.date
@Mr--John-Doe
Mr--John-Doe / window_func_last_row.sql
Last active Dec 22, 2021
Filter the last row of a query
View window_func_last_row.sql
SELECT *
, ROW_NUMBER() OVER (PARTITION BY client ORDER BY sale_date DESC) AS rownum_desc
FROM sales_table
QUALIFY rownum_desc = 1
@Mr--John-Doe
Mr--John-Doe / window_functions_basics_2.sql
Created Dec 9, 2021
windows function - basics - example#2
View window_functions_basics_2.sql
WITH agg AS (
SELECT depname, AVG(salary) avg_salary
FROM empsalary
GROUP BY depname
)
SELECT depname, empno, salary, avg_salary
FROM empsalary
INNER JOIN agg
USING (depname)