🎯 08 — SQL Interview Questions¶

Real questions from data analyst and data science interviews. Each answer includes a runnable SQL example using realistic table names.

SELECT, WHERE, ORDER BY, LIMIT¶

Q1: What is SQL and what is it used for in data science?

Show answer

SQL (Structured Query Language) is the standard language for querying and managing relational databases. In data science it is used to extract, filter, aggregate, and join data before it reaches Python or a BI tool.

SELECT customer_id, order_date, amount
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY amount DESC
LIMIT 10;

Q2: What is the difference between WHERE and HAVING?

Show answer

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You cannot use aggregate functions in a WHERE clause.

-- WHERE filters rows first, then GROUP BY aggregates
SELECT city, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'          -- row-level filter
GROUP BY city
HAVING COUNT(*) > 100;              -- group-level filter

Q3: How does ORDER BY work with NULL values?

Show answer

In most databases, NULL sorts last in ASC order and first in DESC order. Use NULLS FIRST or NULLS LAST to control this explicitly (supported in PostgreSQL and most ANSI-compliant databases).

SELECT customer_id, last_order_date
FROM customers
ORDER BY last_order_date ASC NULLS LAST;
-- customers who never ordered appear at the bottom

Q4: What does DISTINCT do? When would you avoid it?

Show answer

DISTINCT removes duplicate rows from the result set. Avoid it as a crutch — if duplicates appear unexpectedly, the root cause is usually a join creating extra rows. Fix the join rather than hiding the symptom with DISTINCT.

-- Count unique customers who placed an order
SELECT COUNT(DISTINCT customer_id) AS unique_buyers
FROM orders;

GROUP BY and Aggregations¶

Q5: What is the difference between COUNT(*) and COUNT(column)?

Show answer

COUNT(*) counts every row including those with NULLs. COUNT(column) counts only rows where that column is not NULL. This distinction matters when a column is sparsely populated.

SELECT
    COUNT(*)                  AS total_orders,
    COUNT(discount_code)      AS orders_with_discount,
    COUNT(*) - COUNT(discount_code) AS orders_without_discount
FROM orders;

Q6: Write a query that finds the top 3 products by total revenue in each category.

Show answer

Use a window function (RANK) partitioned by category, then filter in an outer query.

WITH ranked AS (
    SELECT
        category,
        product_name,
        SUM(revenue) AS total_revenue,
        RANK() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS rnk
    FROM sales
    GROUP BY category, product_name
)
SELECT category, product_name, total_revenue
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk;

JOIN Types¶

Q7: What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

Show answer

Join type	Rows returned
`INNER JOIN`	Only rows with a match in both tables
`LEFT JOIN`	All rows from the left table; NULLs for unmatched right rows
`RIGHT JOIN`	All rows from the right table; NULLs for unmatched left rows
`FULL OUTER JOIN`	All rows from both tables; NULLs where no match

-- LEFT JOIN: keep all customers, even those with no orders
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Q8: Find all customers who have never placed an order.

Show answer

Use a LEFT JOIN and filter for rows where the right table has no match (NULL on the joined key). A NOT IN subquery is an alternative but can be slow on large tables.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Q9: A join returns more rows than expected. What went wrong?

Show answer

The most common cause is duplicate keys on one or both sides, creating a many-to-many match. For every row in table A, the database matches every qualifying row in table B.

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

-- Fix: deduplicate before joining, or join on a unique key
WITH deduped_orders AS (
    SELECT DISTINCT customer_id, MAX(order_date) AS latest_order
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, d.latest_order
FROM customers c
JOIN deduped_orders d ON c.customer_id = d.customer_id;

Subqueries¶

Q10: What is the difference between a correlated and a non-correlated subquery?

Show answer

A non-correlated subquery runs once and its result is reused. A correlated subquery references a column from the outer query and runs once per row — it is much slower on large tables.

-- Non-correlated: inner query runs once
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Correlated: inner query runs for every row in employees
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department  -- references outer query
);

