SQL Interview Questions for Data Engineers - Real Scenarios, Real Answers

The Interviewer Says

We have an employee table with salaries across multiple departments. I want you to write a query to find the employee who earns the second highest salary in each department. Keep in mind there may be employees sharing the same salary. Walk me through how you would approach this.

Input Table: employees

How to Handle This in the Interview

Before writing anything, say: Let me think through this carefully because there is a subtle trap here around duplicate salaries.

Step 1: Spot the duplicate salary trap immediately

• If two people share the same salary, are they both considered the same rank? Say: I will use DENSE_RANK so that ties get the same rank and no rank number is skipped. ROW_NUMBER would arbitrarily break ties and give wrong results here.

Step 2: Plan the approach

• Rank employees within each department by salary descending, then filter for rank = 2. This requires a CTE or subquery because you cannot reference a window function alias directly in a WHERE clause.

Step 3: Choose CTE over subquery

• Say: I prefer a CTE here, it makes the logic easier to read and debug compared to a nested subquery.

Step 4: Handle edge cases

• Ask: What should we return if a department has only one distinct salary level, should we return nothing or return NULL? This is a very common trap interviewers set intentionally.

Solution

SQL Server

WITH ranked_salaries AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY dept;

PostgreSQL

WITH ranked_salaries AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY dept;

MySQL 8+

WITH ranked_salaries AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY dept;

The syntax is identical across all three platforms for window functions. The CTE and DENSE_RANK behaviour is standard SQL.

What to say: I partition by department so the ranking resets independently for each department. DENSE_RANK means Sara and Raj both get rank 2 in Finance since they share 75000 — both rows appear in the output which is the correct honest answer. ROW_NUMBER would have arbitrarily picked only one of them.

Expected Output

Follow-up Questions & Answers

Q1. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER? Which one is correct here and why?

All three assign a number to each row within a partition but handle ties differently.

• ROW_NUMBER gives every row a unique number even if values are identical, tie-breaking is arbitrary.
• RANK gives tied rows the same number but skips the next rank, two rows tied at rank 1 make the next row rank 3, skipping rank 2.
• DENSE_RANK gives tied rows the same number and does not skip, two rows tied at rank 1 make the next row rank 2. For this problem DENSE_RANK is correct because we want tied salaries to share the same rank and we do not want any rank numbers skipped, which would cause us to miss real second-highest salaries.

Q2. Can you solve this without window functions?

Yes, using a correlated subquery, but this is much less efficient and harder to read. Good to know as an alternative if the interviewer asks.

SELECT
    e1.emp_id,
    e1.name,
    e1.dept,
    e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept = e1.dept
        AND e2.salary < (
            SELECT MAX(e3.salary)
            FROM employees e3
            WHERE e3.dept = e1.dept
        )
)
ORDER BY e1.dept;

What to say: This correlated subquery works but scans the table multiple times for each row which is very inefficient on large datasets. The window function approach with DENSE_RANK only requires one pass, I would always prefer that in production.

Q3. What if the interviewer asks for the Nth highest salary instead of just the second?

The CTE approach generalises very cleanly, just change the filter value.

WITH ranked_salaries AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_salaries
WHERE salary_rank = 3  -- change this to any N
ORDER BY dept;

What to say: This is why window functions are powerful,changing from second highest to Nth highest is just changing one number in the WHERE clause. With the correlated subquery approach you would have to nest it N times which becomes unmanageable very quickly.

Q4. What if a department has only one employee? what does your query return?

It returns no row for that department which is the correct behaviour, there is no second highest salary to show. However if the business requirement says return NULL for those departments, handle it with a LEFT JOIN.

WITH all_depts AS (
    SELECT DISTINCT dept FROM employees
),
ranked_salaries AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    d.dept,
    r.emp_id,
    r.name,
    r.salary
FROM all_depts d
LEFT JOIN ranked_salaries r ON d.dept = r.dept AND r.salary_rank = 2
ORDER BY d.dept;

What to say: Always clarify with the interviewer whether missing departments should appear as NULL rows or be omitted entirely, both are valid but the business requirement decides which is correct.

The Interviewer Says

We have a logistics pipeline that tracks the status of each order over time. The operations team wants to know exactly when an order status changed, for example when it moved from Ordered to Dispatched or from Dispatched to Shipped. I do not want to see rows where the status stayed the same as the previous day. Write a query to return only the rows where a status change happened. Walk me through your approach.

Input Table: order_tracking

How to Handle This in the Interview

Before writing anything, think like: Let me think about how to compare each row with the previous row, that is the key operation here.

Step 1: Identify the core operation

• We need to compare each row's status with the status of the previous row for the same order. This is a classic use case for the LAG window function, it lets you look at the value from the previous row without a self join.

Step 2: Partition and order correctly

• We must partition by order_id so that the LAG lookup stays within the same order and does not bleed across orders. We order by status_date so the previous row is always the chronologically previous one.

Step 3: Handle the first row of each order

• The very first row of each order has no previous row, LAG returns NULL for it. A NULL previous status means this is a new order starting, so we include it as a status change. Say: I will use COALESCE or an IS NULL check to include the first row.

Step 4: Filter in a CTE

• We cannot filter on a window function result directly in WHERE in the same SELECT, wrap it in a CTE or subquery first, then filter.

Solution

SQL Server

WITH status_changes AS (
    SELECT
        order_id,
        status_date,
        status,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status
    FROM order_tracking
)
SELECT
    order_id,
    status_date,
    status
FROM status_changes
WHERE prev_status IS NULL OR status <> prev_status
ORDER BY order_id, status_date;

PostgreSQL

WITH status_changes AS (
    SELECT
        order_id,
        status_date,
        status,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status
    FROM order_tracking
)
SELECT
    order_id,
    status_date,
    status
FROM status_changes
WHERE prev_status IS NULL OR status <> prev_status
ORDER BY order_id, status_date;

MySQL 8+

WITH status_changes AS (
    SELECT
        order_id,
        status_date,
        status,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status
    FROM order_tracking
)
SELECT
    order_id,
    status_date,
    status
FROM status_changes
WHERE prev_status IS NULL OR status <> prev_status
ORDER BY order_id, status_date;

The syntax is identical across all three platforms for LAG and CTEs.

What to say: I use LAG to look at the previous row's status within the same order. If the previous status is NULL it means this is the first row for that order so I include it. If the previous status is different from the current status it means a change happened so I include that too. Any row where the status is the same as the previous row gets filtered out.

Expected Output

Follow-up Questions & Answers

Q1. What is the LAG function and how does it work?

LAG is a window function that lets you access the value of a column from a previous row within the same partition without writing a self join. It takes up to three arguments, the column name, the offset which defaults to 1 meaning one row back, and a default value to return when there is no previous row instead of NULL. For example LAG(status, 1, 'No Previous Status') would return the string 'No Previous Status' for the very first row of each partition instead of NULL. It is one of the most commonly used window functions in data engineering interviews because comparing a row to its neighbour is an extremely common real world problem.

Q2. What is the difference between LAG and LEAD?

LAG looks backwards, it fetches the value from a previous row. LEAD looks forwards, it fetches the value from a following row. Both take the same arguments: column, offset, and default. In this question we use LAG because we want to compare the current status with what came before it. If the requirement was instead find the date when each status will change next, we would use LEAD to look at the next row's status. A good way to remember it: LAG = look back, LEAD = look ahead.

Q3. What happens if two status updates happen on the same date for the same order?

This is an important edge case to raise. If status_date is not unique per order, the ORDER BY inside the window function becomes ambiguous, two rows on the same date could be ordered either way and LAG would give inconsistent results.

-- If there is a timestamp column available, use it instead
WITH status_changes AS (
    SELECT
        order_id,
        status_date,
        status,
        LAG(status) OVER (
            PARTITION BY order_id
            ORDER BY status_date, status_timestamp  -- use timestamp to break ties
        ) AS prev_status
    FROM order_tracking
)
SELECT
    order_id,
    status_date,
    status
