Interview Questions: Joins, Aggregation and Window Functions

This is where interviews get practical. Almost everything below is something you can demonstrate on a whiteboard in five lines, and interviewers will ask you to.

A good habit: when you answer, name the table or data you would use and say what one row represents after the operation. That keeps you honest about grain, the source of most bugs here.

1. What does each join type return?#

Short answer. INNER JOIN returns matching rows only. LEFT JOIN returns all left rows, with NULLs where nothing matched. RIGHT JOIN is the mirror. FULL OUTER JOIN keeps unmatched rows from both. CROSS JOIN pairs every left row with every right row.

Best explanation. Describe it as matching, not Venn diagrams. For each left row, the database finds matching right rows; each match makes an output row. A left row with three matches appears three times, and one with none appears once with NULLs in a LEFT JOIN.

2. What is the difference between a condition in ON and in WHERE for a LEFT JOIN?#

Short answer. In ON, the condition decides what matches, and unmatched left rows survive. In WHERE, it filters the finished result, which can remove those rows and turn the LEFT JOIN into an inner join.

Example. Customers with their non-cancelled orders, keeping customers who have none:

SELECT c.full_name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled';

Moving o.status <> 'cancelled' to WHERE drops Jon Smith (no orders) and Diego Alvarez (only a cancelled one), because their status is NULL after the join and NULL <> 'cancelled' is unknown.

3. How do you find rows with no match in another table?#

Short answer. An anti-join: LEFT JOIN ... WHERE right.key IS NULL, or NOT EXISTS.

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

Returns Jon Smith. Mention that NOT IN is the third option and the dangerous one when the subquery can return NULL.

4. Explain a self join with an example.#

Short answer. Joining a table to itself under two aliases, used for hierarchies and comparisons within a table.

SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

Use LEFT JOIN so the top of the hierarchy, who has no manager, is not lost. For unlimited depth, use a recursive CTE.

5. What is a join fan-out, and how does it ruin totals?#

Short answer. Joining a parent to a child with many rows repeats the parent's values once per child, so sums and counts over the parent's columns inflate.

Evidence. On the course data, joining orders to order_items produces 26 rows for 14 orders. Summing any per-order value after that join would count each order once per line.

Fixes. Aggregate the child table first in a subquery or CTE so the join is one-to-one, use COUNT(DISTINCT ...) for counts, or sum only values that belong to the child's grain.

6. COUNT(*), COUNT(column) and COUNT(DISTINCT column)?#

Short answer. The first counts rows, the second counts rows where the column is not NULL, the third counts distinct non-null values.

Show it. With 10 customers and 7 phones, COUNT(*) is 10 and COUNT(phone) is 7.

7. Can you select a column that is not in GROUP BY?#

Short answer. Not unless it is aggregated or functionally dependent on the grouped columns (PostgreSQL allows columns that depend on a grouped primary key).

Why. A group holds many values of that column and the database cannot pick one. MySQL's old permissive mode silently returned an arbitrary one, which causes wrong results.

8. How do you write a conditional count or pivot?#

Short answer. Conditional aggregation.

SELECT customer_id,
       COUNT(*) FILTER (WHERE status = 'delivered')               AS delivered,
       SUM(CASE WHEN status IN ('pending','paid') THEN 1 ELSE 0 END) AS open_orders
FROM orders
GROUP BY customer_id;

FILTER is PostgreSQL's syntax. The CASE version is portable.

9. EXISTS versus IN: when do they differ?#

Short answer. For positive checks, the planner usually turns both into the same semi-join, so choose for readability. EXISTS can stop at the first match and ignores the values. For negation they differ: NOT IN breaks when the subquery contains NULL, NOT EXISTS does not.

10. What is a correlated subquery, and what is its cost?#

Short answer. A subquery that references the outer query's row, so it is conceptually re-evaluated per outer row.

Example. Products priced above their own category average. The planner often rewrites these as joins, but when it cannot, a SubPlan runs once per outer row, which is slow on large tables without an index on the correlation column. Offer the alternative: compute the averages once in a CTE or window function and join.

11. CTE, subquery or temporary table?#

Short answer. A CTE names a step and improves readability, and since PostgreSQL 12 it is usually inlined like a subquery. A subquery is fine for one-off nesting. A temporary table persists for the session, can be indexed and analyzed, and suits large intermediate results reused many times.

Mention. MATERIALIZED and NOT MATERIALIZED control CTE behavior, and recursive CTEs are the standard way to walk hierarchies.

12. How is a window function different from GROUP BY?#

Short answer. GROUP BY collapses rows into one per group. A window function computes across a set of related rows and keeps every original row.

Example. Each employee's salary next to their department total:

SELECT full_name, salary, SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

13. ROW_NUMBER, RANK and DENSE_RANK?#

Short answer. All number rows in order. ROW_NUMBER never repeats. RANK repeats on ties and then skips. DENSE_RANK repeats on ties and does not skip.

Use the tie. Salaries 185000, 140000, 130000, 120000, 120000, 110000 give ranks RANK 1, 2, 3, 4, 4, 6 and DENSE_RANK 1, 2, 3, 4, 4, 5. For a deterministic ROW_NUMBER, add a unique tiebreaker to the window's ORDER BY.

14. How do you get the top N rows per group?#

Short answer. Rank within each group and filter in an outer query.

SELECT * FROM (
    SELECT d.name AS dept, e.full_name, e.salary,
           ROW_NUMBER() OVER (PARTITION BY e.department_id
                              ORDER BY e.salary DESC, e.employee_id) AS rn
    FROM employees e JOIN departments d USING (department_id)
) t
WHERE rn <= 2;

Why the wrapper. You cannot filter on a window function in the same query level. Use DENSE_RANK instead if ties should all be included.

15. What do LAG and LEAD do?#

Short answer. They return a value from the previous or next row in the window's order, with an optional offset and default.

Example. Month-over-month change is revenue - LAG(revenue) OVER (ORDER BY month). Add PARTITION BY to restart per group, such as per customer for "days since last order".

16. Why can a running total over ORDER BY give odd results with ties?#

Short answer. The default frame is RANGE, which includes every peer row with the same sort value, so tied rows share the same running total. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for strict row-by-row accumulation.

Bonus trap. LAST_VALUE under the default frame returns the current row, because the frame ends at the current row. Widen it to UNBOUNDED FOLLOWING.

Practice#

1. Without looking, write the anti-join for "products never ordered", both ways, and say which you prefer and why.
2. Explain the ON versus WHERE difference using a drawing of three customers and their orders.
3. Take the fan-out example and write the corrected query that sums order value per customer without inflation.
4. Write one query that returns, for each department, the highest-paid employee, handling ties by returning everyone who ties for first.
5. Explain RANK versus DENSE_RANK to a non-technical colleague using a race result.