Q11: Find orders where the revenue is above the average revenue for that order's city.

Show answer

A correlated subquery computes the city average per row. A window function is a more efficient alternative.

-- Correlated subquery approach
SELECT order_id, city, revenue
FROM orders o
WHERE revenue > (
    SELECT AVG(revenue)
    FROM orders
    WHERE city = o.city
);

-- Window function approach (more efficient)
SELECT order_id, city, revenue
FROM (
    SELECT
        order_id,
        city,
        revenue,
        AVG(revenue) OVER (PARTITION BY city) AS city_avg
    FROM orders
) sub
WHERE revenue > city_avg;

CTEs (WITH Clause)¶

Q12: What is a CTE and why use one instead of a subquery?

Show answer

A CTE (Common Table Expression) is a named temporary result set defined with the WITH keyword. It is referenced by name in the main query. Benefits over subqueries: readable, reusable within the same query, and easier to debug step-by-step.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
),
ranked_months AS (
    SELECT
        month,
        revenue,
        RANK() OVER (ORDER BY revenue DESC) AS rnk
    FROM monthly_revenue
)
SELECT month, revenue
FROM ranked_months
WHERE rnk <= 3;

Q13: Write a CTE that calculates 7-day rolling average revenue.

Show answer

Use AVG as a window function with a ROWS BETWEEN frame specification.

WITH daily_revenue AS (
    SELECT
        order_date,
        SUM(amount) AS daily_total
    FROM orders
    GROUP BY order_date
)
SELECT
    order_date,
    daily_total,
    AVG(daily_total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM daily_revenue
ORDER BY order_date;

Window Functions¶

Q14: What is a window function? How does it differ from GROUP BY?

Show answer

A window function computes a value across a set of related rows without collapsing them into one row. GROUP BY reduces rows; window functions keep every row and add a computed column alongside it.

-- GROUP BY collapses to one row per city
SELECT city, SUM(revenue) AS total
FROM orders
GROUP BY city;

-- Window function keeps every row and adds the city total
SELECT
    order_id,
    city,
    revenue,
    SUM(revenue) OVER (PARTITION BY city) AS city_total,
    revenue / SUM(revenue) OVER (PARTITION BY city) AS share_of_city
FROM orders;

Q15: What is the difference between RANK() and DENSE_RANK()?

Show answer

Both assign rank numbers based on ordering. RANK() skips numbers after ties (1, 2, 2, 4). DENSE_RANK() does not skip (1, 2, 2, 3). Use DENSE_RANK() when you want position without gaps.

SELECT
    employee_id,
    department,
    salary,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_no_gaps
FROM employees;

Q16: What do LAG() and LEAD() do? Write a query to find month-over-month revenue change.

Show answer

LAG(col, n) returns the value from n rows before the current row within the window. LEAD(col, n) returns the value from n rows after. Both are ordered within the partition.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month)            AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month)  AS mom_change,
    ROUND(
        100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2) AS mom_pct_change
FROM monthly
ORDER BY month;

Q17: What does ROW_NUMBER() do? How is it different from RANK()?

Show answer

ROW_NUMBER() assigns a unique sequential number to every row — ties get different numbers based on physical order. RANK() gives tied rows the same number and then skips. Use ROW_NUMBER() when you need exactly one row per group (deduplication).

-- Keep only the most recent order per customer
WITH numbered AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM numbered
WHERE rn = 1;

NULL Handling¶

Q18: How do you handle NULLs in SQL? What is COALESCE?

Show answer

NULL represents an unknown value. Comparisons with NULL using = always return NULL (not TRUE), so use IS NULL / IS NOT NULL. COALESCE(a, b, c) returns the first non-NULL argument — useful for providing fallback values.

-- Wrong: this returns no rows because NULL = NULL is NULL, not TRUE
SELECT * FROM customers WHERE phone = NULL;

-- Correct
SELECT * FROM customers WHERE phone IS NULL;

