Practice: subqueries, views and window functions

50 min · Intermediate

Most questions in this module can be answered in more than one way: a subquery, a join, a CTE or a window function. Being able to move between them, and to spot the small mistakes that make a window query quietly wrong, is what turns these tools into everyday skills.

Rewriting: subquery, join or CTE

A rewrite check solves the same question in a second style, such as a subquery, a join, a CTE or a window function, and compares the results. If they differ, one of the queries has a bug.

Rewriting a query in another style is the best way to check it. If a correlated subquery and a join with a derived table return the same rows, both are probably right; if they differ, one of them has a bug worth finding.

Example 1 · Latest order per customer, three ways

SQL

1-- 1. Correlated subquery2SELECT c.name, o.order_id, o.order_date3FROM orders o4JOIN customers c ON c.customer_id = o.customer_id5WHERE o.order_date = (SELECT MAX(x.order_date)6                      FROM orders x7                      WHERE x.customer_id = o.customer_id)8ORDER BY c.name;9
10-- 2. Join to a derived table of latest dates11SELECT c.name, o.order_id, o.order_date12FROM orders o13JOIN customers c ON c.customer_id = o.customer_id14JOIN (SELECT customer_id, MAX(order_date) AS last_date15      FROM orders GROUP BY customer_id) AS l16  ON l.customer_id = o.customer_id AND l.last_date = o.order_date17ORDER BY c.name;18
19-- 3. ROW_NUMBER in a CTE20WITH ranked AS (21    SELECT o.customer_id, o.order_id, o.order_date,22           ROW_NUMBER() OVER (PARTITION BY o.customer_id23                              ORDER BY o.order_date DESC, o.order_id DESC) AS rn24    FROM orders o25)26SELECT c.name, r.order_id, r.order_date27FROM ranked r28JOIN customers c ON c.customer_id = r.customer_id29WHERE r.rn = 130ORDER BY c.name;

Output

+--------------+----------+------------+
| name         | order_id | order_date |
+--------------+----------+------------+
| Arjun Mehta  |        8 | 2025-03-28 |
| Asha Rao     |       10 | 2025-04-12 |
| Divya Nair   |        9 | 2025-04-04 |
| Kabir Singh  |       12 | 2025-05-02 |
| Meena Iyer   |        4 | 2025-02-10 |
| Priya Sharma |       14 | 2025-05-20 |
| Rahul Verma  |       11 | 2025-04-25 |
| Ravi Kumar   |       15 | 2025-06-01 |
| Sneha Patil  |       13 | 2025-05-09 |
+--------------+----------+------------+
+--------------+----------+------------+
| name         | order_id | order_date |
+--------------+----------+------------+
| Arjun Mehta  |        8 | 2025-03-28 |
| Asha Rao     |       10 | 2025-04-12 |
| Divya Nair   |        9 | 2025-04-04 |
| Kabir Singh  |       12 | 2025-05-02 |
| Meena Iyer   |        4 | 2025-02-10 |
| Priya Sharma |       14 | 2025-05-20 |
| Rahul Verma  |       11 | 2025-04-25 |
| Ravi Kumar   |       15 | 2025-06-01 |
| Sneha Patil  |       13 | 2025-05-09 |
+--------------+----------+------------+
+--------------+----------+------------+
| name         | order_id | order_date |
+--------------+----------+------------+
| Arjun Mehta  |        8 | 2025-03-28 |
| Asha Rao     |       10 | 2025-04-12 |
| Divya Nair   |        9 | 2025-04-04 |
| Kabir Singh  |       12 | 2025-05-02 |
| Meena Iyer   |        4 | 2025-02-10 |
| Priya Sharma |       14 | 2025-05-20 |
| Rahul Verma  |       11 | 2025-04-25 |
| Ravi Kumar   |       15 | 2025-06-01 |
| Sneha Patil  |       13 | 2025-05-09 |
+--------------+----------+------------+

What this code does