FROM status_changes
WHERE prev_status IS NULL OR status <> prev_status
ORDER BY order_id, status_date;

What to say: I would always ask the interviewer whether status_date is guaranteed to be unique per order. If not I would look for a timestamp column or a sequence ID to use as a tiebreaker in the ORDER BY. Relying on ambiguous ordering in window functions leads to non-deterministic results which is a serious data quality issue in production.

Q4. Can you solve this without LAG using a self join?

Yes, before window functions existed this was the standard approach. It is less readable and less efficient but good to know.

SELECT
    t1.order_id,
    t1.status_date,
    t1.status
FROM order_tracking t1
LEFT JOIN order_tracking t2 ON t1.order_id = t2.order_id
    AND t2.status_date = (
        SELECT MAX(t3.status_date)
        FROM order_tracking t3
        WHERE t3.order_id    = t1.order_id
        AND t3.status_date < t1.status_date
    )
WHERE t2.status IS NULL
   OR t1.status <> t2.status
ORDER BY t1.order_id, t1.status_date;

What to say: This self join approach works but it is significantly more expensive — for each row it runs a correlated subquery to find the previous date. On a large table with millions of status updates this would be very slow. LAG solves the same problem in a single scan which is why window functions were introduced. I would only use the self join approach if the database does not support window functions.

Q5. How would you also capture what the status changed FROM and TO in the output?

The interviewer may ask you to show both the old and new status in the same row. Since we already have prev_status in the CTE, just include it in the SELECT.

WITH status_changes AS (
    SELECT
        order_id,
        status_date,
        status,
        LAG(status) OVER (PARTITION BY order_id ORDER BY status_date) AS prev_status
    FROM order_tracking
)
SELECT
    order_id,
    status_date,
    COALESCE(prev_status, 'New Order') AS changed_from,
    status AS changed_to
FROM status_changes
WHERE prev_status IS NULL
   OR status <> prev_status
ORDER BY order_id, status_date;

What to say: I use COALESCE on prev_status so that the first row of each order shows 'New Order' instead of NULL in the changed_from column — this makes the output much more readable for the business team.

The Interviewer Says

We run a subscription box service and want to identify our most engaged customers. I am giving you a purchases table and a products table. Write a query to find customers who have purchased every single product that exists in the products table, not just some of them, all of them. This is a relational division problem, take your time to think through it.

Input Table: products

Input Table: purchases

How to Handle This in the Interview

Before writing anything, think like: This looks like a relational division problem. I need customers whose purchased product set fully covers the entire products table.

Step 1: Recognise the pattern

• The phrase "bought every product" is the classic signal for relational division. Do not reach for a simple GROUP BY with a count comparison right away, explain why that is risky.

Step 2: Think about the naive approach and its flaw

• Say: One way is to count distinct products per customer and compare it to the total count of products. But this breaks if a customer buys a product that no longer exists in the products table, or if there is any duplicate or mismatch, counts can match by coincidence even if the actual sets differ.

Step 3: Use the NOT EXISTS double negation approach

• Say: The safest approach is to find customers where there does not exist any product in the products table that the customer has not purchased. This guarantees set coverage rather than relying on counts.

Step 4: Mention the alternative count-based approach as a simpler fallback

• Acknowledge that if the interviewer wants a simpler, faster-to-write solution and the data is clean, the count comparison is acceptable, but always state the trade-off.

Solution

SQL Server

SELECT DISTINCT
    p.customer_id
FROM purchases p
WHERE NOT EXISTS (
    SELECT 1
    FROM products pr
    WHERE NOT EXISTS (
        SELECT 1
        FROM purchases p2
        WHERE p2.customer_id = p.customer_id
          AND p2.product_id  = pr.product_id
    )
);

PostgreSQL

SELECT DISTINCT
    p.customer_id
FROM purchases p
WHERE NOT EXISTS (
    SELECT 1
    FROM products pr
    WHERE NOT EXISTS (
        SELECT 1
        FROM purchases p2
        WHERE p2.customer_id = p.customer_id
          AND p2.product_id  = pr.product_id
    )
);

MySQL 8+

SELECT DISTINCT
    p.customer_id
FROM purchases p
WHERE NOT EXISTS (
    SELECT 1
    FROM products pr
    WHERE NOT EXISTS (
        SELECT 1
        FROM purchases p2
        WHERE p2.customer_id = p.customer_id
          AND p2.product_id  = pr.product_id
    )
);

The syntax is identical across all three platforms for NOT EXISTS and correlated subqueries.

What to say: I am using a double NOT EXISTS pattern, sometimes called the "double negative" trick. The inner NOT EXISTS finds any product that this specific customer has not purchased. The outer NOT EXISTS then checks that no such missing product exists, meaning the customer has purchased everything. This is the textbook way to express relational division in SQL.

Expected Output

Follow-up Questions & Answers

Q1. What is relational division and why does this problem qualify as one?

Relational division is a relational algebra operation where you find rows in one table that are associated with every single row in another table, the opposite of a normal join which finds matches, division finds complete coverage. This problem qualifies because we are not asking "which customers bought a product", we are asking "which customers bought all products," which requires checking that no product is missing from a customer's purchase history. SQL has no native DIVIDE operator, so it is always expressed using either double NOT EXISTS, or a GROUP BY with HAVING COUNT comparison, or set operations like EXCEPT.

Q2. Can you write this using GROUP BY and HAVING COUNT instead?

Yes, this is the simpler and more common approach when the data is known to be clean, meaning no duplicate purchase rows and the products table is the single source of truth.

SELECT
    p.customer_id
FROM purchases p
GROUP BY p.customer_id
HAVING COUNT(DISTINCT p.product_id) = (SELECT COUNT(*) FROM products);

What to say: This counts how many distinct products each customer purchased and compares it to the total number of products available. It is shorter and usually faster to execute. The risk is if there are duplicate or orphaned product_id values in purchases that do not exist in the products table, the count could accidentally match even though the actual sets are different. I would only use this approach after confirming with the interviewer that the data is clean, or I would add a join condition to filter purchases down to valid products first.

Q3. How would you solve this using EXCEPT instead?

EXCEPT returns rows present in the first query but not in the second, it is a direct way to express "what is missing."

SELECT customer_id
FROM (
    SELECT DISTINCT customer_id FROM purchases
) all_customers
WHERE NOT EXISTS (
    SELECT product_id FROM products
    EXCEPT
    SELECT product_id FROM purchases WHERE customer_id = all_customers.customer_id
);

What to say: EXCEPT subtracts the customer's purchased products from the full products list. If the result is empty, that means the customer has no missing products, so they bought everything. This is essentially the same logic as the double NOT EXISTS approach but expressed using set operations, which some people find more intuitive to read. Note that EXCEPT is supported in SQL Server and PostgreSQL but MySQL did not support it until version 8.0.31, so I would confirm the MySQL version before using this.

Q4. What if the products table has a product that was discontinued and should be excluded from this check?

This is a great edge case to bring up proactively. If products can be marked inactive, the query should only check against active products, otherwise a customer would be unfairly excluded for not buying something that is no longer sold.

SELECT DISTINCT
    p.customer_id
FROM purchases p
WHERE NOT EXISTS (
    SELECT 1
    FROM products pr
    WHERE pr.is_active = 1
      AND NOT EXISTS (
        SELECT 1
        FROM purchases p2
        WHERE p2.customer_id = p.customer_id
          AND p2.product_id  = pr.product_id
    )
);

What to say: I added a filter on is_active inside the products subquery so discontinued products are excluded from the coverage check. I would always ask the interviewer whether the products table represents the current active catalog or the full historical catalog, since that changes the correctness of the result.

Q5. How would you optimise this query if both tables were very large?

What to say: The double NOT EXISTS pattern can be expensive because it is a correlated subquery running for every customer-product combination. I would focus on indexing and reducing the search space.

