Joins and Aggregations

Joins and aggregations are the most-tested SQL topic in data science interviews. Nearly every take-home problem or whiteboard question involves combining tables and summarising data. Interviewers use this topic to separate candidates who can write queries from candidates who understand what queries actually do.

---

Q1: What are the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

Show answer

All joins combine rows from two tables based on a condition. They differ in how they handle rows that have no match.

INNER JOIN — returns only rows where the join condition is satisfied in both tables. Unmatched rows are dropped entirely.

LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table. Rows from the right table that have no match are filled with NULL. The left table is never dropped.

RIGHT JOIN — the mirror of LEFT JOIN. All rows from the right table are kept; unmatched left rows become NULL. In practice, you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order — most teams standardise on LEFT JOIN for consistency.

FULL OUTER JOIN — returns all rows from both tables. Unmatched rows from either side fill the other side's columns with NULL.

-- Sample tables: customers (id, name) and orders (id, customer_id, amount)

-- Only customers who have placed at least one order
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- All customers, including those with no orders (amount will be NULL)
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- All rows from both sides, NULLs where there is no match
SELECT c.name, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Practical heuristic: "find all X and optionally Y" → LEFT JOIN. "Find only X that have Y" → INNER JOIN. "Find X that have no Y" → LEFT JOIN + WHERE o.id IS NULL (anti-join).

---

Q2: How do you find rows in one table that have no match in another (anti-join)?

Show answer

The anti-join pattern is one of the most common interview tasks. Three approaches exist: LEFT JOIN + NULL check, NOT EXISTS, and NOT IN (with caveats).

LEFT JOIN + IS NULL (most portable, easy to read):

-- Customers who have never placed an order
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

After the LEFT JOIN, rows with no matching order will have NULL in every orders column. Filtering on IS NULL isolates those unmatched rows.

NOT EXISTS (explicit and safe with NULLs):

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

NOT IN — avoid when the subquery can return NULLs:

-- Works only if orders.customer_id is guaranteed NOT NULL
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If orders.customer_id contains even one NULL, the entire NOT IN expression evaluates to NULL (not TRUE) for every row, returning zero results. This is a silent, hard-to-catch bug. Prefer LEFT JOIN or NOT EXISTS.

---

Q3: When can a JOIN produce more rows than either input table?

Show answer

A join duplicates rows whenever the join key appears more than once in either table — a many-to-many relationship on the join column.

If a customer_id appears 3 times in orders and 2 times in customers (e.g., due to a data quality issue), joining on customer_id produces 2 × 3 = 6 rows for that customer.

-- Diagnose: check for duplicates on the join key before joining
SELECT customer_id, COUNT(*) AS occurrences
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

How to fix unexpected fanout: - Deduplicate one side before joining using a CTE with ROW_NUMBER() - Aggregate one side down to the granularity you need before joining - If duplicates are expected, verify the SUM or COUNT in the result is not inflated

-- Safely join after deduplicating customers
WITH deduped_customers AS (
    SELECT DISTINCT ON (customer_id) *
    FROM customers
    ORDER BY customer_id, updated_at DESC
)
SELECT dc.name, o.amount
FROM deduped_customers dc
JOIN orders o ON dc.customer_id = o.customer_id;

Undetected fanout silently inflates SUM and COUNT results — one of the most dangerous data bugs in analytics pipelines.

---

Q4: What is a self-join and when would you use one?

Show answer

A self-join joins a table to itself. You use it when rows within the same table have a relationship to each other — parent-child hierarchies, comparing adjacent rows, or finding pairs.

You must alias the table twice to give each reference a distinct name.

-- employees: id, name, manager_id (references id in the same table)
-- Find each employee paired with their manager's name
SELECT
    emp.name    AS employee,
    mgr.name    AS manager
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

Another common use — finding row pairs that satisfy a condition:

-- All pairs of products in the same category priced within $10 of each other
SELECT
    a.product_name,
    b.product_name,
    ABS(a.price - b.price) AS price_diff
FROM products a
JOIN products b
    ON  a.category     = b.category
    AND a.product_id   < b.product_id       -- prevents (A,B) and (B,A) duplicates
    AND ABS(a.price - b.price) <= 10;

The a.id < b.id condition prevents each pair from appearing twice and prevents a row from matching itself.

---

Q5: What can you SELECT when using GROUP BY, and what are the rules?

Show answer

GROUP BY collapses multiple rows into one row per group. Any column you SELECT must either: 1. Appear in the GROUP BY clause, or 2. Be wrapped in an aggregate function (SUM, COUNT, AVG, MIN, MAX)

Selecting a non-grouped, non-aggregated column is a logic error — the database cannot know which row's value to display.