All three return the same 9 rows, one per customer who has ordered. Imran Khan never ordered, so he is in none of them.
Versions 1 and 2 compare dates, so a customer with two orders on the same last day would appear twice. Version 3 always returns exactly one row per customer, because order_id DESC breaks the tie.
Version 3 is also the easiest to change into "the last 2 orders per customer": just keep rn <= 2.

"The latest order of each customer" has three common answers: a correlated subquery that compares each order with that customer's MAX(order_date), a join to a derived table of those maximum dates, and ROW_NUMBER() in a CTE. They agree here because no customer has two orders on the same day. With such a tie, the first two would return both orders and ROW_NUMBER exactly one.

When you rewrite, compare row counts first, then the rows themselves. Pick the final version by readability and by how it treats ties and NULLs.

Ranking and running totals in one report

One report can mix several window functions over the same rows. A monthly revenue report can show each month's rank by revenue, the running total so far and the share of the whole half-year, all next to the month's own revenue.

Example 2 · Monthly report: rank, running total and share

SQL

1WITH monthly AS (2    SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,3           SUM(oi.quantity * oi.unit_price)  AS revenue4    FROM orders o5    JOIN order_items oi ON oi.order_id = o.order_id6    WHERE o.status <> 'CANCELLED'7    GROUP BY month8)9SELECT month,10       revenue,11       RANK() OVER (ORDER BY revenue DESC)              AS revenue_rank,12       SUM(revenue) OVER (ORDER BY month)              AS running_total,13       ROUND(100 * revenue / SUM(revenue) OVER (), 1)  AS share_pct14FROM monthly15ORDER BY month;

Output

+---------+----------+--------------+---------------+-----------+
| month   | revenue  | revenue_rank | running_total | share_pct |
+---------+----------+--------------+---------------+-----------+
| 2025-01 | 22544.00 |            4 |      22544.00 |      11.5 |
| 2025-02 | 66037.00 |            2 |      88581.00 |      33.6 |
| 2025-03 | 72083.00 |            1 |     160664.00 |      36.7 |
| 2025-04 | 24594.00 |            3 |     185258.00 |      12.5 |
| 2025-05 |  6298.00 |            5 |     191556.00 |       3.2 |
| 2025-06 |  4897.00 |            6 |     196453.00 |       2.5 |
+---------+----------+--------------+---------------+-----------+

What this code does

Three windows over the same six rows: revenue_rank orders by revenue, running_total by month, and SUM(revenue) OVER () is the half-year total used for the share.
March is rank 1 with 36.7% of revenue; the running total ends at 196453.00, the same total as the share's denominator.
The rows print in month order because of the final ORDER BY month, even though the rank column follows revenue.

Each window function has its own OVER (...), so one column can order by revenue (the rank) while another orders by month (the running total). The final ORDER BY only decides how the rows are printed.

Build such reports on top of a CTE that already has one row per month. Window functions then work on clean, summarised rows and stay short.

Mistakes in window queries

A wrong window query rarely gives an error. It runs and prints believable numbers, so you must know the usual mistakes:

Example 3 · Mistake: a per-department running total without PARTITION BY

SQL

1-- Wrong: the total runs across both departments, in hire-date order2SELECT name, dept_id, hire_date, salary,3       SUM(salary) OVER (ORDER BY hire_date) AS running_payroll4FROM employees5WHERE dept_id IN (2, 4)6ORDER BY dept_id, hire_date;7
8-- Fixed: restart the total in each department9SELECT name, dept_id, hire_date, salary,10       SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS running_payroll11FROM employees12WHERE dept_id IN (2, 4)13ORDER BY dept_id, hire_date;

Output

