Interview Coding Exercises with Solutions

A coding round is not a test of whether you remember a trick. It is a test of how you think under mild pressure: whether you ask about the data, state your assumptions, build the answer in steps and check it.

Work each exercise yourself before you read the solution. Say what one row of your final result represents. Then compare. All outputs use the course database exactly as seeded.

A routine that works in an interview: clarify (ties? nulls? what counts as an order?), sketch (what tables, what grain), write the simplest correct version, test it against an edge case, then talk about performance.

Exercise 1: Second-highest salary#

Return the second-highest distinct salary in employees. If there is none, return NULL.

Solution.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

 second_highest
----------------
         140000

Reasoning. Everything below the maximum is the pool, and its maximum is the second-highest. An aggregate with no matching rows returns NULL, which handles the "no second value" case for free.

Variants. For the Nth highest, use DENSE_RANK() OVER (ORDER BY salary DESC) and keep the rank you want, which handles ties correctly. Mention that LIMIT 1 OFFSET 1 returns the second row, which is wrong when the top salary is tied, and returns no row, not NULL, when there is no second value.

Exercise 2: Customers who never ordered#

Solution.

SELECT c.full_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

 full_name
-----------
 Jon Smith

Reasoning. An anti-join. Explain why you chose NOT EXISTS over NOT IN (NULL safety) and over LEFT JOIN ... IS NULL (equivalent, but this states the intent more directly).

Edge case to raise. Diego Alvarez has an order, but it was cancelled. If "never ordered" meant "never completed a purchase", you would add AND o.status <> 'cancelled' inside the subquery and Diego would appear too. Asking that out loud is exactly what the interviewer wants.

Exercise 3: Find duplicates#

Which cities have more than one customer?

Solution.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;

  city  | customers
--------+-----------
 Austin |         2

Reasoning. Group by the thing that should be unique, keep groups larger than one. To see the full duplicate rows, join back or use COUNT(*) OVER (PARTITION BY city) in a subquery and filter on it.

Exercise 4: Delete duplicates, keep the oldest#

You have a table contacts (id, email, ...) with repeated emails. Delete every duplicate but keep the lowest id.

Solution.

DELETE FROM contacts
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY id) AS rn
        FROM contacts
    ) t
    WHERE rn > 1
);

Reasoning. Number the rows within each email group, oldest first. Everything with rn > 1 is an extra copy. Normalizing with LOWER catches Ana@x.com and ana@x.com.

Safe habit to mention. Run the inner SELECT first and look at what will be deleted, wrap the delete in a transaction and check the row count. Then add a unique index on LOWER(email) so it cannot happen again.

Exercise 5: Employees earning above their department's average#

Solution.

SELECT e.full_name, d.name AS dept, e.salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary > (
    SELECT AVG(x.salary) FROM employees x WHERE x.department_id = e.department_id
)
ORDER BY d.name;

  full_name  |    dept     | salary
-------------+-------------+--------
 Meera Iyer  | Engineering | 185000
 Priya Nair  | Sales       | 130000
 Omar Haddad | Support     | 100000

Reasoning. A correlated subquery computes each employee's own department average. Rahul Verma, alone in Finance, equals his department's average, so he is correctly excluded (> is strict).

Alternative. Compute the averages once with AVG(salary) OVER (PARTITION BY department_id) in a subquery and filter in the outer query. It reads the table once, which scales better.

Exercise 6: Highest-paid employee in each department, including ties#

Solution.

SELECT d.name AS dept, e.full_name, e.salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary = (
    SELECT MAX(x.salary) FROM employees x WHERE x.department_id = e.department_id
)
ORDER BY d.name;

    dept     |  full_name  | salary
-------------+-------------+--------
 Engineering | Meera Iyer  | 185000
 Finance     | Rahul Verma | 110000
 Sales       | Priya Nair  | 130000
 Support     | Omar Haddad | 100000

Reasoning. Equality against the department maximum returns every employee who ties for the top, which ROW_NUMBER() = 1 would not. Legal does not appear because it has no employees. State that this is a consequence of the inner join. If the question required every department, start from departments with a LEFT JOIN.

Exercise 7: People who share a salary within their department#

Solution.

SELECT e.full_name, e.salary
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees o
    WHERE o.department_id = e.department_id
      AND o.salary = e.salary
      AND o.employee_id <> e.employee_id
)
ORDER BY e.full_name;

   full_name    | salary
----------------+--------
 Kenji Watanabe | 120000
 Sofia Martins  | 120000

Reasoning. For each person, look for a different person (employee_id <>) in the same department with the same salary. The self-comparison exclusion is the line most candidates forget, and without it every row matches itself.

Exercise 8: Cumulative revenue by month#

Report monthly revenue (non-cancelled orders) with a running total.

Solution.