-- Composite index to make the correlated lookup fast
CREATE INDEX idx_purchases_customer_product ON purchases(customer_id, product_id);

-- Index on products if filtering by active status
CREATE INDEX idx_products_active ON products(is_active);

What to say: A composite index on customer_id and product_id together lets the inner correlated subquery use an index seek instead of a table scan, which is critical here since it runs once per customer per product. I would also check the query execution plan to confirm the optimiser is not falling back to nested loop joins over the entire table. If the products table is small, like under a few thousand rows, this query usually performs fine even at large purchase volumes because the inner subquery only needs to scan a small set.

The Interviewer Says

We have a table that logs daily active users for our app. Each row represents a date where at least one event was recorded. The data team suspects there might be missing dates where the pipeline silently failed to load any data. Write a query to find the missing date ranges, meaning gaps in the sequence of dates. This is a classic gaps and islands problem.

Input Table: daily_log

How to Handle This in the Interview

Before writing anything, think: This is a classic gaps and islands problem. I need to find where consecutive dates break, then express the missing range between them.

Step 1: Identify what defines a gap

• A gap exists between two consecutive rows when the difference between the current date and the previous date is more than one day. If the difference is exactly one day, there is no gap.

Step 2: Get the previous date using LAG

• Use LAG(log_date) ordered by log_date to bring the previous row's date alongside the current row. This lets us calculate the day difference in the same row.

Step 3: Filter where the gap is greater than one day

• Once we have the previous date next to the current date, filter for rows where DATEDIFF between them is greater than 1. The missing range is then the day right after the previous date up to the day right before the current date.

Step 4: Clarify what output format the interviewer wants

• Ask: Should I return the gap as a start and end date range, or list out every individual missing date? This changes whether you need a recursive CTE or a simple SELECT.

Solution

SQL Server

WITH dated_rows AS (
    SELECT
        log_date,
        LAG(log_date) OVER (ORDER BY log_date) AS prev_date
    FROM daily_log
)
SELECT
    DATEADD(DAY, 1, prev_date) AS gap_start,
    DATEADD(DAY, -1, log_date) AS gap_end
FROM dated_rows
WHERE DATEDIFF(DAY, prev_date, log_date) > 1;

PostgreSQL

WITH dated_rows AS (
    SELECT
        log_date,
        LAG(log_date) OVER (ORDER BY log_date) AS prev_date
    FROM daily_log
)
SELECT
    prev_date + INTERVAL '1 day' AS gap_start,
    log_date - INTERVAL '1 day'  AS gap_end
FROM dated_rows
WHERE log_date - prev_date > 1;

MySQL 8+

WITH dated_rows AS (
    SELECT
        log_date,
        LAG(log_date) OVER (ORDER BY log_date) AS prev_date
    FROM daily_log
)
SELECT
    DATE_ADD(prev_date, INTERVAL 1 DAY) AS gap_start,
    DATE_SUB(log_date, INTERVAL 1 DAY)  AS gap_end
FROM dated_rows
WHERE DATEDIFF(log_date, prev_date) > 1;

What to say: I use LAG to bring the previous log date next to the current row, then check whether the gap between them is more than one day. Wherever the gap exists, I calculate the missing range as the day after the previous date through the day before the current date. The date functions differ slightly across platforms but the core logic using LAG is identical.

Expected Output

Follow-up Questions & Answers

Q1. What is the gaps and islands problem in general?

Gaps and islands is a well known category of SQL problems where data has a sequence, usually dates or numbers, and you need to either find the missing breaks in that sequence, called gaps, or find the contiguous runs of present values, called islands. This pattern shows up constantly in real data engineering work, finding missing dates in a pipeline, finding consecutive login streaks for a user, or finding continuous periods where a sensor was online. The general technique is the same for both gaps and islands: use LAG or ROW_NUMBER to detect where consecutive values break, then group or filter based on that break point.

Q2. How would you list every individual missing date instead of just the start and end range?

If the interviewer wants each missing date listed individually rather than a range, you need a recursive CTE or a numbers table to generate every date between the gap boundaries.

-- SQL Server using a recursive CTE
WITH dated_rows AS (
    SELECT
        log_date,
        LAG(log_date) OVER (ORDER BY log_date) AS prev_date
    FROM daily_log
),
gaps AS (
    SELECT
        DATEADD(DAY, 1, prev_date) AS gap_start,
        DATEADD(DAY, -1, log_date) AS gap_end
    FROM dated_rows
    WHERE DATEDIFF(DAY, prev_date, log_date) > 1
),
all_missing_dates AS (
    SELECT gap_start AS missing_date, gap_end
    FROM gaps

    UNION ALL

    SELECT DATEADD(DAY, 1, missing_date), gap_end
    FROM all_missing_dates
    WHERE DATEADD(DAY, 1, missing_date) <= gap_end
)
SELECT missing_date
FROM all_missing_dates
OPTION (MAXRECURSION 1000);

What to say: I expand each gap range into individual dates using a recursive CTE, starting from gap_start and adding one day at a time until I reach gap_end. I would always set a sensible MAXRECURSION limit in SQL Server to avoid an infinite loop if the data has an unexpectedly huge gap, which usually signals a bigger upstream problem worth flagging separately.

Q3. How is this different from an islands problem? Can you find the contiguous date ranges instead?

Islands is the opposite of gaps, instead of finding where the sequence breaks, you find the continuous runs where it does not break. The trick is to subtract a row number from the date, which stays constant for consecutive dates and changes whenever there is a break.

WITH numbered_rows AS (
    SELECT
        log_date,
        ROW_NUMBER() OVER (ORDER BY log_date) AS rn
    FROM daily_log
),
island_groups AS (
    SELECT
        log_date,
        DATEADD(DAY, -rn, log_date) AS island_id
    FROM numbered_rows
)
SELECT
    MIN(log_date) AS island_start,
    MAX(log_date) AS island_end
FROM island_groups
GROUP BY island_id
ORDER BY island_start;

What to say: Subtracting the row number from the date is a clever trick, for any run of consecutive dates, this subtraction produces the same constant value, effectively giving each island a unique group ID. I then group by that ID and take the min and max date in each group to get the island boundaries. This is a very common follow-up because it tests whether you understand the technique conceptually rather than just memorising one query.

Q4. What if there could be duplicate dates in the daily_log table — does your gap query still work?

This is an important edge case. Duplicate dates do not break the gap detection logic itself since LAG still correctly identifies the previous distinct date in sequence, but duplicates can cause the gap query to evaluate the same gap multiple times if not handled.

WITH distinct_dates AS (
    SELECT DISTINCT log_date FROM daily_log
),
dated_rows AS (
    SELECT
        log_date,
        LAG(log_date) OVER (ORDER BY log_date) AS prev_date
    FROM distinct_dates
)
SELECT
    DATEADD(DAY, 1, prev_date) AS gap_start,
    DATEADD(DAY, -1, log_date) AS gap_end
FROM dated_rows
WHERE DATEDIFF(DAY, prev_date, log_date) > 1;

What to say: I add a DISTINCT step before applying LAG so that duplicate log entries on the same date do not get treated as separate rows in the sequence. This keeps the gap detection accurate regardless of how many events were logged on a given day.

Q5. How would you turn this into a monitoring query that runs daily and alerts if today's date itself is missing?

What to say: This is a practical extension, instead of just looking backward at historical gaps, the pipeline needs to also check if data simply has not arrived yet for the most recent expected date.

SELECT
    CASE
        WHEN MAX(log_date) < CAST(GETDATE() AS DATE) - 1
        THEN 'ALERT: No data loaded for ' + CAST(CAST(GETDATE() AS DATE) - 1 AS VARCHAR)
        ELSE 'OK'
    END AS pipeline_status
FROM daily_log;

