SQL Advanced — Interview Questions¶
Questions covering JOINs, GROUP BY, subqueries, CTEs, CASE WHEN, and window functions. These appear in data analyst technical screens at all levels.
JOINs¶
[Beginner] What is the difference between INNER JOIN and LEFT JOIN?
Show answer
- INNER JOIN returns only rows where the join condition matches in both tables. Rows with no match are excluded.
- LEFT JOIN returns all rows from the left table (before JOIN), and matching rows from the right. If no match exists, right-side columns are NULL.
Use INNER JOIN when you only want records with data in both tables. Use LEFT JOIN when you want to keep all records from the left table, including those with no match on the right — e.g., customers who have never ordered.
[Beginner] Write a query to find all customers who have never placed an order.
Show answer
-- Method 1: LEFT JOIN + IS NULL
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
-- Method 2: NOT IN subquery (careful with NULLs)
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id FROM orders
WHERE customer_id IS NOT NULL
);
-- Method 3: NOT EXISTS (safest — handles NULLs correctly)
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
[Mid-level] You join a 500-row customers table with a 50,000-row orders table and get 75,000 rows. Why? Is this a problem?
Show answer
The extra rows come from a one-to-many relationship — some customers have placed multiple orders, so each customer row is repeated once per order.
This is correct behaviour, not a bug. A single customer with 5 orders appears 5 times after the join.
It becomes a problem when you then try to aggregate customer-level attributes (like segment or city) in the same query — you'll double-count:
-- WRONG: counts each customer once per order
SELECT COUNT(*) FROM customers JOIN orders ...; -- returns 75,000
-- CORRECT: count unique customers
SELECT COUNT(DISTINCT c.customer_id) FROM customers c JOIN orders o ...; -- returns ≤500
If you need customer metrics + order metrics in the same query, aggregate orders first (CTE or subquery), then join the summary to customers.
[Mid-level] What is a self-join? Give a real business example.
Show answer
A self-join is when a table is joined to itself. It's used when you need to compare rows within the same table.
Example 1 — Employee hierarchy: An employees table has employee_id and manager_id. The manager is also an employee, so you join the table to itself:
SELECT
e.employee_id,
e.first_name AS employee,
m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Example 2 — Find customers who ordered the same product: Compare rows in orders where two different customers bought the same product_id.
GROUP BY and Aggregation¶
[Beginner] What is the difference between WHERE and HAVING?
Show answer
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups (aggregated values) |
| Runs | Before GROUP BY | After GROUP BY |
| Can use | Column values | Aggregate functions |
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'completed' -- ← filters rows (before grouping)
GROUP BY customer_id
HAVING COUNT(*) > 3; -- ← filters groups (after grouping)
A common mistake: trying to write WHERE COUNT(*) > 3 — this is a syntax error because COUNT hasn't been computed yet.
[Mid-level] Write a query that returns the percentage of orders that are 'completed' out of all orders, without using a subquery.
Show answer
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
ROUND(
COUNT(CASE WHEN status = 'completed' THEN 1 END)
* 100.0 / COUNT(*), 1
) AS completion_pct
FROM orders;
CASE WHEN status = 'completed' THEN 1 END returns 1 for completed rows and NULL for all others. COUNT() ignores NULLs, so it only counts the 1s.
Subqueries and CTEs¶
[Mid-level] What is the difference between a correlated and a non-correlated subquery?
Show answer
Non-correlated: runs once, independent of the outer query.
WHERE order_total > (SELECT AVG(order_total) FROM orders);
-- The inner query runs once and returns a single number.
Correlated: references a column from the outer query, so it runs once per row.
WHERE order_total > (SELECT AVG(order_total) FROM orders o2 WHERE o2.customer_id = o.customer_id);
-- Runs once per row in the outer query — very slow on large tables.
Correlated subqueries in SELECT or WHERE should be rewritten as JOINs or CTEs for performance whenever possible.
[Mid-level] When would you use a CTE instead of a subquery?
Show answer
Prefer CTEs when: - You need to reference the same result more than once (subqueries would duplicate the computation) - The query has multiple logical steps (chained CTEs are much more readable than nested subqueries) - You're writing code others will maintain
Subqueries are fine for: - Simple one-off filters in WHERE - Small scalar values (a single number)
Window Functions¶
[Beginner] What is a window function? How does it differ from GROUP BY?
Show answer
A window function computes a value across a set of rows related to the current row, without collapsing the result. Every row stays visible in the output.
GROUP BY collapses multiple rows into one row per group.
-- GROUP BY: 1 row per customer, individual orders disappear
SELECT customer_id, SUM(order_total) FROM orders GROUP BY customer_id;
-- Window function: 1 row per order, with the customer total added
SELECT order_id, customer_id, order_total,
SUM(order_total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;
[Mid-level] What is the difference between RANK() and DENSE_RANK()?
Show answer
When there are ties, RANK() skips the next rank(s), while DENSE_RANK() does not.
| Score | RANK | DENSE_RANK |
|---|---|---|
| 100 | 1 | 1 |
| 90 | 2 | 2 |
| 80 | 3 | 3 |
| 80 | 3 | 3 |
| 70 | 5 | 4 |
Use RANK() when the gap matters (e.g., Olympic medals — no 4th place after a tie for 3rd).
Use DENSE_RANK() when you want continuous numbering (e.g., percentile groups, top-N queries).
[Mid-level] Write a query that returns the most recent order for each customer.
Show answer
[Senior] You're asked to calculate the 7-day rolling average of daily revenue on a 500-million-row orders table. The query takes 12 minutes. What are your optimisation strategies?
Show answer
1. Pre-aggregate to daily: Don't run the window function on 500M rows — first aggregate to a daily revenue summary table (~365 rows/year), then apply the window function on that.
-- Step 1: daily aggregation (run as a scheduled job)
CREATE TABLE daily_revenue AS
SELECT DATE(order_date) AS day, SUM(order_total) AS revenue
FROM orders WHERE status = 'completed'
GROUP BY DATE(order_date);
-- Step 2: 7-day rolling average on 365 rows instead of 500M
SELECT day, revenue,
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily_revenue;
2. Use a materialised view (PostgreSQL) or a scheduled summary table. Refresh daily via a cron job.
3. Push to a data warehouse (BigQuery, Redshift, Snowflake) — these are columnar stores optimised for analytical queries and will handle 500M rows far faster than row-oriented OLTP databases.
4. Partition the table by date — the query only needs to read the relevant partition, not full 500M rows.