SQL Window Functions and CTEs

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL "30 days"
)
SELECT customer_id, COUNT(*) AS total_recent_orders
FROM recent_orders
GROUP BY customer_id;

# Create a named subquery for readability and reuse.

WITH order_totals AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
ranked AS (
  SELECT customer_id, total_spent
  FROM order_totals
)
SELECT *
FROM ranked;

# Chain multiple named steps in one query.

WITH RECURSIVE org_tree AS (
  SELECT employee_id, manager_id, name, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id, e.name, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON ot.employee_id = e.manager_id
)
SELECT *
FROM org_tree
ORDER BY depth, employee_id;

# Traverse hierarchical or graph-like data.

SELECT order_id, customer_id, created_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;

# Number rows within an ordered partition.

SELECT employee_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees;

# Assign ordered rankings with or without gaps.

SELECT order_id, created_at, amount,
       SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

# Accumulate values across an ordered window.

SELECT employee_id, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

# Compute metrics per partition while keeping each row.

SELECT day, total_sales,
       LAG(total_sales) OVER (ORDER BY day) AS previous_day_sales
FROM daily_sales;

# Read a value from the previous row in the same partition.

SELECT subscription_id, starts_at,
       LEAD(starts_at) OVER (PARTITION BY user_id ORDER BY starts_at) AS next_start
FROM subscriptions;

# Read a value from the next row in the same partition.

SELECT customer_id, order_id, amount,
       FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_order_amount
FROM orders;

# Return boundary values within a window frame.

SELECT customer_id, total_spent,
       NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_lifetime_value;

# Divide ordered rows into a fixed number of groups.

SELECT employee_id, salary,
       PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
       CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution
FROM employees;

# Measure relative row position inside an ordered set.