-- Valid: department is in GROUP BY; AVG(salary) is aggregated
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Invalid in standard SQL: employee_name is neither grouped nor aggregated
SELECT department, employee_name, AVG(salary)
FROM employees
GROUP BY department;
-- PostgreSQL raises an error. MySQL without ONLY_FULL_GROUP_BY silently picks an arbitrary value.

You can GROUP BY multiple columns — one group per unique combination:

SELECT department, job_title, COUNT(*) AS headcount
FROM employees
GROUP BY department, job_title
ORDER BY department, headcount DESC;

Interview tip: MySQL historically allowed non-grouped columns without raising an error, producing non-deterministic results. Knowing this difference — and that PostgreSQL and BigQuery enforce strict grouping — shows database awareness.

---

Q6: How do aggregate functions handle NULL values?

Show answer

All aggregate functions — SUM, AVG, MIN, MAX, COUNT(col) — ignore NULL values. COUNT(*) counts rows, including rows where all columns are NULL.

-- Table scores(student_id, score): rows (1,80), (2,90), (3,NULL), (4,70)
SELECT
    COUNT(*)        AS total_students,   -- 4
    COUNT(score)    AS with_scores,      -- 3  (NULL excluded)
    SUM(score)      AS total_score,      -- 240 (NULL not treated as 0)
    AVG(score)      AS avg_score         -- 80.0 = 240 / 3, not 240 / 4
FROM scores;

The AVG gotcha: AVG(score) divides the sum by the count of non-null values, not by total rows. If a missing score should count as zero, substitute explicitly:

SELECT AVG(COALESCE(score, 0)) AS avg_including_absent FROM scores;
-- Returns 60.0 = 240 / 4

Always ask: does a NULL mean "unknown", "not applicable", or "zero"? The answer determines whether to leave nulls alone or substitute with COALESCE.

---

Q7: What is the difference between HAVING and WHERE?

Show answer

Both filter rows, but they run at different stages.

WHERE filters individual rows before grouping. It cannot reference aggregate functions because aggregation has not happened yet.

HAVING filters groups after GROUP BY. It can reference aggregates.

-- Departments where more than 5 employees earn over $80,000
SELECT
    department,
    COUNT(*) AS high_earners
FROM employees
WHERE salary > 80000             -- row-level filter (runs before GROUP BY)
GROUP BY department
HAVING COUNT(*) > 5;             -- group-level filter (runs after GROUP BY)

Common mistake: writing WHERE COUNT(*) > 5 — this raises an error because aggregate functions are not allowed in WHERE.

Interview tip: if the question involves filtering on a computed aggregate value, HAVING is the answer. If the question involves filtering raw rows, WHERE is the answer. Using both in the same query is correct and common.

---

Q8: How do you find and count duplicate rows?

Show answer

Duplicate detection is a standard data quality task. The approach depends on whether you mean exact row duplicates or duplicates on a subset of columns.

Find column-level duplicates:

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

Retrieve the actual duplicate rows for inspection:

SELECT *
FROM users
WHERE email IN (
    SELECT email FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
);

Keep one row per duplicate group using ROW_NUMBER:

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC      -- keep the most recent row
        ) AS rn
    FROM users
)
SELECT * FROM ranked WHERE rn = 1;

The ROW_NUMBER() OVER (PARTITION BY ...) pattern is the production-grade deduplication answer. It signals you've actually written data pipelines, not just classroom queries.

---

Q9: What is the "largest per group" problem and how do you solve it?

Show answer

For each group, find the row with the maximum (or minimum) value of some column. This comes up constantly: top-selling product per category, most recent order per customer, highest-paid employee per department.

Approach 1: Subquery with MAX()

SELECT p.*
FROM products p
JOIN (
    SELECT category, MAX(price) AS max_price
    FROM products
    GROUP BY category
) top ON p.category = top.category AND p.price = top.max_price;

Limitation: ties are both returned. That may or may not be what you want.

Approach 2: ROW_NUMBER() window function (preferred)

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY price DESC
        ) AS price_rank
    FROM products
) ranked
WHERE price_rank = 1;

ROW_NUMBER breaks ties deterministically (based on ORDER BY). Use RANK if you want ties to all appear with rank 1.

Approach 3: Correlated subquery

SELECT p.*
FROM products p
WHERE p.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category = p.category
);

Correct but runs a subquery per row — slow on large tables.

Interviewers use this problem specifically to see whether you reach for ROW_NUMBER() OVER (PARTITION BY ...). Knowing it is the expected signal for mid-level and senior candidates.

---

Q10: What is a CROSS JOIN and when would you intentionally use one?

Show answer

A CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right table. M rows × N rows = M×N result rows.

-- Generate all possible size-colour combinations for a product catalogue
SELECT s.size_label, c.colour_name
FROM sizes s
CROSS JOIN colours c;