What to say: I compare the most recent log_date against yesterday's date, since today's data may not have fully loaded yet depending on the pipeline schedule. If the max date is older than expected, I raise an alert. In a real production setup I would wrap this in a stored procedure or orchestration tool like Airflow that sends a Slack or email alert rather than just returning a status string.

The Interviewer Says

We have a sales table with one row per product per month. The finance team wants a report where each month becomes its own column instead of separate rows, so they can scan across a single row to compare a product's performance month over month. Write a query to pivot this data. Walk me through how you would approach it.

Input Table: monthly_sales

How to Handle This in the Interview

Before writing anything, think: Pivoting means turning row values into column headers, I have two ways to do this, a manual CASE WHEN approach or the database's built-in PIVOT syntax. Let me think about which fits better here.

Step 1: Recognise this is a pivot operation

• The shape of the data needs to change from long format, one row per product-month, to wide format, one row per product with a column per month. This is fundamentally different from a normal aggregation.

Step 2: Decide between CASE WHEN and native PIVOT

• Say: CASE WHEN works identically across every SQL platform and is easy to read. Native PIVOT syntax exists in SQL Server but is not standard across PostgreSQL or MySQL, so I would default to CASE WHEN for portability unless the interviewer specifies SQL Server only.

Step 3: Identify the aggregation needed inside each CASE

• Even though there is only one row per product per month here, always wrap the CASE WHEN inside an aggregate function like SUM or MAX. Say: I wrap it in SUM so the query still works correctly even if there were duplicate rows for the same product and month.

Step 4: Ask about dynamic vs static columns

• Ask: Is the list of months fixed, like always Jan through Mar, or does it change dynamically based on what is in the data? That changes whether I can hardcode the columns or need dynamic SQL.

Solution

SQL Server

SELECT
    product_id,
    SUM(CASE WHEN sale_month = 'Jan' THEN revenue END) AS Jan,
    SUM(CASE WHEN sale_month = 'Feb' THEN revenue END) AS Feb,
    SUM(CASE WHEN sale_month = 'Mar' THEN revenue END) AS Mar
FROM monthly_sales
GROUP BY product_id
ORDER BY product_id;

PostgreSQL

SELECT
    product_id,
    SUM(CASE WHEN sale_month = 'Jan' THEN revenue END) AS Jan,
    SUM(CASE WHEN sale_month = 'Feb' THEN revenue END) AS Feb,
    SUM(CASE WHEN sale_month = 'Mar' THEN revenue END) AS Mar
FROM monthly_sales
GROUP BY product_id
ORDER BY product_id;

MySQL 8+

SELECT
    product_id,
    SUM(CASE WHEN sale_month = 'Jan' THEN revenue END) AS Jan,
    SUM(CASE WHEN sale_month = 'Feb' THEN revenue END) AS Feb,
    SUM(CASE WHEN sale_month = 'Mar' THEN revenue END) AS Mar
FROM monthly_sales
GROUP BY product_id
ORDER BY product_id;

The CASE WHEN pivot pattern is identical across all three platforms, this is the most portable way to pivot data in SQL.

What to say: For each month I create a conditional column that only picks up the revenue when sale_month matches, otherwise it is NULL. Wrapping it in SUM collapses the NULLs and keeps the real value, and since I GROUP BY product_id, each product ends up as exactly one row with its revenue spread across the month columns.

Expected Output

Follow-up Questions & Answers

Q1. How would you do this using SQL Server's native PIVOT syntax instead?

SQL Server has built-in PIVOT syntax which can be more concise, though it is less portable and harder to read for people unfamiliar with it.

SELECT
    product_id,
    [Jan], [Feb], [Mar]
FROM monthly_sales
PIVOT (
    SUM(revenue)
    FOR sale_month IN ([Jan], [Feb], [Mar])
) AS pivot_table;

What to say: Native PIVOT requires you to hardcode the column names inside the FOR clause using square brackets in SQL Server. It does the same job as the CASE WHEN approach but in a more compact syntax. I personally prefer CASE WHEN for interviews and for portability since PIVOT syntax differs significantly across database platforms, but it is good to show I know both.

Q2. What if the months are not fixed and could be any value, how would you handle dynamic columns?

This requires dynamic SQL, since you cannot have a variable number of columns in standard SQL, the column list must be known at parse time.

DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(sale_month) + ','
FROM (SELECT DISTINCT sale_month FROM monthly_sales) AS months;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

SET @sql = '
SELECT product_id, ' + @columns + '
FROM monthly_sales
PIVOT (
    SUM(revenue)
    FOR sale_month IN (' + @columns + ')
) AS pivot_table;';

EXEC sp_executesql @sql;

What to say: I first query the distinct list of months that actually exist in the data, build that into a comma separated string of column names, then construct and execute the full PIVOT query as a dynamic string. This is necessary whenever the column set is not known in advance, for example if new months keep getting added over time. I would always be cautious with dynamic SQL around SQL injection if any part of the column list comes from user input rather than trusted internal data.

Q3. How would you reverse this — unpivot wide data back into long format?

The opposite operation, unpivot, is also commonly asked. It is needed when you receive a wide report and need to normalise it back into rows for storage or further processing.

SELECT
    product_id,
    sale_month,
    revenue
FROM monthly_sales_wide
UNPIVOT (
    revenue FOR sale_month IN ([Jan], [Feb], [Mar])
) AS unpvt;

What to say: UNPIVOT takes the wide columns and stacks them back into rows, generating a sale_month column from the original column names and a revenue column from their values. This is the inverse operation and comes up often when ingesting spreadsheet-style data from business teams who naturally produce wide formats but our warehouse needs normalised long formats for efficient storage and querying.

Q4. What happens if a product has no sales in a particular month, does it show up as 0 or NULL in your pivoted output?

This is an important detail to clarify. With the CASE WHEN approach, if a product genuinely has zero rows for a given month, that month's column will show NULL, not 0, because there is no row to aggregate at all.

SELECT
    product_id,
    COALESCE(SUM(CASE WHEN sale_month = 'Jan' THEN revenue END), 0) AS Jan,
    COALESCE(SUM(CASE WHEN sale_month = 'Feb' THEN revenue END), 0) AS Feb,
    COALESCE(SUM(CASE WHEN sale_month = 'Mar' THEN revenue END), 0) AS Mar
FROM monthly_sales
GROUP BY product_id
ORDER BY product_id;

What to say: I wrap each SUM in COALESCE to convert NULL into 0 wherever a product has no sales recorded for that month. I would ask the interviewer whether NULL or 0 is the expected business meaning here, NULL technically means no data was recorded, while 0 means sales happened but totalled zero. These carry different meanings to a finance team and the distinction genuinely matters in reporting.

Q5. How would you extend this to also show a total column across all months?

A common real world extension, finance teams almost always want a running total alongside the monthly breakdown.

SELECT
    product_id,
    SUM(CASE WHEN sale_month = 'Jan' THEN revenue END) AS Jan,
    SUM(CASE WHEN sale_month = 'Feb' THEN revenue END) AS Feb,
    SUM(CASE WHEN sale_month = 'Mar' THEN revenue END) AS Mar,
    SUM(revenue) AS total_revenue
FROM monthly_sales
GROUP BY product_id
ORDER BY product_id;

What to say: Since SUM(revenue) without a CASE WHEN condition aggregates across all rows for that product regardless of month, it naturally gives the total. I add it as one extra column alongside the pivoted months, this is a simple addition but shows I am thinking about what the business actually needs from the report, not just literally answering the pivot request.

The Interviewer Says

We track monthly revenue for the company. Finance wants two things in a single report, a running cumulative total of revenue through each month, and the percentage growth compared to the previous month. Write a query that produces both. Walk me through your thinking.

Input Table: monthly_revenue

How to Handle This in the Interview

Before writing anything, think: This needs two separate window function calculations on the same ordered data, a running sum and a row-to-previous-row comparison. Let me handle them one at a time.

Step 1: Identify the running total operation