+--------------+---------+------------+-----------+-----------------+
| name         | dept_id | hire_date  | salary    | running_payroll |
+--------------+---------+------------+-----------+-----------------+
| Neha Joshi   |       2 | 2016-11-30 | 150000.00 |       290000.00 |
| Amit Shah    |       2 | 2021-04-12 |  85000.00 |       375000.00 |
| Farah Ali    |       2 | 2023-02-01 |  85000.00 |       535000.00 |
| Gita Reddy   |       4 | 2016-02-25 | 140000.00 |       140000.00 |
| Manoj Pillai |       4 | 2022-12-05 |  75000.00 |       450000.00 |
+--------------+---------+------------+-----------+-----------------+
+--------------+---------+------------+-----------+-----------------+
| name         | dept_id | hire_date  | salary    | running_payroll |
+--------------+---------+------------+-----------+-----------------+
| Neha Joshi   |       2 | 2016-11-30 | 150000.00 |       150000.00 |
| Amit Shah    |       2 | 2021-04-12 |  85000.00 |       235000.00 |
| Farah Ali    |       2 | 2023-02-01 |  85000.00 |       320000.00 |
| Gita Reddy   |       4 | 2016-02-25 | 140000.00 |       140000.00 |
| Manoj Pillai |       4 | 2022-12-05 |  75000.00 |       215000.00 |
+--------------+---------+------------+-----------+-----------------+

What this code does

In the wrong version Neha Joshi, the first person in Sales, shows 290000.00: her own 150000.00 plus Gita Reddy of Finance, hired earlier. The first-row check fails at once.
With PARTITION BY dept_id, each department's first row equals its own salary, and the totals end at each department's payroll: 320000.00 for Sales and 215000.00 for Finance.

No ORDER BY in OVER → a "running" total is the grand total on every row.
No PARTITION BY → a per-department total runs on across departments.
ORDER BY with ties and the default frame → tied rows share one running total.
Filtering in WHERE → removes rows before LAG or a ranking sees them.
ROW_NUMBER without a tie-breaker → a different winner from run to run.

The quickest check is to compute one or two rows by hand. For a per-group running total, the first row of each group must equal that row's own value.

Find the bug

Find the wrong line

Each window query has one wrong line. Click it.

List only the highest-paid employee.

Round 1 of 2

When a number looks wrong, print the window's inputs next to it: the partition column, the order column and the raw value. The mistake usually becomes visible at the first group boundary.

Choosing the right tool

Window functions run after GROUP BY, so they can work on grouped results: SUM(SUM(salary)) OVER () is the total of all department payrolls, and RANK() OVER (ORDER BY SUM(salary) DESC) ranks the departments, in one query.

Example 4 · Window functions over a grouped result

SQL

1SELECT d.dept_name,2       COUNT(*)                                                AS staff,3       SUM(e.salary)                                           AS payroll,4       ROUND(100 * SUM(e.salary) / SUM(SUM(e.salary)) OVER (), 1) AS pct_of_payroll,5       RANK() OVER (ORDER BY SUM(e.salary) DESC)               AS payroll_rank6FROM employees e7JOIN departments d ON d.dept_id = e.dept_id8GROUP BY d.dept_name9ORDER BY payroll_rank;

Output

+-------------+-------+-----------+----------------+--------------+
| dept_name   | staff | payroll   | pct_of_payroll | payroll_rank |
+-------------+-------+-----------+----------------+--------------+
| Engineering |     5 | 765000.00 |           52.8 |            1 |
| Sales       |     3 | 320000.00 |           22.1 |            2 |
| Finance     |     2 | 215000.00 |           14.8 |            3 |
| HR          |     2 | 150000.00 |           10.3 |            4 |
+-------------+-------+-----------+----------------+--------------+

What this code does

GROUP BY runs first and makes one row per department. The window functions then run over those 4 rows.
SUM(SUM(e.salary)) OVER () reads as: the window total of the group totals. Engineering takes 52.8% of the payroll of these four departments.
Tanvi Kulkarni has no department, so the inner join leaves her out of every number here; a LEFT JOIN would add a NULL group.

A short guide for the whole module:

Compare with one value or a list → subquery in WHERE (=, IN, EXISTS).
Summary of a summary, or named steps → derived table or CTE; hierarchy → recursive CTE.
Reuse a query across reports and apps → view.
Rank, running total, previous or next row, while keeping every row → window function.

Predict the output

Predict the groups

The employees table has departments 1 to 4, and Tanvi Kulkarni has dept_id NULL.

SELECT COUNT(*)

FROM (

SELECT dept_id

FROM employees

GROUP BY dept_id

) AS t;

Next step

Check your understanding

12 quick questions

Rewriting: subquery, join or CTE

A rewrite check solves the same question in a second style, such as a subquery, a join, a CTE or a window function, and compares the results. If they differ, one of the queries has a bug.

1-- 1. Correlated subquery2SELECT c.name, o.order_id, o.order_date3FROM orders o4JOIN customers c ON c.customer_id = o.customer_id5WHERE o.order_date = (SELECT MAX(x.order_date)6 FROM orders x7 WHERE x.customer_id = o.customer_id)8ORDER BY c.name;9 10-- 2. Join to a derived table of latest dates11SELECT c.name, o.order_id, o.order_date12FROM orders o13JOIN customers c ON c.customer_id = o.customer_id14JOIN (SELECT customer_id, MAX(order_date) AS last_date15 FROM orders GROUP BY customer_id) AS l16 ON l.customer_id = o.customer_id AND l.last_date = o.order_date17ORDER BY c.name;18 19-- 3. ROW_NUMBER in a CTE20WITH ranked AS (21 SELECT o.customer_id, o.order_id, o.order_date,22 ROW_NUMBER() OVER (PARTITION BY o.customer_id23 ORDER BY o.order_date DESC, o.order_id DESC) AS rn24 FROM orders o25)26SELECT c.name, r.order_id, r.order_date27FROM ranked r28JOIN customers c ON c.customer_id = r.customer_id29WHERE r.rn = 130ORDER BY c.name;

+--------------+----------+------------+ | name | order_id | order_date | +--------------+----------+------------+ | Arjun Mehta | 8 | 2025-03-28 | | Asha Rao | 10 | 2025-04-12 | | Divya Nair | 9 | 2025-04-04 | | Kabir Singh | 12 | 2025-05-02 | | Meena Iyer | 4 | 2025-02-10 | | Priya Sharma | 14 | 2025-05-20 | | Rahul Verma | 11 | 2025-04-25 | | Ravi Kumar | 15 | 2025-06-01 | | Sneha Patil | 13 | 2025-05-09 | +--------------+----------+------------+ +--------------+----------+------------+ | name | order_id | order_date | +--------------+----------+------------+ | Arjun Mehta | 8 | 2025-03-28 | | Asha Rao | 10 | 2025-04-12 | | Divya Nair | 9 | 2025-04-04 | | Kabir Singh | 12 | 2025-05-02 | | Meena Iyer | 4 | 2025-02-10 | | Priya Sharma | 14 | 2025-05-20 | | Rahul Verma | 11 | 2025-04-25 | | Ravi Kumar | 15 | 2025-06-01 | | Sneha Patil | 13 | 2025-05-09 | +--------------+----------+------------+ +--------------+----------+------------+ | name | order_id | order_date | +--------------+----------+------------+ | Arjun Mehta | 8 | 2025-03-28 | | Asha Rao | 10 | 2025-04-12 | | Divya Nair | 9 | 2025-04-04 | | Kabir Singh | 12 | 2025-05-02 | | Meena Iyer | 4 | 2025-02-10 | | Priya Sharma | 14 | 2025-05-20 | | Rahul Verma | 11 | 2025-04-25 | | Ravi Kumar | 15 | 2025-06-01 | | Sneha Patil | 13 | 2025-05-09 | +--------------+----------+------------+