Accidental CROSS JOINs (forgetting the join condition) are bugs. Intentional uses:

1. Generating a full grid for gap detection — pair every customer with every product to find absent combinations:

   SELECT c.customer_id, p.product_id
   FROM customers c
   CROSS JOIN products p
   WHERE NOT EXISTS (
       SELECT 1 FROM purchases pu
       WHERE pu.customer_id = c.customer_id AND pu.product_id = p.product_id
   );
   

2. Date spine expansion — cross-join a calendar table with a segment list to ensure every segment appears on every date before a LEFT JOIN to actual data.

3. Scenario analysis — pair a data table with a scenarios table (e.g., different discount rates) to compute outcomes for all combinations in one query.

---

Q11: How do you compute a running total without window functions?

Show answer

Without window functions, you compute a running total with a self-join: join each row to all preceding rows and sum.

-- Running total of daily revenue
SELECT
    a.sale_date,
    a.daily_revenue,
    SUM(b.daily_revenue) AS running_total
FROM daily_sales a
JOIN daily_sales b ON b.sale_date <= a.sale_date
GROUP BY a.sale_date, a.daily_revenue
ORDER BY a.sale_date;

This is O(n²) — for each of n rows, you join against up to n preceding rows. It is correct but slow on large tables.

The window function equivalent:

SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales;

Understanding the self-join approach demonstrates that you know what a running total is, not just how to write the shorthand. Interviewers who do not allow window functions (testing only join knowledge) expect this answer.

---

Q12: How do you pivot data from long format to wide format?

Show answer

Pivoting turns rows into columns. Standard SQL uses conditional aggregation because most databases (including PostgreSQL) lack a native PIVOT keyword.

Long format input — one row per metric:

user_id	metric	value
1	pageviews	100
1	clicks	10
2	pageviews	200

Pivot to wide format using CASE WHEN inside aggregates:

SELECT
    user_id,
    SUM(CASE WHEN metric = 'pageviews' THEN value END) AS pageviews,
    SUM(CASE WHEN metric = 'clicks'    THEN value END) AS clicks
FROM user_events
GROUP BY user_id;

For each metric column, CASE WHEN returns the value for matching rows and NULL for non-matching rows. SUM adds up the values, ignoring NULLs.

Monthly revenue pivot:

SELECT
    product_id,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN revenue END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN revenue END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN revenue END) AS mar
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY product_id;

Conditional aggregation is portable across all major databases. SQL Server and Oracle have PIVOT syntax, but the conditional aggregation approach always works.

---

Q13: What is ROLLUP and how does it differ from plain GROUP BY?

Show answer

GROUP BY ROLLUP computes aggregations at multiple levels of a hierarchy in a single query, adding subtotals and a grand total without requiring separate queries united by UNION ALL.

-- Sales by year and quarter, with yearly subtotals and a grand total
SELECT
    EXTRACT(YEAR    FROM order_date) AS year,
    EXTRACT(QUARTER FROM order_date) AS quarter,
    SUM(amount)                      AS total_sales
FROM orders
GROUP BY ROLLUP(
    EXTRACT(YEAR    FROM order_date),
    EXTRACT(QUARTER FROM order_date)
)
ORDER BY year NULLS LAST, quarter NULLS LAST;

Result includes: - One row per (year, quarter) combination - One subtotal row per year (quarter = NULL) - One grand total row (year = NULL, quarter = NULL)

Use GROUPING(col) to distinguish a subtotal NULL from an actual NULL in the data:

SELECT
    CASE WHEN GROUPING(department) = 1 THEN 'All Departments'
         ELSE department
    END AS dept_label,
    SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department);

CUBE computes aggregations for all possible combinations of dimensions, not just the hierarchical rollup. Use CUBE when dimensions are independent; use ROLLUP when they have a hierarchy (year → quarter → month).

---

Q14: What is the difference between UNION and UNION ALL?

Show answer

Both UNION and UNION ALL stack result sets vertically, requiring the same number of columns with compatible types from each query.

UNION removes duplicate rows from the combined result — it applies an implicit DISTINCT. This requires the database to sort or hash the full result, which is expensive.

UNION ALL keeps all rows, including duplicates. No deduplication step — it is significantly faster.

-- Combine active and inactive customers, removing any rows that appear in both
SELECT customer_id, name FROM active_customers
UNION
SELECT customer_id, name FROM inactive_customers;

-- Combine two partitioned tables known to have no overlapping rows
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024;

Default to UNION ALL unless you specifically need deduplication. Using UNION when UNION ALL would suffice wastes compute on large tables.

Common gotcha: column names in the output come from the first query. Alias your columns in the first SELECT if you need specific output names.

UNION vs JOIN: - JOIN combines columns horizontally (wider rows, same number of rows) - UNION stacks rows vertically (more rows, same number of columns)