• A running total means each row shows the sum of all values up to and including that row. This is done with SUM(revenue) OVER (ORDER BY revenue_month), without a PARTITION BY since we want one continuous running total across all months.

Step 2: Identify the month-over-month growth operation

• Growth percentage requires comparing the current row to the previous row, which means LAG. The formula is (current - previous) / previous * 100.

Step 3: Watch out for division by zero

• Say: If the previous month's revenue was 0, dividing by it would error out or return an undefined value. I need to guard against that with a NULLIF or a CASE check.

Step 4: Confirm the default window frame

• Mention: By default, SUM OVER ORDER BY uses a frame of RANGE UNBOUNDED PRECEDING TO CURRENT ROW, which is exactly what a running total needs, I do not need to specify it manually unless I want ROWS instead of RANGE for tie-breaking behaviour.

Solution

SQL Server

SELECT
    revenue_month,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue_month ROWS UNBOUNDED PRECEDING) AS running_total,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY revenue_month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY revenue_month), 0), 2) AS mom_growth_pct
FROM monthly_revenue
ORDER BY revenue_month;

PostgreSQL

SELECT
    revenue_month,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue_month ROWS UNBOUNDED PRECEDING) AS running_total,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY revenue_month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY revenue_month), 0), 2) AS mom_growth_pct
FROM monthly_revenue
ORDER BY revenue_month;

MySQL 8+

SELECT
    revenue_month,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue_month ROWS UNBOUNDED PRECEDING) AS running_total,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY revenue_month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY revenue_month), 0), 2) AS mom_growth_pct
FROM monthly_revenue
ORDER BY revenue_month;

The syntax is identical across all three platforms for this query.

What to say: I use SUM with ROWS UNBOUNDED PRECEDING to build the running total, each row adds itself to the sum of everything before it. For growth, I compute the difference between the current and previous month's revenue using LAG, divide by the previous month, and multiply by 100. I wrap the previous month value in NULLIF so that if it is ever 0, the division returns NULL instead of throwing a divide by zero error.

Expected Output

Follow-up Questions & Answers

Q1. Why did you use ROWS UNBOUNDED PRECEDING instead of just leaving the default frame?

The default frame when you write OVER (ORDER BY ...) without specifying ROWS or RANGE is RANGE UNBOUNDED PRECEDING TO CURRENT ROW. RANGE and ROWS behave identically when the ORDER BY column has unique values, but RANGE treats tied values as part of the same logical group, meaning if two rows had the exact same revenue_month, RANGE would sum them together into the same running total step instead of accumulating row by row. I explicitly use ROWS to guarantee row-by-row accumulation regardless of ties, which is safer and more predictable for a running total. I would always prefer being explicit about ROWS versus RANGE rather than relying on the default, since the behaviour difference is a common source of subtle bugs.

Q2. What does NULLIF actually do and why is it the right tool here instead of a CASE statement?

NULLIF(a, b) returns NULL if a equals b, otherwise it returns a. Here NULLIF(previous_revenue, 0) returns NULL whenever the previous month's revenue was exactly 0, which makes the division return NULL automatically instead of erroring. This is functionally equivalent to writing a CASE statement like CASE WHEN previous_revenue = 0 THEN NULL ELSE previous_revenue END, but NULLIF is shorter and more idiomatic for this specific zero-guard pattern. I would use NULLIF whenever the goal is purely "treat this specific value as NULL," and reserve CASE for situations with more than two outcomes or more complex conditional logic.

Q3. How would you modify this to show a 3-month rolling average instead of a running total since the start?

A rolling average over a fixed window, rather than an ever-growing running total, requires bounding the frame to a specific number of preceding rows instead of UNBOUNDED PRECEDING.

SELECT
    revenue_month,
    revenue,
    AVG(revenue) OVER (ORDER BY revenue_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_month_avg
FROM monthly_revenue
ORDER BY revenue_month;

What to say: I change SUM to AVG and bound the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which means the current row plus the two rows before it, three rows total. For the first two months in the data, the average is simply computed over however many rows are actually available since there is nothing before them, SQL does not error out, it just uses what exists. This pattern is extremely common for smoothing out noisy monthly data in real dashboards.

Q4. The running total resets every year in reality, how would you make it reset on January each year instead of continuing forever?

This is a great real world twist. A running total that needs to restart periodically requires PARTITION BY on the reset boundary, here the year extracted from revenue_month.

SELECT
    revenue_month,
    revenue,
    SUM(revenue) OVER (PARTITION BY LEFT(revenue_month, 4) ORDER BY revenue_month ROWS UNBOUNDED PRECEDING) AS running_total_ytd
FROM monthly_revenue
ORDER BY revenue_month;

What to say: I add a PARTITION BY on the year portion of revenue_month, which resets the running total calculation independently for each year while keeping the row ordering within that year intact. This pattern, partition by year, order by month, is exactly how you would compute a year-to-date running total in a real finance reporting pipeline, and it is a very natural follow-up because PARTITION BY combined with an ordered window is one of the most important concepts to internalise.

Q5. If the interviewer asks you to also flag months where growth dropped by more than 20 percent as a risk alert, how would you do that?

This tests whether you can layer simple business logic on top of a window function result, which usually means wrapping the window function query in a CTE first.

WITH growth_calc AS (
    SELECT
        revenue_month,
        revenue,
        ROUND((revenue - LAG(revenue) OVER (ORDER BY revenue_month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY revenue_month), 0), 2) AS mom_growth_pct
    FROM monthly_revenue
)
SELECT
    revenue_month,
    revenue,
    mom_growth_pct,
    CASE
        WHEN mom_growth_pct < -20 THEN 'Risk Alert'
        ELSE 'Normal'
    END AS status
FROM growth_calc
ORDER BY revenue_month;

What to say: I cannot reference mom_growth_pct directly in a CASE statement within the same SELECT as where it is calculated using a window function, so I wrap the calculation in a CTE first, then apply the business rule in the outer query. This two-layer pattern, calculate in a CTE then apply logic outside, is something I use constantly when window function results need further conditional processing.

The Interviewer Says

We want to identify churned customers. A customer is considered churned if they purchased something last year but have not purchased anything this year. I am giving you a single orders table with all transactions across both years. Write a query to find these customers. Walk me through your approach before coding.

Input Table: orders

How to Handle This in the Interview

Before writing anything, think: I need to find customers present in last year's purchase set but absent from this year's purchase set. This is essentially an anti join problem.

Step 1: Split the problem into two sets

• First identify all customers who ordered in 2023. Then identify all customers who ordered in 2024. The churned customers are the ones in the first set but not in the second.

Step 2: Pick the right join type

• Say: A LEFT JOIN from last year's customers to this year's customers, filtering where the right side is NULL, gives me exactly the customers who exist in one set but not the other. This pattern is called an anti join.

Step 3: Use derived tables or CTEs to keep it readable

• Building two separate CTEs, one for each year's distinct customers, makes the final join much easier to read than trying to filter dates inline in a single complex query.

Step 4: Ask about the year boundary

• Ask: Should this be calendar year based, January to December, or should it be a rolling twelve month window from today's date? These give very different results and is a common clarification interviewers expect.

Solution

SQL Server

WITH last_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
),
this_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
)
SELECT
    ly.customer_id
FROM last_year_customers ly
LEFT JOIN this_year_customers ty ON ly.customer_id = ty.customer_id
WHERE ty.customer_id IS NULL;

PostgreSQL

WITH last_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2023
),
this_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = 2024
)
SELECT
    ly.customer_id
FROM last_year_customers ly
LEFT JOIN this_year_customers ty ON ly.customer_id = ty.customer_id
WHERE ty.customer_id IS NULL;

MySQL 8+

WITH last_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
),
this_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
)
SELECT
    ly.customer_id
FROM last_year_customers ly
LEFT JOIN this_year_customers ty ON ly.customer_id = ty.customer_id
WHERE ty.customer_id IS NULL;

