Use CTEs, recursive queries, ranking, running totals, partitions, and lag/lead patterns for analytical SQL.
Write named subqueries with WITH.
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;CTEs make multi-step queries easier to read and maintain.
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;Multiple CTEs are useful for staged transformations.
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;Recursive CTEs are powerful for trees, nested categories, and parent-child traversal.
Use OVER clauses without collapsing rows.
SELECT order_id, customer_id, created_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders;ROW_NUMBER is useful for deduping and top-N-per-group queries.
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;RANK leaves gaps after ties; DENSE_RANK does not.
Accumulate values across an ordered window.
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;Running totals are common in finance and time-series analysis.
Compute metrics per partition while keeping each row.
SELECT employee_id, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;Window functions differ from GROUP BY because they keep row-level detail.
Compare rows to earlier or later rows.
SELECT day, total_sales,
LAG(total_sales) OVER (ORDER BY day) AS previous_day_sales
FROM daily_sales;LAG is great for deltas, trend analysis, and change detection.
SELECT subscription_id, starts_at,
LEAD(starts_at) OVER (PARTITION BY user_id ORDER BY starts_at) AS next_start
FROM subscriptions;LEAD helps compare a row to the following event.
Return boundary values within a window frame.
SELECT customer_id, order_id, amount,
FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_order_amount
FROM orders;Window frame details affect LAST_VALUE behavior in some dialects.
Segment rows into bands or percentile-like groups.
Divide ordered rows into a fixed number of groups.
SELECT customer_id, total_spent,
NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_lifetime_value;NTILE is helpful for quartiles, deciles, and score bands.
Measure relative row position inside an ordered set.
SELECT employee_id, salary,
PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank,
CUME_DIST() OVER (ORDER BY salary) AS cumulative_distribution
FROM employees;Useful for reporting on distribution and percentile-like placement.