WITH monthly AS (
    SELECT DATE_TRUNC('month', o.order_date)::date AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY 1
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS cumulative
FROM monthly
ORDER BY month;

   month    | revenue | cumulative
------------+---------+------------
 2024-02-01 |  439.00 |     439.00
 2024-03-01 |  501.50 |     940.50
 2024-04-01 | 1229.50 |    2170.00
 2024-05-01 |  710.00 |    2880.00
 2024-06-01 |  259.50 |    3139.50

Reasoning. Aggregate to months first, then run the window over the aggregated rows. Mention that months are unique, so the default RANGE frame behaves like a strict running total here. With duplicate sort values you would specify ROWS.

Exercise 9: Customers who ordered in two consecutive months#

Solution.

WITH months AS (
    SELECT DISTINCT customer_id,
           EXTRACT(YEAR FROM order_date)::int * 12 + EXTRACT(MONTH FROM order_date)::int AS month_index
    FROM orders
    WHERE status <> 'cancelled'
),
with_prev AS (
    SELECT customer_id, month_index,
           LAG(month_index) OVER (PARTITION BY customer_id ORDER BY month_index) AS prev_index
    FROM months
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE month_index - prev_index = 1
ORDER BY customer_id;

 customer_id
-------------
           1
           6

Reasoning. Turn each month into a single integer (year * 12 + month), so consecutive months differ by exactly 1, including across a year boundary. DISTINCT in the first CTE handles customers with several orders in one month. Customer 1 ordered in February and March, customer 6 in April and May.

Why not subtract dates. Subtracting timestamps gives days, and months have different lengths, which makes "exactly one month" awkward. A month index sidesteps it.

Exercise 10: Median order value#

Solution.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) AS median_order
FROM (
    SELECT SUM(oi.quantity * oi.unit_price) AS total
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id
) t;

 median_order
--------------
        177.50

Reasoning. PostgreSQL has no MEDIAN, but PERCENTILE_CONT(0.5) is the continuous median (it interpolates between the two middle values for an even count). There are 13 orders, so the median is the 7th sorted value. Compare with the mean of 241.50: the average is pulled up by a few large orders, which is why analysts prefer the median for skewed data.

Portable alternative. Sort with ROW_NUMBER and COUNT(*) OVER () and average the middle one or two rows.

Exercise 11: Latest order per customer, only if it is still open#

List customers whose most recent order is pending or paid.

Solution.

SELECT customer_id, order_id, status
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY order_date DESC, order_id DESC) AS rn
    FROM orders o
) t
WHERE rn = 1
  AND status IN ('pending', 'paid')
ORDER BY customer_id;

 customer_id | order_id | status
-------------+----------+---------
           6 |       12 | pending
           9 |       14 | pending

Reasoning. First pick each customer's latest order, then test its status. Filtering by status before ranking would answer a different question: it would find their latest open order, even if a newer delivered one exists. Be explicit about that difference.

Exercise 12: Everyone under a manager, at any depth#

List everyone who reports to Priya Nair, directly or indirectly.

Solution.

WITH RECURSIVE reports AS (
    SELECT employee_id, full_name
    FROM employees
    WHERE manager_id = (SELECT employee_id FROM employees WHERE full_name = 'Priya Nair')

    UNION ALL

    SELECT e.employee_id, e.full_name
    FROM employees e
    JOIN reports r ON e.manager_id = r.employee_id
)
SELECT full_name FROM reports ORDER BY full_name;

   full_name
---------------
 Daniel Okafor
 Lena Fischer

Reasoning. The anchor is Priya's direct reports. The recursive step finds their reports, and so on until a round returns nothing. Replace the manager filter with Meera Iyer and the same query returns the other nine employees. Mention guarding against cycles in untrusted data.

Exercise 13: Percentage of customers who have bought#

Solution.

SELECT ROUND(
         100.0 * COUNT(DISTINCT o.customer_id) / (SELECT COUNT(*) FROM customers),
         1) AS pct_customers_who_bought
FROM orders o
WHERE o.status <> 'cancelled';

 pct_customers_who_bought
--------------------------
                     80.0

Reasoning. The numerator is customers with at least one non-cancelled order (8), the denominator is all customers (10). The key points are the 100.0 literal, which prevents integer division, and the question of what "bought" means. Using all orders instead of non-cancelled would give 90.0, so say which definition you used.

Exercise 14: Orders per customer by status, as columns#

For each country, count delivered orders and in-progress orders (pending, paid or shipped).

Solution.

SELECT c.country,
       COUNT(*) FILTER (WHERE o.status = 'delivered')                    AS delivered,
       COUNT(*) FILTER (WHERE o.status IN ('pending', 'paid', 'shipped')) AS in_progress
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.country
ORDER BY c.country;

 country | delivered | in_progress
---------+-----------+-------------
 France  |         1 |           1
 Germany |         1 |           0
 India   |         2 |           3
 Ireland |         0 |           1
 Japan   |         1 |           0
 Spain   |         0 |           0
 USA     |         3 |           0

Reasoning. Conditional aggregation turns row values into columns without a special PIVOT keyword. Spain shows 0 | 0 because Diego's cancelled order matches the join but neither filter. Jon Smith, who has no orders at all, contributes no row, so a country whose only customers never ordered would be missing entirely. If the question asked for every country, start from customers with a LEFT JOIN. Raising that unprompted is what the interviewer is listening for.

How to practice these#

1. Cover the solution, write yours, then run it against the course database and compare the output.
2. For each exercise, invent one edge case (a tie, a NULL, a customer with no orders) and predict what your query does with it.
3. Rewrite each solution a second way: a join instead of a subquery, a window function instead of a correlated subquery.
4. Time yourself. Aim to reach a correct, tested answer for the simpler ones in about five minutes, and talk through your assumptions as you go.

Practice#

1. Write "customers whose total spend is above the median customer spend" using PERCENTILE_CONT in a CTE.
2. Write "the product pair bought together most often" with a self-join on order_items and a.product_id < b.product_id.
3. Write "for each customer, the number of days between their first and last non-cancelled order". Which customers have a gap of zero, and why?
4. Make up an exercise of your own with a deliberate trap (a tie, a NULL, a fan-out), write the wrong answer a rushed candidate would give, and explain why it fails.