SQL window functions cheat sheet

function(args) OVER (
  [PARTITION BY col_a, col_b]      -- group rows; analogous to GROUP BY
  [ORDER BY col_t [ASC|DESC]]      -- sort within each partition
  [ROWS|RANGE BETWEEN ... AND ...] -- frame: which rows in the partition to operate on
)

SELECT
  user_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn,    -- 1, 2, 3, 4
  RANK()       OVER (PARTITION BY user_id ORDER BY total DESC) AS rk,    -- 1, 2, 2, 4 (ties skip)
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS dr     -- 1, 2, 2, 3 (ties don't skip)
FROM orders;

-- 3 most recent orders per user.
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
) ranked
WHERE rn <= 3;

-- DuckDB / Snowflake / BigQuery / Databricks: use QUALIFY to skip the wrapper.
SELECT *
FROM   orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) <= 3;

SELECT
  day,
  revenue,
  LAG(revenue)  OVER (ORDER BY day) AS prev_day,
  LEAD(revenue) OVER (ORDER BY day) AS next_day,
  revenue - LAG(revenue) OVER (ORDER BY day) AS day_over_day_delta,
  LAG(revenue, 7)    OVER (ORDER BY day) AS revenue_week_ago,
  LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_or_zero       -- default for first row
FROM daily_revenue;

SELECT
  region,
  user_id,
  revenue,
  -- Group total without collapsing rows.
  SUM(revenue)   OVER (PARTITION BY region)                   AS region_total,
  -- Per-row percentage of region total.
  100.0 * revenue / SUM(revenue) OVER (PARTITION BY region)   AS pct_of_region,
  -- Running total within region, ordered by date.
  SUM(revenue)   OVER (PARTITION BY region ORDER BY day)      AS running_total,
  -- Average so far for that region.
  AVG(revenue)   OVER (PARTITION BY region ORDER BY day)      AS running_avg,
  -- Min / Max so far.
  MIN(revenue)   OVER (PARTITION BY region ORDER BY day)      AS region_low_so_far,
  MAX(revenue)   OVER (PARTITION BY region ORDER BY day)      AS region_high_so_far
FROM region_daily;

-- Last 7 rows (rolling 7-day if the data is one row per day).
SUM(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- 30-day moving average.
AVG(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
)

-- Forward-looking: this row plus next 4.
SUM(revenue) OVER (
  ORDER BY day
  ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING
)

-- Whole partition (e.g., for LAST_VALUE to actually return the last).
LAST_VALUE(revenue) OVER (
  PARTITION BY region ORDER BY day
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

-- First and last revenue in each region (sorted by date).
SELECT
  region,
  day,
  revenue,
  FIRST_VALUE(revenue) OVER (PARTITION BY region ORDER BY day)               AS first_day_rev,
  LAST_VALUE(revenue)  OVER (PARTITION BY region ORDER BY day
                              ROWS BETWEEN UNBOUNDED PRECEDING
                                       AND UNBOUNDED FOLLOWING)              AS last_day_rev,
  NTH_VALUE(revenue, 2) OVER (PARTITION BY region ORDER BY day)              AS second_day_rev
FROM region_daily;

SELECT
  user_id,
  revenue,
  NTILE(4)   OVER (ORDER BY revenue) AS quartile,
  NTILE(10)  OVER (ORDER BY revenue) AS decile,
  NTILE(100) OVER (ORDER BY revenue) AS percentile
FROM user_revenue;

SELECT
  value,
  PERCENT_RANK() OVER (ORDER BY value) AS pct_rank,        -- 0 to 1
  CUME_DIST()    OVER (ORDER BY value) AS cumulative_dist  -- 0 to 1
FROM measurements;

-- As an aggregate (one row per group): WITHIN GROUP form.
SELECT
  region,
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY revenue) AS median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY revenue) AS p95
FROM   user_revenue
GROUP  BY region;

-- As a window function: per-row.
SELECT
  region, user_id, revenue,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue)
    OVER (PARTITION BY region) AS region_median
FROM user_revenue;

SELECT
  user_id,
  total,
  ROW_NUMBER() OVER w AS rn,
  RANK()       OVER w AS rk,
  LAG(total)   OVER w AS prev_total
FROM   orders
WINDOW w AS (PARTITION BY user_id ORDER BY order_date);     -- Postgres / MySQL 8 / DuckDB / SQLite / BigQuery / SQL Server 2022+ / Oracle 21c+ (not Snowflake)