PostgreSQL practice exercises

These problems mix everything from the course, the way real questions arrive: in plain language, with no hint about which feature to use. Plan first (which tables? which grain? filter before or after grouping?), then write the query step by step, testing each CTE as you go.

Column names in the questions are suggestions; the checker compares values, not names. Where order matters, the question says so.

Syntax

-- A reliable way to attack any question
WITH step1 AS (...),
     step2 AS (...)
SELECT ... FROM step2 ...;

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 27.1

Which sales rep generated the most revenue from non-cancelled orders? Show first_name and revenue for the top rep only.

Hint

Join orders → order_items → employees, group by rep, order by revenue desc, limit 1.

Solution

SELECT e.first_name, sum(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN employees e ON e.id = o.employee_id WHERE o.status <> 'cancelled' GROUP BY e.id, e.first_name ORDER BY revenue DESC LIMIT 1;

Exercise 27.2

For each country, show the number of customers and the number of customers who have placed at least one order: country, customers, buyers.

Hint

count(DISTINCT c.id) and count(DISTINCT o.customer_id) after a LEFT JOIN.

Solution

SELECT c.country, count(DISTINCT c.id) AS customers, count(DISTINCT o.customer_id) AS buyers FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.country;

Exercise 27.3

Find employees who earn more than the average salary of their own department: first_name, dept_id, salary.

Hint

A correlated subquery, or avg(salary) OVER (PARTITION BY dept_id) in a CTE.

Solution

SELECT first_name, dept_id, salary FROM employees e WHERE salary > (SELECT avg(salary) FROM employees x WHERE x.dept_id = e.dept_id);

Exercise 27.4

What percentage of total revenue (all order items) does each category represent? Show category and pct rounded to 1 decimal, highest first.

Hint

sum(...) / sum(sum(...)) OVER () * 100

Solution

SELECT p.category, round(100 * sum(oi.quantity * oi.unit_price) / sum(sum(oi.quantity * oi.unit_price)) OVER (), 1) AS pct FROM order_items oi JOIN products p ON p.id = oi.product_id GROUP BY p.category ORDER BY pct DESC;

Exercise 27.5

For each customer with at least two orders, show the name and the average number of days between their consecutive orders as avg_gap_days (rounded to 0 decimals).

Hint

lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), then average the differences.

Solution

WITH g AS (SELECT customer_id, order_date - lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS gap FROM orders) SELECT c.name, round(avg(g.gap)) AS avg_gap_days FROM g JOIN customers c ON c.id = g.customer_id WHERE g.gap IS NOT NULL GROUP BY c.name;

Exercise 27.6

Which pairs of products were bought together in the same order more than once? Show product_a, product_b (names, a alphabetically before b) and times.

Hint

Join order_items to itself on order_id with a.product_id < b.product_id. Then compare names to order the pair.

Solution

SELECT LEAST(pa.name, pb.name) AS product_a, GREATEST(pa.name, pb.name) AS product_b, count(*) AS times FROM order_items a JOIN order_items b ON a.order_id = b.order_id AND a.product_id < b.product_id JOIN products pa ON pa.id = a.product_id JOIN products pb ON pb.id = b.product_id GROUP BY 1, 2 HAVING count(*) > 1;

Exercise 27.7

Show each month of 2024 (January to December) with its delivered revenue, using 0 for months with none: month (a date) and revenue, in month order.

Hint

generate_series calendar LEFT JOIN a monthly revenue CTE, COALESCE to 0.

Solution

WITH rev AS (SELECT date_trunc('month', o.order_date) AS m, sum(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status = 'delivered' GROUP BY 1) SELECT cal::date AS month, coalesce(rev.revenue, 0) AS revenue FROM generate_series('2024-01-01'::timestamp, '2024-12-01', interval '1 month') AS cal LEFT JOIN rev ON rev.m = cal ORDER BY cal;

Exercise 27.8

For every department, show its name, budget, total salaries of active employees as payroll (0 if none), and remaining (budget minus payroll). Order by remaining, smallest first.

Hint

LEFT JOIN with the is_active condition inside ON, then COALESCE.

Solution

SELECT d.name, d.budget, coalesce(sum(e.salary), 0) AS payroll, d.budget - coalesce(sum(e.salary), 0) AS remaining FROM departments d LEFT JOIN employees e ON e.dept_id = d.id AND e.is_active GROUP BY d.id ORDER BY remaining;