Mr--John-Doe

DELETE FROM SCHEMA.TABLE_XYZ;
INSERT INTO SCHEMA.TABLE_XYZ(sale_id, user_id, sale_value, no_of_items)
SELECT a.sale_id
     , a.user_id
     , a.sale_value
     , COALESCE(CASE
                   WHEN a.product = 310 THEN 90
                   WHEN a.product = 311 THEN 10
                   WHEN a.product = 312 THEN 50
                   WHEN X.type = 'Month' THEN X.quantity * 30

Mr--John-Doe

DELETE FROM SCHEMA.TABLE_XYZ;

INSERT INTO SCHEMA.TABLE_XYZ(sale_id, user_id, sale_value, no_of_items)
WITH voucher_redemption_summary AS (
   SELECT food_voucher.redeem_id, prod.product, COUNT(*) AS quantity
   FROM SCHEMA.tableA as food_voucher
      JOIN SCHEMA.tableB as voucher_info
           ON voucher_info.id = food_voucher.redeem_id
      JOIN SCHEMA.tableC as prod
           ON prod.externalId = voucher_info.product_id

Mr--John-Doe

DELETE FROM SCHEMA.TABLE_XYZ;

INSERT INTO SCHEMA.TABLE_XYZ(sale_id, user_id, sale_value, no_of_items)
WITH voucher_redemption_summary AS (
   SELECT food_voucher.redeem_id, prod.product, COUNT(*) AS quantity
   FROM SCHEMA.tableA as food_voucher
      JOIN SCHEMA.tableB as voucher_info
           ON voucher_info.id = food_voucher.redeem_id
      JOIN SCHEMA.tableC as prod
           ON prod.externalId = voucher_info.product_id

Mr--John-Doe

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

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 *
     , ROW_NUMBER() OVER (PARTITION BY client ORDER BY sale_date DESC) AS rownum_desc
FROM sales_table
   QUALIFY rownum_desc = 1

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_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

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

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