SQL exercises with solutions: CTEs (WITH)

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

Query structure:

WITH … AS (
  SELECT …, AVG(…) AS …
  FROM …
  GROUP BY …)
SELECT …
FROM …
WHERE … > …

WITH avg_dept AS (
  SELECT department, AVG(salary) AS a
  FROM staff
  GROUP BY department)
SELECT department
FROM avg_dept
WHERE a > 45000;

Expected result (1 row):

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT …, SUM(…) AS …
  FROM …
  GROUP BY …)
SELECT …
FROM …
ORDER BY … DESC
LIMIT …

WITH t AS (
  SELECT department, SUM(salary) AS s
  FROM staff
  GROUP BY department)
SELECT department
FROM t
ORDER BY s DESC
LIMIT 1;

Expected result (1 row):

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

Query structure:

WITH … AS (
  SELECT …, COUNT(*) AS …
  FROM …
  GROUP BY …)
SELECT …
FROM …
JOIN … … ON … = …
WHERE … > (SELECT AVG(…) FROM …)

WITH c AS (
  SELECT member_id, COUNT(*) AS n
  FROM loans
  GROUP BY member_id)
SELECT m.name
FROM c
JOIN members m ON m.id = c.member_id
WHERE c.n > (SELECT AVG(n) FROM c);

Expected result (2 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT …, … + … AS …
  FROM …)
SELECT …
FROM …
WHERE … > (SELECT AVG(…) FROM …)

WITH t AS (
  SELECT played_on, home_goals + away_goals AS g
  FROM matches)
SELECT played_on
FROM t
WHERE g > (SELECT AVG(g) FROM t);

Expected result (5 rows):

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

Query structure:

WITH … AS (
  SELECT …, …, (… + …) / … AS …
  FROM …)
SELECT …, …, …
FROM …
WHERE … > …

WITH m AS (
  SELECT city, day, (temp_max + temp_min) / 2.0 AS t
  FROM readings)
SELECT city, day, t
FROM m
WHERE t > 22;

Expected result (7 rows):

Topics to use: WHERE (filters), JOIN, Text and dates, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT …, (JULIANDAY(…) - JULIANDAY(…)) * … AS …
  FROM … …
  JOIN … … ON … = …)
SELECT …, CAST(… AS INTEGER)
FROM …
WHERE … > …

WITH a AS (
  SELECT b.id, (julianday(b.check_out) - julianday(b.check_in)) * r.price AS amount
  FROM bookings b
  JOIN rooms r ON r.id = b.room_id)
SELECT id, CAST(amount AS INTEGER)
FROM a
WHERE amount > 500;

Expected result (3 rows):

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

Query structure:

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

WITH c AS (
  SELECT t.project_id, SUM(t.hours * d.rate) AS cost
  FROM tasks t
  JOIN devs d ON d.id = t.dev_id
  GROUP BY t.project_id)
SELECT p.name, c.cost
FROM projects p
JOIN c ON c.project_id = p.id
WHERE c.cost > 0.15 * p.budget;

Expected result (2 rows):

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, Text and dates, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT …, SUM(CASE WHEN … LIKE … THEN … ELSE … END) AS …, SUM(CASE WHEN … LIKE … THEN … ELSE … END) AS …
  FROM … …
  JOIN … … ON … = …
  WHERE … = …
  GROUP BY …)
SELECT …, …, …
FROM …
WHERE … > …

WITH s AS (
  SELECT a.owner, SUM(CASE WHEN t.made_on LIKE '2025-01%' THEN t.amount ELSE 0 END) AS jan, SUM(CASE WHEN t.made_on LIKE '2025-02%' THEN t.amount ELSE 0 END) AS feb
  FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  WHERE t.label = 'salary'
  GROUP BY a.owner)
SELECT owner, jan, feb
FROM s
WHERE feb > jan;

Expected result (1 row):

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

Query structure:

WITH … AS (
  SELECT …, …, SUM(…) AS …
  FROM … …
  JOIN … … ON … = …
  GROUP BY …)
SELECT …, …, …
FROM …
WHERE … = (SELECT MAX(…) FROM … … WHERE … = …)

WITH q AS (
  SELECT p.category, p.name, SUM(oi.qty) AS s
  FROM products p
  JOIN order_items oi ON oi.product_id = p.id
  GROUP BY p.id)
SELECT category, name, s
FROM q
WHERE s = (SELECT MAX(s) FROM q q2 WHERE q2.category = q.category);

Expected result (3 rows):

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