Only the date extraction function differs slightly between platforms, the join logic itself stays the same everywhere.

What to say: I build two separate CTEs, customers who ordered in 2023 and customers who ordered in 2024. Then I left join the 2023 set to the 2024 set on customer_id. Wherever the right side comes back NULL, that means the customer had no matching order in 2024, so they qualify as churned.

Expected Output

Follow-up Questions & Answers

Q1. Can you write this using NOT IN instead of a LEFT JOIN? What are the risks?

Yes, this is a common alternative but it carries a hidden danger with NULL values.

SELECT DISTINCT customer_id
FROM orders
WHERE YEAR(order_date) = 2023
AND customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
);

What to say: NOT IN works fine as long as the subquery never returns a NULL value in customer_id. But if even one row in the 2024 subquery has a NULL customer_id, the entire NOT IN comparison silently returns no rows at all, because comparing anything to NULL is unknown rather than true or false. This is a well known SQL trap. I personally avoid NOT IN with subqueries for this reason and prefer LEFT JOIN with an IS NULL check, or NOT EXISTS, both of which handle NULLs safely.

Q2. How would you write this using NOT EXISTS instead?

NOT EXISTS is generally considered the safest and often the most efficient way to express this kind of anti join logic.

SELECT DISTINCT customer_id
FROM orders o1
WHERE YEAR(o1.order_date) = 2023
AND NOT EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
    AND YEAR(o2.order_date) = 2024
);

What to say: NOT EXISTS checks row by row whether a matching record exists in 2024 for that customer, and it has no NULL related pitfalls like NOT IN does. Most database optimisers also handle NOT EXISTS very efficiently since it can stop searching the moment it finds one match. I tend to favour this pattern in production code over NOT IN for exactly this safety reason.

Q3. What if a customer ordered in 2023 and also ordered again later in 2023, would your query count them once or multiple times?

Good catch to bring up. Because the last_year_customers CTE uses SELECT DISTINCT, each customer appears exactly once in that set regardless of how many orders they placed in 2023. The same applies to this_year_customers. So the final result naturally has no duplicates, even if the underlying orders table has many rows per customer.

What to say: I deliberately use DISTINCT inside both CTEs so the join operates on unique customers per year rather than on raw order rows. Without that, a customer with five orders in 2023 could appear up to five times if I joined directly off the raw orders table, which would be incorrect for this kind of churn analysis.

Q4. How would you extend this to a rolling twelve month definition instead of calendar year?

If the business wants a rolling window rather than fixed calendar years, the date filtering changes but the anti join structure stays the same.

WITH prior_period_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= DATEADD(MONTH, -24, GETDATE())
    AND   order_date <  DATEADD(MONTH, -12, GETDATE())
),
recent_period_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= DATEADD(MONTH, -12, GETDATE())
)
SELECT
    pp.customer_id
FROM prior_period_customers pp
LEFT JOIN recent_period_customers rp ON pp.customer_id = rp.customer_id
WHERE rp.customer_id IS NULL;

What to say: Instead of hardcoding calendar years, I define two rolling twelve month windows relative to today's date using DATEADD. The prior period covers months thirteen through twenty four ago, and the recent period covers the last twelve months. This is closer to how churn is typically measured in real subscription or retail businesses, since calendar year boundaries are somewhat arbitrary and a customer who bought in December and skipped January should not necessarily be flagged immediately as churned under a rolling definition.

Q5. How would you also calculate how many days it has been since each churned customer's last order?

A natural follow up the business team would actually want, since churned is binary but recency tells a richer story.

WITH last_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2023
),
this_year_customers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE YEAR(order_date) = 2024
),
churned AS (
    SELECT ly.customer_id
    FROM last_year_customers ly
    LEFT JOIN this_year_customers ty ON ly.customer_id = ty.customer_id
    WHERE ty.customer_id IS NULL
)
SELECT
    c.customer_id,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(DAY, MAX(o.order_date), GETDATE()) AS days_since_last_order
FROM churned c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

What to say: I join the churned customer list back to the full orders table, take the maximum order_date per customer to get their true last purchase, and use DATEDIFF against today's date to show how long they have been inactive. This turns a simple yes or no churn flag into something the business can actually prioritise outreach with, customers inactive for ninety days might get a different campaign than someone inactive for four hundred days.

The Interviewer Says

We manage meeting room bookings. Each row represents a booking with a start and end time for a specific room. Before we let a new booking go through, we need to detect if any existing bookings for the same room overlap with each other. Write a query that returns all pairs of bookings that overlap in time. Walk me through your thinking before writing the query.

Input Table: room_bookings

How to Handle This in the Interview

Before writing anything, think: To find overlaps I need to compare every booking against every other booking for the same room, which means a self join. The tricky part is getting the overlap condition mathematically correct.

Step 1: Recall the standard overlap formula

• Two time ranges overlap if and only if start_time_1 < end_time_2 AND end_time_1 > start_time_2. Say this out loud, this exact formula is something interviewers want to hear stated correctly rather than guessed at.

Step 2: Self join on the same room

• Join the table to itself where room_id matches, since bookings in different rooms can never overlap with each other regardless of time.

Step 3: Avoid duplicate and self comparing pairs

• Without care, a self join would compare B01 to B01, and also produce both B01 with B02 and B02 with B01 as separate rows. Add a condition like b1.booking_id < b2.booking_id to keep only one direction per pair and exclude comparing a row to itself.

Step 4: Clarify the boundary condition

• Ask: If one booking ends exactly when another starts, like 10:00 to 10:00, should that count as an overlap or as back to back bookings that are fine? This changes whether you use strict less than or less than or equal to in the formula.

Solution

SQL Server

SELECT
    b1.booking_id AS booking_1,
    b2.booking_id AS booking_2,
    b1.room_id,
    b1.start_time AS booking_1_start,
    b1.end_time AS booking_1_end,
    b2.start_time AS booking_2_start,
    b2.end_time AS booking_2_end
FROM room_bookings b1
JOIN room_bookings b2 ON b1.room_id = b2.room_id
    AND b1.booking_id < b2.booking_id
WHERE b1.start_time < b2.end_time
  AND b1.end_time > b2.start_time;

PostgreSQL

SELECT
    b1.booking_id AS booking_1,
    b2.booking_id AS booking_2,
    b1.room_id,
    b1.start_time AS booking_1_start,
    b1.end_time AS booking_1_end,
    b2.start_time AS booking_2_start,
    b2.end_time AS booking_2_end
FROM room_bookings b1
JOIN room_bookings b2 ON  b1.room_id = b2.room_id
    AND b1.booking_id < b2.booking_id
WHERE b1.start_time < b2.end_time
  AND b1.end_time > b2.start_time;

MySQL 8+

SELECT
    b1.booking_id AS booking_1,
    b2.booking_id AS booking_2,
    b1.room_id,
    b1.start_time AS booking_1_start,
    b1.end_time AS booking_1_end,
    b2.start_time AS booking_2_start,
    b2.end_time AS booking_2_end
FROM room_bookings b1
JOIN room_bookings b2 ON  b1.room_id = b2.room_id
    AND b1.booking_id < b2.booking_id
WHERE b1.start_time < b2.end_time
  AND b1.end_time > b2.start_time;

The self join overlap pattern is identical across all three platforms.

What to say: I join the table to itself on room_id so I am only comparing bookings within the same room. The booking_id less than condition stops me from comparing a row to itself and also stops me from getting the same pair twice in both directions. Then the actual overlap check is the standard formula, one booking starts before the other ends, and ends after the other starts. If both conditions are true, the two time ranges must intersect somewhere.

Expected Output

Follow-up Questions & Answers

Q1. Why does the formula start_time_1 less than end_time_2 AND end_time_1 greater than start_time_2 correctly detect overlap?