-- COALESCE: use discount_price if available, otherwise list_price
SELECT
    product_id,
    COALESCE(discount_price, list_price) AS effective_price
FROM products;

Q19: What does NULLIF do?

Show answer

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. It is commonly used to prevent division-by-zero errors.

-- Without NULLIF: division by zero if total_sessions = 0
SELECT
    campaign_id,
    conversions * 1.0 / total_sessions AS conversion_rate   -- may error
FROM campaigns;

-- With NULLIF: returns NULL instead of error when total_sessions = 0
SELECT
    campaign_id,
    conversions * 1.0 / NULLIF(total_sessions, 0) AS conversion_rate
FROM campaigns;

Performance and Indexes¶

Q20: What is a database index and how does it affect query performance?

Show answer

An index is a data structure (typically a B-tree) that lets the database find rows without scanning the entire table. It speeds up WHERE, JOIN, and ORDER BY on the indexed column. The trade-off is that indexes slow down INSERT, UPDATE, and DELETE and consume storage.

-- Create an index on a frequently filtered column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- This query now uses the index instead of a full table scan
SELECT * FROM orders WHERE customer_id = 1042;

-- Check if the query uses an index (PostgreSQL)
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1042;

In practice: always index foreign keys and columns used in WHERE or JOIN conditions on large tables.

Data Types and Casting¶

Q21: How do you cast a column to a different data type in SQL?

Show answer

Use CAST(column AS type) (ANSI standard) or the ::type shorthand in PostgreSQL. Type mismatches are a common source of silent bugs — for example, a revenue column stored as VARCHAR will produce string sorting rather than numeric sorting.

-- ANSI CAST syntax
SELECT
    order_id,
    CAST(revenue_str AS DECIMAL(10, 2)) AS revenue,
    CAST(order_date_str AS DATE)        AS order_date
FROM raw_orders;

-- PostgreSQL shorthand
SELECT revenue_str::DECIMAL(10, 2) AS revenue
FROM raw_orders;

-- Aggregate after casting
SELECT SUM(CAST(revenue_str AS DECIMAL(10, 2))) AS total_revenue
FROM raw_orders;

Scenario Questions¶

Q22: A query runs slowly on a large table. What do you check first?

Show answer

Check in this order:

Run EXPLAIN (or EXPLAIN ANALYZE) to see the query plan — look for full table scans
Check if the WHERE and JOIN columns are indexed
Check if you are filtering early enough (avoid selecting all columns with SELECT * then filtering)
Look for functions on indexed columns in the WHERE clause — this disables index use

-- Index-defeating: function on the column prevents index use
WHERE YEAR(order_date) = 2024

-- Index-friendly: range comparison uses the index
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Q23: A query has both WHERE and HAVING. In what order does SQL process them?

Show answer

SQL processes clauses in this logical order (not the written order):

FROM / JOIN — identify source tables
WHERE — filter individual rows
GROUP BY — group the filtered rows
HAVING — filter groups
SELECT — compute output columns
ORDER BY — sort results
LIMIT — truncate

SELECT city, COUNT(*) AS order_count, AVG(amount) AS avg_amount
FROM orders                          -- 1. FROM
WHERE status = 'completed'           -- 2. WHERE (row filter)
GROUP BY city                        -- 3. GROUP BY
HAVING AVG(amount) > 500             -- 4. HAVING (group filter)
ORDER BY order_count DESC            -- 5. ORDER BY
LIMIT 10;                            -- 6. LIMIT

Checklist¶

[ ] SELECT specific columns
[ ] filter with WHERE
[ ] aggregate with GROUP BY
[ ] filter groups with HAVING
[ ] join tables (INNER, LEFT, FULL OUTER)
[ ] use subqueries (correlated and non-correlated)
[ ] use CTEs with WITH
[ ] use window functions (RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD)
[ ] handle NULLs with IS NULL, COALESCE, NULLIF
[ ] cast data types
[ ] interpret EXPLAIN output

🔗 Next¶

➡️ EDA