SQL exercises with solutions: window functions

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, ROW_NUMBER() OVER (ORDER BY … DESC, …)
FROM …

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC, name)
FROM staff;

Expected result (10 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC, …)
FROM …

SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name)
FROM staff;

Expected result (10 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, ROUND(… - AVG(…) OVER (), …)
FROM …

SELECT title, rating, ROUND(rating - AVG(rating) OVER (), 2)
FROM movies;

Expected result (10 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, SUM(… + …) OVER (ORDER BY …)
FROM …

SELECT played_on, SUM(home_goals + away_goals) OVER (ORDER BY played_on)
FROM matches;

Expected result (10 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, …, ROUND(AVG(…) OVER (PARTITION BY …), …)
FROM …

SELECT id, dest, price, ROUND(AVG(price) OVER (PARTITION BY dest), 1)
FROM flights;

Expected result (12 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, …, LEAD(…) OVER (PARTITION BY … ORDER BY …)
FROM …

SELECT city, day, rain_mm, LEAD(rain_mm) OVER (PARTITION BY city ORDER BY day)
FROM readings;

Expected result (15 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CTEs (WITH), Window functions (OVER).

Query structure:

WITH … AS (
  SELECT …, …, SUM(… * …) AS …
  FROM … …
  JOIN … … ON … = …
  JOIN … … ON … = …
  WHERE … <> …
  GROUP BY …)
SELECT …, …, …, SUM(…) OVER (ORDER BY …, …)
FROM …

WITH t AS (
  SELECT o.id, o.order_date, SUM(oi.qty * p.price) AS amount
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id)
SELECT id, order_date, amount, SUM(amount) OVER (ORDER BY order_date, id)
FROM t;

Expected result (7 rows):

Topics to use: Window functions (OVER).

Query structure:

SELECT …, …, …, SUM(…) OVER (PARTITION BY … ORDER BY …, …)
FROM …

SELECT account_id, made_on, amount, SUM(amount) OVER (PARTITION BY account_id ORDER BY made_on, id)
FROM transactions;

Expected result (14 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, Window functions (OVER).

Query structure:

SELECT AVG(…)
FROM (SELECT …, ROW_NUMBER() OVER (ORDER BY …) AS …, COUNT(*) OVER () AS … FROM …)
WHERE … IN ((… + …) / …, (… + …) / …)

SELECT AVG(salary)
FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS c FROM staff)
WHERE rn IN ((c + 1) / 2, (c + 2) / 2);

Expected result (1 row):

Topics to use: WHERE (filters), Subqueries, Window functions (OVER).

Query structure:

SELECT …, …
FROM (SELECT …, …, ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC) AS … FROM …)
WHERE … = …

SELECT team_id, name
FROM (SELECT team_id, name, ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY goals DESC) AS rn FROM players)
WHERE rn = 1;

Expected result (5 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, CTEs (WITH), Window functions (OVER).

Query structure:

WITH … AS (
  SELECT …, …, …, ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) - ROW_NUMBER() OVER (PARTITION BY …, … = … ORDER BY …) AS …
  FROM …), … AS (
  SELECT …, …, COUNT(*) AS …
  FROM …
  WHERE … = …
  GROUP BY …, …)
SELECT …, MAX(…)
FROM …
GROUP BY …

WITH r AS (
  SELECT city, day, rain_mm, ROW_NUMBER() OVER (PARTITION BY city ORDER BY day) - ROW_NUMBER() OVER (PARTITION BY city, rain_mm = 0 ORDER BY day) AS grp
  FROM readings), s AS (
  SELECT city, grp, COUNT(*) AS n
  FROM r
  WHERE rain_mm = 0
  GROUP BY city, grp)
SELECT city, MAX(n)
FROM s
GROUP BY city;

Expected result (3 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, CTEs (WITH), Window functions (OVER).

Query structure:

WITH … AS (
  SELECT …, SUM(SUM(…)) OVER (ORDER BY …) AS …
  FROM …
  WHERE … = …
  GROUP BY …)
SELECT MIN(…)
FROM …
WHERE … > …

WITH m AS (
  SELECT month, SUM(SUM(amount)) OVER (ORDER BY month) AS c
  FROM sales
  WHERE region = 'North'
  GROUP BY month)
SELECT MIN(month)
FROM m
WHERE c > 1000;

Expected result (1 row):

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, Text and dates, Window functions (OVER).

Query structure:

SELECT STRFTIME(…, …) AS …, COUNT(*), SUM(COUNT(*)) OVER (ORDER BY STRFTIME(…, …))
FROM …
GROUP BY …
ORDER BY …

SELECT strftime('%Y-%m', loan_date) AS month, COUNT(*), SUM(COUNT(*)) OVER (ORDER BY strftime('%Y-%m', loan_date))
FROM loans
GROUP BY month
ORDER BY month;

Expected result (4 rows, in this order):

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, CTEs (WITH), Window functions (OVER).

Query structure:

WITH … AS (
  SELECT …, …, …, ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC, …) AS …
  FROM … …
  JOIN … … ON … = …)
SELECT …, SUM(CASE WHEN … > … THEN … ELSE … END), SUM(…), MAX(CASE WHEN … = … AND … > … THEN … END)
FROM …
GROUP BY …

WITH r AS (
  SELECT c.region, x.day, x.rain_mm, ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY x.rain_mm DESC, x.day) AS rn
  FROM cities c
  JOIN readings x ON x.city = c.name)
SELECT region, SUM(CASE WHEN rain_mm > 0 THEN 1 ELSE 0 END), SUM(rain_mm), MAX(CASE WHEN rn = 1 AND rain_mm > 0 THEN day END)
FROM r
GROUP BY region;

Expected result (3 rows):

Topics to use: WHERE (filters), CTEs (WITH), Window functions (OVER).

Query structure:

WITH … AS (
  SELECT …, …, …, SUM(…) OVER (PARTITION BY … ORDER BY …, …) AS …
  FROM …), … AS (
  SELECT …, …, …, ROW_NUMBER() OVER (PARTITION BY … ORDER BY …, …, …) AS …
  FROM …)
SELECT …, …, …
FROM …
WHERE … = …

WITH b AS (
  SELECT account_id, made_on, id, SUM(amount) OVER (PARTITION BY account_id ORDER BY made_on, id) AS bal
  FROM transactions), k AS (
  SELECT account_id, made_on, bal, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY bal, made_on, id) AS rn
  FROM b)
SELECT account_id, made_on, bal
FROM k
WHERE rn = 1;

Expected result (5 rows):