1WITH monthly AS (2 SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,3 SUM(oi.quantity * oi.unit_price) AS revenue4 FROM orders o5 JOIN order_items oi ON oi.order_id = o.order_id6 WHERE o.status <> 'CANCELLED'7 GROUP BY month8)9SELECT month,10 revenue,11 RANK() OVER (ORDER BY revenue DESC) AS revenue_rank,12 SUM(revenue) OVER (ORDER BY month) AS running_total,13 ROUND(100 * revenue / SUM(revenue) OVER (), 1) AS share_pct14FROM monthly15ORDER BY month;

+---------+----------+--------------+---------------+-----------+ | month | revenue | revenue_rank | running_total | share_pct | +---------+----------+--------------+---------------+-----------+ | 2025-01 | 22544.00 | 4 | 22544.00 | 11.5 | | 2025-02 | 66037.00 | 2 | 88581.00 | 33.6 | | 2025-03 | 72083.00 | 1 | 160664.00 | 36.7 | | 2025-04 | 24594.00 | 3 | 185258.00 | 12.5 | | 2025-05 | 6298.00 | 5 | 191556.00 | 3.2 | | 2025-06 | 4897.00 | 6 | 196453.00 | 2.5 | +---------+----------+--------------+---------------+-----------+

1-- Wrong: the total runs across both departments, in hire-date order2SELECT name, dept_id, hire_date, salary,3 SUM(salary) OVER (ORDER BY hire_date) AS running_payroll4FROM employees5WHERE dept_id IN (2, 4)6ORDER BY dept_id, hire_date;7 8-- Fixed: restart the total in each department9SELECT name, dept_id, hire_date, salary,10 SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) AS running_payroll11FROM employees12WHERE dept_id IN (2, 4)13ORDER BY dept_id, hire_date;

+--------------+---------+------------+-----------+-----------------+ | name | dept_id | hire_date | salary | running_payroll | +--------------+---------+------------+-----------+-----------------+ | Neha Joshi | 2 | 2016-11-30 | 150000.00 | 290000.00 | | Amit Shah | 2 | 2021-04-12 | 85000.00 | 375000.00 | | Farah Ali | 2 | 2023-02-01 | 85000.00 | 535000.00 | | Gita Reddy | 4 | 2016-02-25 | 140000.00 | 140000.00 | | Manoj Pillai | 4 | 2022-12-05 | 75000.00 | 450000.00 | +--------------+---------+------------+-----------+-----------------+ +--------------+---------+------------+-----------+-----------------+ | name | dept_id | hire_date | salary | running_payroll | +--------------+---------+------------+-----------+-----------------+ | Neha Joshi | 2 | 2016-11-30 | 150000.00 | 150000.00 | | Amit Shah | 2 | 2021-04-12 | 85000.00 | 235000.00 | | Farah Ali | 2 | 2023-02-01 | 85000.00 | 320000.00 | | Gita Reddy | 4 | 2016-02-25 | 140000.00 | 140000.00 | | Manoj Pillai | 4 | 2022-12-05 | 75000.00 | 215000.00 | +--------------+---------+------------+-----------+-----------------+

1SELECT d.dept_name,2 COUNT(*) AS staff,3 SUM(e.salary) AS payroll,4 ROUND(100 * SUM(e.salary) / SUM(SUM(e.salary)) OVER (), 1) AS pct_of_payroll,5 RANK() OVER (ORDER BY SUM(e.salary) DESC) AS payroll_rank6FROM employees e7JOIN departments d ON d.dept_id = e.dept_id8GROUP BY d.dept_name9ORDER BY payroll_rank;

+-------------+-------+-----------+----------------+--------------+ | dept_name | staff | payroll | pct_of_payroll | payroll_rank | +-------------+-------+-----------+----------------+--------------+ | Engineering | 5 | 765000.00 | 52.8 | 1 | | Sales | 3 | 320000.00 | 22.1 | 2 | | Finance | 2 | 215000.00 | 14.8 | 3 | | HR | 2 | 150000.00 | 10.3 | 4 | +-------------+-------+-----------+----------------+--------------+