Query structure:

WITH … AS (
  SELECT …, SUM(…) AS …
  FROM …
  GROUP BY …)
SELECT …, …, CASE WHEN … >= … THEN … ELSE … END AS …
FROM …

WITH t AS (
  SELECT client, SUM(amount) AS total
  FROM invoices
  GROUP BY client)
SELECT client, total, CASE WHEN total >= 1500 THEN 'big' ELSE 'small' END AS size
FROM t;

Expected result (3 rows):

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, CASE, UNION, INTERSECT, EXCEPT, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT … AS …, CASE WHEN … > … THEN … WHEN … = … THEN … ELSE … END AS …
  FROM …
  UNION ALL SELECT …, CASE WHEN … > … THEN … WHEN … = … THEN … ELSE … END
  FROM …)
SELECT …, SUM(…)
FROM …
JOIN … … ON … = …
GROUP BY …
ORDER BY SUM(…) DESC, …

WITH r AS (
  SELECT home_id AS team, CASE WHEN home_goals > away_goals THEN 3 WHEN home_goals = away_goals THEN 1 ELSE 0 END AS pts
  FROM matches
  UNION ALL SELECT away_id, CASE WHEN away_goals > home_goals THEN 3 WHEN away_goals = home_goals THEN 1 ELSE 0 END
  FROM matches)
SELECT t.name, SUM(r.pts)
FROM r
JOIN teams t ON t.id = r.team
GROUP BY t.id
ORDER BY SUM(r.pts) DESC, t.name;

Expected result (5 rows, in this order):

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

Query structure:

WITH … AS (
  SELECT …, … * … / … AS …
  FROM …)
SELECT …, ROUND(AVG(…), …), SUM(CASE WHEN … > … THEN … ELSE … END)
FROM …
GROUP BY …

WITH r AS (
  SELECT airline, 100.0 * seats_sold / capacity AS t
  FROM flights)
SELECT airline, ROUND(AVG(t), 1), SUM(CASE WHEN t > 80 THEN 1 ELSE 0 END)
FROM r
GROUP BY airline;

Expected result (3 rows):

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

Query structure:

WITH … AS (
  SELECT …, AVG(…) AS …
  FROM …
  GROUP BY …)
SELECT …, …, ROUND(… - …, …)
FROM … …
JOIN … ON … = …
WHERE … - … > …

WITH a AS (
  SELECT day, AVG(temp_max) AS m
  FROM readings
  GROUP BY day)
SELECT r.day, r.city, ROUND(r.temp_max - a.m, 1)
FROM readings r
JOIN a ON a.day = r.day
WHERE r.temp_max - a.m > 3;

Expected result (4 rows):

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, NULL, COALESCE, CTEs (WITH).

Query structure:

WITH … AS (
  SELECT …, …, …, COALESCE(SUM(… * …), …) AS …
  FROM … …
  LEFT JOIN … … ON … = …
  LEFT JOIN … … ON … = …
  GROUP BY …)
SELECT …, …, …, … - …, ROUND(… * … / …, …)
FROM …
ORDER BY … * … / … DESC, …

WITH c AS (
  SELECT p.id, p.name, p.budget, COALESCE(SUM(t.hours * d.rate), 0) AS cost
  FROM projects p
  LEFT JOIN tasks t ON t.project_id = p.id
  LEFT JOIN devs d ON d.id = t.dev_id
  GROUP BY p.id)
SELECT name, budget, cost, budget - cost, ROUND(100.0 * cost / budget, 1)
FROM c
ORDER BY 1.0 * cost / budget DESC, name;

Expected result (4 rows, in this order):

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

Query structure:

WITH … AS (
  SELECT …, SUM(CASE WHEN … > … THEN … ELSE … END) AS …, -SUM(CASE WHEN … < … THEN … ELSE … END) AS …
  FROM … …
  JOIN … … ON … = …
  GROUP BY …)
SELECT …, ROUND(… * … / …, …)
FROM …
WHERE … > … AND … > … * …

WITH s AS (
  SELECT a.owner, SUM(CASE WHEN t.amount > 0 THEN t.amount ELSE 0 END) AS income, -SUM(CASE WHEN t.amount < 0 THEN t.amount ELSE 0 END) AS spent
  FROM accounts a
  JOIN transactions t ON t.account_id = a.id
  GROUP BY a.owner)
SELECT owner, ROUND(100.0 * spent / income, 1)
FROM s
WHERE income > 0 AND spent > 0.3 * income;

Expected result (2 rows):