The Data Engineer’s Definitive SQL Interview Playbook

My approach is a three-step process: identify, inspect, and then execute a safe cleaning strategy.

Step 1: How do you identify which emails are duplicated?

Use `GROUP BY` and `HAVING` to find keys that appear more than once.

Input `customers` table:

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

Output: `john.s@email.com` with 2 occurrences.

Step 2: How do you inspect the full details of these duplicate rows?

Use a subquery or CTE to filter the main table for the problematic emails.

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

Output: Rows with `id` 1 and 3.

Step 3: How do you delete the duplicates but keep the earliest signed-up record?

The safest and most flexible method is using a CTE with the `ROW_NUMBER()` window function.

WITH RankedCustomers AS (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY signed_up_date ASC) as rn
    FROM customers
)
DELETE FROM customers WHERE id IN (SELECT id FROM RankedCustomers WHERE rn > 1);

Window functions perform calculations across a set of related rows. The ranking functions (`ROW_NUMBER`, `RANK`, `DENSE_RANK`) are a core part of this family.

Problem 1: Find the top 2 highest-paid employees in each department.

Input `employees` table:

`DENSE_RANK()` is the right choice to include all employees who tie for a position.

WITH RankedSalaries AS (
    SELECT name, department, salary, DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) as rnk
    FROM employees
)
SELECT name, department, salary FROM RankedSalaries WHERE rnk <= 2;

Output: Alice, Bob, and Charlie from Engineering, and David from Sales.

Problem 2: Calculate a monthly running total of revenue.

Input `monthly_revenue` table:

SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) as running_total_revenue FROM monthly_revenue;

Output:

SUM(revenue) OVER (PARTITION BY product_line ORDER BY month)

`JOIN`s combine columns from different tables horizontally, while `UNION`/`UNION ALL` stack rows from different tables vertically.

`INNER JOIN` vs. `LEFT JOIN`

An `INNER JOIN` returns only the intersection of two tables. A `LEFT JOIN` returns all records from the left table, and matched records from the right.

Use Case: Use `INNER JOIN` when you only care about records that have a valid relationship in both tables (e.g., `orders` and `customers`). Use `LEFT JOIN` when you need all records from one table, regardless of whether they have a match in the other (e.g., finding all customers, even those who haven't placed an order yet).

`UNION` vs. `UNION ALL`

`UNION ALL` simply appends datasets. `UNION` does the same, but then adds a costly de-duplication step.

CTEs vs. Subqueries

A Common Table Expression (`WITH` clause) creates a temporary, named result set. It is superior to nested subqueries because it makes complex queries highly readable and modular. You can also reference a CTE multiple times in the same query.

Pivoting Data

Pivoting transforms rows into columns, often for reporting. The standard method is conditional aggregation.

Input `quarterly_sales` table:

SELECT
    product,
    SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS q1_sales,
    SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS q2_sales
FROM quarterly_sales
GROUP BY product;

Pivoted Output:

This requires a **Recursive CTE**, which is designed specifically for traversing hierarchical or graph-like data structures.

Input `employees` table:

Goal: Find the reporting chain for the 'Engineer' (id = 4).

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: Start with the employee in question
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE id = 4

    UNION ALL

    -- Recursive Member: Join back to the employees table to find the manager
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.id = eh.manager_id
)
SELECT * FROM EmployeeHierarchy;

Output:

These commands all remove data, but they operate at different levels and have huge implications for performance and recoverability.

• `DELETE`: A DML (Data Manipulation Language) command. It removes rows one by one and logs each deletion.
  • When to use: When you need to remove a subset of rows based on a `WHERE` clause.
  • Why: It's slow for large tables but safe, as it's part of a transaction and can be rolled back. It also fires triggers.
• `TRUNCATE`: A DDL (Data Definition Language) command. It deallocates the data pages of the table, instantly removing all rows.
  • When to use: To quickly delete all rows from a large staging table.
  • Why: It's extremely fast and uses minimal system resources. However, it's typically not logged in the same way, so it cannot be easily rolled back and does not fire `DELETE` triggers.
• `DROP`: A DDL command. It removes the entire table structure and all its data permanently.
  • When to use: When you are completely finished with a table and want to remove it from the database.
  • Why: It's the most destructive command. There's no coming back from a `DROP TABLE` without a backup.

1. Analyze the Execution Plan: My first action is to use `EXPLAIN` to ask the database for its execution plan. This reveals if it's using a "Full Table Scan" on a large table, which is a major red flag.
2. Verify Indexes: The execution plan will guide me, but I will confirm that columns in `WHERE` clauses, `JOIN` conditions, and `ORDER BY` clauses are properly indexed. This is the most common cause of poor performance.
3. Check for Non-Sargable Queries: I'll look for functions applied to columns in the `WHERE` clause, like `WHERE YEAR(order_date) = 2023`. This prevents the database from using an index. The fix is to rewrite it as `WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'`.