Mr--John-Doe

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

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

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

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

Mr--John-Doe

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

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

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,

Mr--John-Doe

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

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)

Mr--John-Doe

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) avg_salary
FROM empsalary