SQL for Data Analysis: GROUP BY, JOINs and Window Functions

Excel stops being comfortable around a few hundred thousand rows. Company data lives in databases with millions — and the language to ask them questions is SQL. It is the single most tested skill in analyst interviews, so this lesson focuses on exactly the queries that come up.

Our schema

customers(customer_id, name, city, signup_date)
orders(order_id, customer_id, order_date, status)          -- status: delivered / cancelled / returned
order_items(order_id, product_id, qty, price)
products(product_id, name, category)

1. Filter and sort

SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-10-01' AND order_date < '2026-11-01'
ORDER BY order_date DESC
LIMIT 10;

Use >= start and < next-start for date ranges. BETWEEN '2026-10-01' AND '2026-10-31' quietly misses orders placed during the day on the 31st when the column stores time.

2. Aggregate with GROUP BY

-- Revenue and number of orders per category in October
SELECT p.category,
       COUNT(DISTINCT oi.order_id)  AS orders,
       SUM(oi.qty * oi.price)       AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN orders   o ON o.order_id   = oi.order_id
WHERE o.status = 'delivered' AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01'
GROUP BY p.category
ORDER BY revenue DESC;

Rule: every column in SELECT is either in GROUP BY or inside an aggregate (COUNT, SUM, AVG, MIN, MAX).

3. WHERE vs HAVING

-- Cities with more than 500 delivered orders
SELECT c.city, COUNT(*) AS orders
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'          -- filters rows BEFORE grouping
GROUP BY c.city
HAVING COUNT(*) > 500;                -- filters groups AFTER grouping

4. JOINs — and the one that finds what is missing

• INNER JOIN — only rows that match in both tables.
• LEFT JOIN — every row from the left table, with matches if they exist (NULL if not).

-- Customers who signed up but never ordered
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

5. CASE: categories inside the query

SELECT CASE WHEN total >= 20000 THEN 'High'
            WHEN total >= 5000  THEN 'Medium'
            ELSE 'Low' END        AS order_band,
       COUNT(*)                   AS orders
FROM (SELECT order_id, SUM(qty * price) AS total FROM order_items GROUP BY order_id) t
GROUP BY order_band;

6. Window functions: the interview favourite

A window function calculates across related rows without collapsing them into one row like GROUP BY does.

-- Top 3 products by revenue in each category
WITH product_revenue AS (
  SELECT p.category, p.name, SUM(oi.qty * oi.price) AS revenue
  FROM order_items oi JOIN products p ON p.product_id = oi.product_id
  GROUP BY p.category, p.name
)
SELECT *
FROM (
  SELECT category, name, revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
) ranked
WHERE rnk <= 3;

-- Daily revenue with a running total and day-over-day change
SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date)            AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY order_date)  AS change_vs_prev_day
FROM daily_revenue;

• ROW_NUMBER gives 1, 2, 3 even for ties; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2.
• PARTITION BY restarts the calculation for each group; ORDER BY inside OVER sets the order.
• LAG / LEAD read the previous / next row — perfect for growth comparisons.

NULL gotchas

• WHERE city = NULL never matches; use IS NULL.
• COUNT(*) counts rows; COUNT(city) skips NULLs.
• AVG ignores NULLs — use COALESCE(col, 0) only if missing truly means zero.