This is the standard interval intersection test from computer science, and interviewers love asking you to justify it rather than just write it. Two ranges fail to overlap only in two scenarios, either the first range ends before the second one starts, or the first range starts after the second one ends. The formula is essentially the logical negation of those two failure conditions. If neither failure condition is true, meaning the first range does not end before the second starts, and the first range does not start after the second ends, then the ranges must share at least some common time. It works regardless of which range is longer or which one starts first, because the condition is symmetric in what it is checking even though it looks directional.

Q2. The boundary case, what if one booking ends at exactly 10:00 and another starts at exactly 10:00, does your query flag that as an overlap?

With the strict less than and greater than operators used here, no, it does not flag that as an overlap, which is the more common business expectation for booking systems, since back to back meetings should be allowed.

-- If the interviewer wants touching boundaries to also count as overlap
WHERE b1.start_time <= b2.end_time AND b1.end_time >= b2.start_time

What to say: With strict inequality, a booking ending at 10:00 and another starting at 10:00 are treated as non overlapping, since there is zero duration of actual time intersection between them. If the interviewer specifically wants touching endpoints to count as a conflict, I would switch to less than or equal to and greater than or equal to instead. I always ask which behaviour is wanted rather than assuming, since this single character difference completely changes the result set.

Q3. This self join could get expensive on a large bookings table, how would you optimise it?

A great performance follow up. The self join compares every booking against every other booking in the same room, which can get slow as the table grows, especially without the right index in place.

-- Composite index to support the join and filter efficiently
CREATE INDEX idx_bookings_room_time ON room_bookings(room_id, start_time, end_time);

What to say: I would add a composite index on room_id, start_time, and end_time together. This lets the database quickly narrow down to bookings within the same room and then efficiently filter by the time range conditions, rather than scanning the entire table for every comparison. I would also check the query execution plan to confirm the database is actually using this index for an index seek rather than falling back to a full table scan, since self joins on inequality conditions can sometimes confuse the optimiser into choosing a less efficient plan.

Q4. How would you modify this to also include the duration of the overlap itself, not just which bookings overlap?

The interviewer may want the actual overlapping minutes calculated, which is a common real requirement for billing or conflict severity reporting.

SELECT
    b1.booking_id AS booking_1,
    b2.booking_id AS booking_2,
    b1.room_id,
    DATEDIFF(
        MINUTE,
        CASE WHEN b1.start_time > b2.start_time THEN b1.start_time ELSE b2.start_time END,
        CASE WHEN b1.end_time < b2.end_time THEN b1.end_time ELSE b2.end_time END
    ) AS overlap_minutes
FROM room_bookings b1
JOIN room_bookings b2 ON  b1.room_id = b2.room_id
    AND b1.booking_id < b2.booking_id
WHERE b1.start_time < b2.end_time
  AND b1.end_time > b2.start_time;

What to say: The overlap window itself is the later of the two start times through the earlier of the two end times. I use CASE WHEN to pick the maximum of the two start times and the minimum of the two end times, then take the difference in minutes between them using DATEDIFF. This gives the actual overlapping duration rather than just a yes or no flag, which is genuinely more useful for understanding how severe a scheduling conflict is.

Q5. How would you check if a brand new incoming booking conflicts with any existing booking, rather than checking all existing bookings against each other?

This is the realistic production scenario, checking one new booking before confirming it, rather than auditing the whole table at once.

-- Assume the new booking is for R101, 09:45 to 10:15
SELECT
    booking_id,
    start_time,
    end_time
FROM room_bookings
WHERE room_id = 'R101'
  AND start_time < '2024-03-01 10:15:00'
  AND end_time   > '2024-03-01 09:45:00';

What to say: Instead of a self join across the whole table, this is a single lookup, I filter to the same room and apply the same overlap formula but against the fixed start and end time of the new booking I am trying to insert. If this query returns any rows at all, the new booking conflicts with something already on the calendar and should be rejected. This is exactly the kind of check that would run inside an application's booking confirmation logic before the insert actually happens.

The Interviewer Says

Marketing wants to segment customers into four equal sized tiers based on their total lifetime spend, so they can target the top tier with a loyalty program. Write a query that divides customers into four buckets, where bucket one represents the highest spenders. Walk me through how you would think about this.

Input Table: customer_spend

How to Handle This in the Interview

Before writing anything, say: Dividing customers into equal sized buckets based on a ranking is a classic use case for NTILE, I want to make sure I pick that over RANK or DENSE_RANK since the goal here is equal group sizes, not handling ties in value.

Step 1: Recognise the difference between ranking and bucketing

• Ranking functions like RANK assign a position to each row. NTILE is different, it does not rank individual values, it distributes rows into a specified number of roughly equal sized groups based on the ordering you give it.

Step 2: Decide the sort direction

• Since bucket one should represent the highest spenders, order by total_spend descending inside the NTILE window function.

Step 3: Think about what happens when row count is not evenly divisible

• Say: With eight customers and four buckets, this divides perfectly evenly, two customers per bucket. But if the row count is not divisible evenly by the bucket count, NTILE distributes the extra rows to the earlier buckets first, I should mention that the bucket sizes might differ by one row in those cases.

Step 4: Clarify what label the business wants

• Ask: Should the buckets be labelled as numbers one through four, or does marketing want named tiers like Platinum, Gold, Silver, Bronze? This affects whether a simple NTILE call is enough or whether a CASE WHEN needs to sit on top of it.

Solution

SQL Server

SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_tier
FROM customer_spend
ORDER BY spend_tier, total_spend DESC;

PostgreSQL

SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_tier
FROM customer_spend
ORDER BY spend_tier, total_spend DESC;

MySQL 8+

SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_tier
FROM customer_spend
ORDER BY spend_tier, total_spend DESC;

NTILE syntax is identical across all three platforms.

What to say: NTILE with the value 4 splits all customers into four groups based on their position once sorted by total_spend descending. Since the ordering is descending, bucket one ends up containing the customers with the highest spend, which is exactly what marketing wants for their top tier loyalty program.

Expected Output

Follow-up Questions & Answers

Q1. What exactly does NTILE do differently compared to RANK or DENSE_RANK?

RANK and DENSE_RANK answer the question what position does this row hold relative to others, based purely on its value, and ties get the same rank. NTILE answers a completely different question, which equal sized group does this row fall into once everything is sorted, regardless of whether values are tied or not. NTILE does not care about ties in the underlying value at all, it only cares about dividing the total row count into the number of groups you specify. This is the key reason I reached for NTILE here rather than RANK, the business requirement was explicitly about four equal sized buckets for a loyalty program, not about precisely ranking each individual customer's spend value.

Q2. With eight customers and four buckets the split was perfectly even, what happens with an uneven number, say nine customers into four buckets?

This is exactly the kind of follow up interviewers ask to see if you actually understand the mechanics rather than just having memorised the syntax. NTILE distributes the remainder rows to the earliest buckets first when the total does not divide evenly.

-- With 9 rows and NTILE(4), the distribution becomes 3, 2, 2, 2
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_tier
FROM customer_spend
ORDER BY spend_tier, total_spend DESC;

What to say: With nine rows split into four buckets, nine divided by four leaves a remainder of one, so NTILE gives the first bucket three rows and the remaining three buckets two rows each. The extra row always goes to the earliest groups in the ordering. I would mention this explicitly to the business stakeholder, since they might assume every tier has exactly the same number of customers, and a small size difference of one row could matter if they are doing something sensitive like assigning a fixed budget per tier.

Q3. Two customers in the data have the exact same total_spend value, but NTILE puts them in different tiers, is that a bug?

This is not a bug, it is expected and important behaviour to understand. NTILE only respects the row count target for each bucket, it does not check whether tied values end up split across a bucket boundary. If two customers both have exactly 65000 and they happen to fall right at the edge between bucket two and bucket three based on row position alone, NTILE will place them in different tiers even though their underlying spend is identical.

-- To see this clearly, compare NTILE against DENSE_RANK on the same data
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC)       AS spend_tier,
    DENSE_RANK() OVER (ORDER BY total_spend DESC)   AS spend_rank
