SQL for Data Analysis
Analytical SQL turns raw transactional rows into answers: rankings, trends, shares, cohorts, and pivot-style reports.
Concept
Analytical SQL turns raw transactional rows into answers: rankings, trends, shares, cohorts, and pivot-style reports. Most analytical questions map to a small set of reusable patterns built from GROUP BY, CASE, CTEs, and window functions. This module uses the course sample database (Modules 1–22).
Why It Matters
Data analysts, data engineers, product managers, and testers use these patterns daily. They are also the core of "SQL take-home" and live-coding interviews.
Syntax
Top-N / Bottom-N ORDER BY... LIMIT/FETCH or ROW_NUMBER() Per-group Top-N, latest/first per group
ROW_NUMBER() OVER (PARTITION BY... ORDER BY...) GROUP BY... HAVING COUNT(*) > 1
Anti-join (NOT EXISTS, LEFT JOIN ... IS NULL), calendar scaffolds
SUM() OVER (ORDER BY... ROWS UNBOUNDED PRECEDING) x / SUM(x) OVER () GROUP BY date truncation
Conditional aggregation / PIVOT / crosstab UNION ALL / UNPIVOT / LATERAL (VALUES...)
Example
37.1 Top-N and Bottom-N records 37.2 Duplicate detection (Module 39 covers duplicates in depth.) 37.3 Missing records
Missing IDs in a sequence (gaps in invoice numbers):
37.4 Latest record per group and first record per group Alternatives: DISTINCT ON (customer_id)... ORDER BY customer_id, order_date DESC (PostgreSQL only), a correlated MAX() subquery, or FIRST_VALUE.
37.5 Running totals and percentage calculations SUM(SUM(x)) OVER () is a window function applied over the grouped result: the inner SUM is the group aggregate; the outer SUM totals it across all groups. This is the standard "share of total" and Pareto (80/20) pattern.
37.7 Monthly and yearly reports; trend analysis Monthly grouping per DBMS:
DATE_TRUNC('month', d) DATE_FORMAT(d, '%Y-%m-01') DATEFROMPARTS(YEAR(d), MONTH(d), 1) or DATETRUNC(month, d) (2022+) TRUNC(d, 'MM') strftime('%Y-%m-01', d)
Yearly report: replace the month key with the year (EXTRACT(YEAR FROM order_date) / YEAR(order_date)). Year-over-year comparison of the same month uses LAG(revenue, 12) over a complete monthly series.
37.8 Conditional aggregation and pivot concepts Pivot = turn row values into columns.
Native pivot syntax:
Native PIVOT returns NULL (not 0) for empty cells; wrap with COALESCE if needed. The list of pivot columns must be known in advance; dynamic pivots require dynamic SQL.
37.9 Unpivot concepts Unpivot = turn columns into rows (e.g., normalize a spreadsheet-shaped table).
(UNPIVOT drops rows whose value is NULL; the UNION ALL and LATERAL forms keep them.)
-- Top 3 orders by value
SELECT order_id, customer_id, total_amount
FROM orders
ORDER BY total_amount DESC, order_id
FETCH FIRST 3 ROWS ONLY; -- LIMIT 3 (MySQL/PostgreSQL/SQLite); TOP (3) (SQL Server)-- Bottom 2 products by revenue (including unsold products)
SELECT p.product_name, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue ASC, p.product_name
FETCH FIRST 2 ROWS ONLY;-- Employees sharing the same salary
SELECT salary, COUNT(*) AS n, STRING_AGG(first_name, ', ') AS employees
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;-- Customers without orders (anti-join)
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); -- Elena Rossi
-- Months in Q1 2026 without any DELIVERED order (calendar scaffold)
WITH months AS (
SELECT DATE '2026-01-01' AS m UNION ALL
SELECT DATE '2026-02-01' UNION ALL
SELECT DATE '2026-03-01'
)
SELECT months.m
FROM months
LEFT JOIN orders o
ON o.status = 'DELIVERED'
AND o.order_date >= months.m
AND o.order_date < months.m + INTERVAL '1 month' -- PostgreSQL
WHERE o.order_id IS NULL;SELECT order_id + 1 AS gap_starts_at
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.order_id = o.order_id + 1)
AND o.order_id < (SELECT MAX(order_id) FROM orders);
-- Sample data: no rows (1001–1006 are contiguous)WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn_latest,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC, order_id ASC) AS rn_first
FROM orders o
)
SELECT customer_id,
MAX(CASE WHEN rn_first = 1 THEN order_id END) AS first_order,
MAX(CASE WHEN rn_latest = 1 THEN order_id END) AS latest_order,
MAX(CASE WHEN rn_latest = 1 THEN total_amount END) AS latest_amount
FROM ranked
GROUP BY customer_id
ORDER BY customer_id;SELECT p.category,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER (), 2) AS pct_of_total,
SUM(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY SUM(oi.quantity * oi.unit_price) DESC
ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id AND o.status <> 'CANCELLED'
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;SELECT c.name,
SUM(o.total_amount) AS spent,
RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spend_rank,
NTILE(2) OVER (ORDER BY SUM(o.total_amount) DESC) AS half
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'CANCELLED'
GROUP BY c.customer_id, c.name
ORDER BY spend_rank;WITH monthly AS (
SELECT DATE_TRUNC('month', order_date)::DATE AS month,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE status <> 'CANCELLED'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month, orders, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS mom_growth_pct,
SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS ytd_revenue
FROM monthly
ORDER BY month;-- Revenue by category (rows) and month (columns) — portable conditional aggregation
SELECT p.category,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 1 THEN oi.quantity * oi.unit_price ELSE 0 END) AS jan,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 2 THEN oi.quantity * oi.unit_price ELSE 0 END) AS feb,
SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 3 THEN oi.quantity * oi.unit_price ELSE 0 END) AS mar,
SUM(oi.quantity * oi.unit_price) AS total
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id AND o.status <> 'CANCELLED'
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY p.category;Expected Result
Share of revenue by country (non-cancelled orders):
SELECT c.country, SUM(o.total_amount) AS revenue,
ROUND(100.0 * SUM(o.total_amount) / SUM(SUM(o.total_amount)) OVER (), 1) AS pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'CANCELLED'
GROUP BY c.country
ORDER BY revenue DESC;Common Mistakes
Percentages with integer division (100 * a / b on integers).
Forgetting cancelled/refunded records.
Month-over-month using a series with missing months (LAG compares to the wrong month); build a complete calendar first.
Using RANK when exactly one row per group is required.
Pivot columns hard-coded and silently missing new categories.
Best Practices
Define metrics once (views) so every report agrees.
Build complete date scaffolds for time series.
Use NULLIF in denominators.
Document the grain and filters of every report query.
Interview Relevance
Very high for analyst/data roles: Top-N per group, MoM growth, running totals, percent of total, pivot with CASE, latest record per group.
Practice Questions
Top 2 customers per country by revenue.
For each product, its share of category revenue.
Cumulative number of orders per day.
Pivot order counts by status (columns) and month (rows).
Unpivot a table sensor_daily(sensor_id, min_val, avg_val, max_val) into (sensor_id, stat, value).