SQL (Snowflake): Aggregation and Window Functions Cheatsheet with Quiz

SELECT order_id, customer_id, amount
FROM ORDERS
WHERE status = 'completed' AND amount > 100
ORDER BY amount DESC
LIMIT 10;

SELECT DISTINCT customer_id FROM ORDERS;

SELECT COUNT(*)                    AS orders,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(amount)                 AS revenue,
       AVG(amount)                 AS avg_order,
       MIN(amount), MAX(amount),
       MEDIAN(amount)
FROM ORDERS
WHERE status = 'completed';

SELECT customer_id, SUM(amount) AS total_spent, COUNT(*) AS orders
FROM ORDERS
WHERE status = 'completed'      -- rows, before grouping
GROUP BY customer_id
HAVING SUM(amount) > 300        -- groups, after aggregation
ORDER BY total_spent DESC;

SELECT customer_id,
       SUM(IFF(status = 'completed', amount, 0))                  AS completed_revenue,
       SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END)  AS pending_revenue,
       COUNT_IF(status = 'cancelled')                             AS cancelled_orders,
       COUNT_IF(status = 'completed') * 100.0 / NULLIF(COUNT(*), 0) AS completion_pct
FROM ORDERS
GROUP BY customer_id;

DATE_TRUNC('MONTH', order_date)        -- 2026-01-01 for any January date
YEAR(order_date), MONTH(order_date), DAYNAME(order_date)
DATEDIFF(DAY, first_date, last_date)
DATEADD(DAY, -7, CURRENT_DATE)
order_date::DATE

GROUP BY ROLLUP(region, product)      -- (region, product), (region), ()
GROUP BY CUBE(region, product)        -- every combination, including ()
GROUP BY GROUPING SETS ((region), (product), ())
GROUPING(region)                      -- 1 on the rows where region was aggregated away

SELECT c.name, o.amount
FROM CUSTOMERS c
LEFT JOIN ORDERS o ON o.customer_id = c.customer_id;   -- keep every customer

SELECT c.customer_id
FROM CUSTOMERS c
WHERE NOT EXISTS (SELECT 1 FROM ORDERS o WHERE o.customer_id = c.customer_id);

WITH customer_revenue AS (
  SELECT customer_id, SUM(amount) AS revenue
  FROM ORDERS
  GROUP BY customer_id
),
avg_revenue AS (SELECT AVG(revenue) AS avg_rev FROM customer_revenue)
SELECT c.*
FROM customer_revenue c, avg_revenue a
WHERE c.revenue > a.avg_rev;

SELECT employee, department, salary,
       AVG(salary) OVER (PARTITION BY department)                   AS dept_avg,     -- per group, every row kept
       AVG(salary) OVER ()                                          AS company_avg,  -- whole table, on every row
       SUM(salary) OVER (PARTITION BY department ORDER BY salary)   AS running
FROM EMPLOYEES;

ROW_NUMBER()   OVER (PARTITION BY department ORDER BY salary DESC)  -- 1, 2, 3, 4, 5  (always unique)
RANK()         OVER (ORDER BY salary DESC)                           -- 1, 1, 3, 4, 4  (gaps after ties)
DENSE_RANK()   OVER (ORDER BY salary DESC)                           -- 1, 1, 2, 3, 3  (no gaps)
NTILE(4)       OVER (ORDER BY salary DESC)                           -- bucket 1–4 (quartiles)
PERCENT_RANK() OVER (ORDER BY salary DESC)                           -- (rank − 1) / (rows − 1)

SELECT *
FROM ORDERS
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) = 1;

SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  -- running total
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)          -- 3-row moving sum
LAG(amount)  OVER (PARTITION BY customer_id ORDER BY order_date)    -- previous row (NULL on the first)
LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date)    -- next row (NULL on the last)
FIRST_VALUE(product_id) OVER (PARTITION BY customer_id ORDER BY order_date)   -- first purchase
amount * 100.0 / SUM(amount) OVER ()                                -- share of the total

SELECT data:customer_id::NUMBER   AS customer_id,
       f.value:id::NUMBER         AS product_id,
       f.value:price::NUMBER      AS price
FROM RAW_ORDERS,
LATERAL FLATTEN(INPUT => data:products) f;

PARSE_JSON(raw_text)                                       -- string → VARIANT
ARRAY_AGG(DISTINCT product_id)                             -- rows → array
LISTAGG(product_id, ', ') WITHIN GROUP (ORDER BY product_id)  -- rows → string

MERGE INTO ORDERS t
USING (SELECT * FROM STAGING_ORDERS
       QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount, t.status = s.status
WHEN NOT MATCHED THEN INSERT (order_id, amount, status) VALUES (s.order_id, s.amount, s.status);

SELECT * FROM (SELECT customer_id, status, amount FROM ORDERS)
PIVOT (SUM(amount) FOR status IN ('completed', 'pending', 'cancelled'));

SELECT DATEADD(DAY, SEQ4(), '2026-01-01'::DATE) AS day
FROM TABLE(GENERATOR(ROWCOUNT => 365));