FROM customer_spend
ORDER BY total_spend DESC;

What to say: I would flag this clearly to whoever requested the tiering, since splitting two customers with identical spend into different loyalty tiers could look unfair or arbitrary from a customer relations standpoint. If equal spend must always land in the same tier even at the cost of unequal bucket sizes, NTILE is actually the wrong tool entirely, and I would instead compute percentile thresholds using PERCENT_RANK or manually defined spend ranges with CASE WHEN.

Q4. How would you turn the numeric tier output into named labels like Platinum, Gold, Silver, Bronze for the business team?

A very common real world ask, the raw bucket number from NTILE is rarely what gets shown directly to a non technical stakeholder.

WITH tiered AS (
    SELECT
        customer_id,
        total_spend,
        NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_tier
    FROM customer_spend
)
SELECT
    customer_id,
    total_spend,
    CASE spend_tier
        WHEN 1 THEN 'Platinum'
        WHEN 2 THEN 'Gold'
        WHEN 3 THEN 'Silver'
        WHEN 4 THEN 'Bronze'
    END AS loyalty_tier
FROM tiered
ORDER BY spend_tier, total_spend DESC;

What to say: I wrap the NTILE calculation in a CTE first, then apply a CASE WHEN in the outer query to translate the numeric tier into a business friendly label. Keeping the NTILE logic and the labelling logic in separate layers makes the query easier to maintain, if marketing later wants five tiers instead of four, I only need to touch the NTILE call and update the CASE mapping, the rest of the query structure stays the same.

Q5. The interviewer asks, instead of fixed tiers, can you calculate exactly what percentile each customer falls into?

This pushes beyond NTILE into a more precise statistical ranking, which uses PERCENT_RANK or CUME_DIST depending on exactly how the percentile should be defined.

SELECT
    customer_id,
    total_spend,
    ROUND(
        PERCENT_RANK() OVER (ORDER BY total_spend DESC) * 100,
        1
    ) AS percentile_rank
FROM customer_spend
ORDER BY total_spend DESC;

What to say: PERCENT_RANK calculates the relative position of each row as a value between zero and one, where zero represents the top of the ordering, I multiply by one hundred to express it as a more familiar percentile number. This gives a continuous, precise measure rather than NTILE's discrete buckets, which is more appropriate if the business eventually wants finer grained segmentation or wants to compare a single customer's standing without needing to fit them into one of only four predefined groups.

The Interviewer Says

This is a very common one but I still want to see how you write it cleanly. We have an employee table with department information. Find the employee with the highest salary in each department. If there is a tie, show all of them. Walk me through your approach.

Input Table: employees

How to Handle This in the Interview

Before writing anything, say: This is a per group maximum problem, I want the full row for the highest salary in each department, not just the salary value itself, so a simple GROUP BY MAX will not be enough on its own.

Step 1: Identify why plain GROUP BY does not work

• SELECT dept, MAX(salary) FROM employees GROUP BY dept only gives you the department and the max salary number, it cannot also return emp_id or name in the same query unless those columns are also part of the GROUP BY, which would break the aggregation. Say this limitation out loud, interviewers want to hear you recognise it before reaching for a fix.

Step 2: Choose the window function approach

• Use RANK or DENSE_RANK partitioned by department ordered by salary descending, then filter for rank equal to 1. This naturally returns every column on the row, not just the aggregated value, and also handles ties correctly.

Step 3: Decide RANK versus DENSE_RANK here

• Say: Since I am only filtering for rank 1, RANK and DENSE_RANK behave identically at the top position, there is no difference until you look past rank 1. I will use RANK since it is the more conventional choice when only the top value matters.

Step 4: Mention the GROUP BY plus subquery alternative

• Note that this can also be solved with a correlated subquery comparing each employee's salary to the max salary in their own department, useful to mention as a second approach if asked.

Solution

SQL Server

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_employees
WHERE salary_rank = 1
ORDER BY dept;

PostgreSQL

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_employees
WHERE salary_rank = 1
ORDER BY dept;

MySQL 8+

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_employees
WHERE salary_rank = 1
ORDER BY dept;

The syntax is identical across all three platforms.

What to say: I partition by department so ranking restarts for each department, and order by salary descending so the highest earner gets rank 1. Filtering for rank equal to 1 then picks out the top earner in every department, and since I used RANK, if two employees tie for the highest salary, both correctly get rank 1 and both appear in the output.

Expected Output

Follow-up Questions & Answers

Q1. Can you solve this without window functions, using only GROUP BY and a join?

Yes, this is the pre window function way of solving it and interviewers sometimes ask for it specifically to test whether you understand the older approach.

SELECT
    e.emp_id,
    e.name,
    e.dept,
    e.salary
FROM employees e
JOIN (
    SELECT dept, MAX(salary) AS max_salary
    FROM employees
    GROUP BY dept
) dept_max
    ON e.dept = dept_max.dept
   AND e.salary = dept_max.max_salary
ORDER BY e.dept;

What to say: The subquery first finds the maximum salary per department using a normal GROUP BY, then I join that result back to the original employee table matching on both department and salary, which pulls back the full row including emp_id and name. This naturally handles ties as well, since both Amit and Sara have salary 90000 in Finance, both will match the join condition.

Q2. What if the interviewer wants the second highest salary returned alongside the highest, both in the same query?

This extends naturally from the RANK approach, just widen the filter condition.

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary,
    salary_rank
FROM ranked_employees
WHERE salary_rank IN (1, 2)
ORDER BY dept, salary_rank;

What to say: Here I switch to DENSE_RANK instead of RANK, because once I am including more than just the top position, DENSE_RANK avoids skipping rank numbers when there are ties, which keeps the filter condition rank IN one, two behaving predictably regardless of how many people tie for first place.

Q3. Why did you not just use ROW_NUMBER here instead of RANK?

This is an important distinction to articulate clearly. ROW_NUMBER would assign a unique number to every row even when salaries tie, arbitrarily picking only one employee as rank 1 in Finance even though both Amit and Sara earn exactly 90000. Since the interviewer explicitly said show all of them if there is a tie, ROW_NUMBER would silently produce an incorrect result by dropping one of the tied employees, even though the query would run without any error.

What to say: This is exactly the kind of subtle bug that does not throw an error but produces a wrong business answer, the query executes fine and returns a plausible looking result, which makes it dangerous. I always pick the ranking function based on whether ties should be preserved or arbitrarily broken, and I confirm that requirement with the interviewer or stakeholder rather than assuming.

Q4. How would you find the department with the highest paid employee overall, just one single department, not per department?

A natural extension once you already have departments ranked, now you want only the single best across the entire company.

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS overall_rank
    FROM employees
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_employees
WHERE overall_rank = 1;

What to say: The only change here is removing PARTITION BY entirely, so the ranking runs across the whole table rather than resetting per department. This single change is a good way to show the interviewer I understand exactly what PARTITION BY controls rather than having memorised the per department version as a fixed template.

Q5. What if salary could be NULL for some employees, how does that affect this query?

A genuinely important edge case for production data. In most databases including SQL Server, PostgreSQL, and MySQL, NULL values sort last by default when ordering descending, so an employee with a NULL salary would never accidentally be ranked as the top earner. However, it is worth explicitly deciding what should happen to that employee in the output.

WITH ranked_employees AS (
    SELECT
        emp_id,
        name,
        dept,
        salary,
        RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE salary IS NOT NULL
)
SELECT
    emp_id,
    name,
    dept,
    salary
FROM ranked_employees
WHERE salary_rank = 1
ORDER BY dept;

What to say: I would filter out NULL salaries before ranking entirely, rather than relying on default NULL sort ordering, since that default behaviour can vary across database platforms and I do not want correctness depending on an implicit assumption. I would also flag to the interviewer that employees with missing salary data might need a separate data quality conversation rather than just being silently excluded from this report.