Level up your SQL skills with advanced patterns used in real-world data pipelines and technical interviews from window functions and time-series analysis to deduplication, recursive queries, pivoting, and transaction management.
This chapter focuses on solving problems that require more than basic filtering and aggregation. Each topic includes a practical example, an explanation of the logic, and interview tips to help you understand when and why to use it.
Sample dataset used in this chapter
We'll use an employee_sales table to demonstrate ranking and time-series analysis.
| employee_id | employee_name | department | sale_date | sales |
|---|---|---|---|---|
| 1 | Alice | Sales | 2026-01-01 | 100 |
| 1 | Alice | Sales | 2026-01-02 | 150 |
| 1 | Alice | Sales | 2026-01-03 | 150 |
| 2 | Bob | Sales | 2026-01-01 | 200 |
| 2 | Bob | Sales | 2026-01-03 | 100 |
| 3 | Carol | Marketing | 2026-01-01 | 300 |
| 3 | Carol | Marketing | 2026-01-02 | 200 |
Window functions let us calculate values across related rows while keeping the original row-level detail.
1. Window Functions: ROW_NUMBER, RANK, DENSE_RANK
A window function performs a calculation across a set of related rows without collapsing them into a single row per group, unlike a typical GROUP BY.
1.1 ROW_NUMBER()
ROW_NUMBER() assigns a unique sequential number to each row within a partition.
SELECT
employee_id,
sale_date,
sales,
ROW_NUMBER() OVER (
PARTITION BY employee_id
ORDER BY sale_date
) AS row_num
FROM employee_sales;The numbering restarts for each employee and follows the sale date.
1.2 RANK() and DENSE_RANK()
Both functions rank rows according to an ordering expression. When values tie, they differ in how they assign subsequent ranks.
SELECT
employee_name,
sales,
ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num,
RANK() OVER (ORDER BY sales DESC) AS sales_rank,
DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_sales_rank
FROM employee_sales;| sales | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 300 | 1 | 1 | 1 |
| 200 | 2 | 2 | 2 |
| 200 | 3 | 2 | 2 |
| 150 | 4 | 4 | 3 |
This table illustrates the ranking behavior on sample sales values. The order of rows tied on sales is not guaranteed unless you add a tie-breaker.
| Function | Behavior when values tie | Example ranks for 100, 100, 90 |
|---|---|---|
ROW_NUMBER() | Always gives a unique sequence | 1, 2, 3 (tie order may vary) |
RANK() | Leaves gaps after ties | 1, 1, 3 |
DENSE_RANK() | No gaps after ties | 1, 1, 2 |
ROW_NUMBER() when you need exactly one row per position, such as selecting the latest record. Use RANK() when ties should share a rank and gaps are acceptable. Use DENSE_RANK() when ties share a rank without gaps.2. LAG and LEAD
LAG() accesses a value from a previous row, while LEAD() accesses a value from a following row within an ordered window. They are useful for comparing current and prior periods, detecting changes, and calculating growth.
SELECT
employee_id,
sale_date,
sales,
LAG(sales) OVER (
PARTITION BY employee_id
ORDER BY sale_date
) AS previous_sales,
LEAD(sales) OVER (
PARTITION BY employee_id
ORDER BY sale_date
) AS next_sales
FROM employee_sales;For Alice, the first row has no previous sale, so previous_sales is NULL. The next row's sales value appears in next_sales.
Calculate change from the previous row
WITH sales_comparison AS (
SELECT
employee_id,
sale_date,
sales,
LAG(sales) OVER (
PARTITION BY employee_id
ORDER BY sale_date
) AS previous_sales
FROM employee_sales
)
SELECT
employee_id,
sale_date,
sales,
previous_sales,
sales - previous_sales AS sales_change
FROM sales_comparison;LAG and LEAD follow the ordering you specify. If multiple rows have the same ordering value, add a deterministic tie-breaker where needed.3. Running Totals and Moving Averages
3.1 Running total
A running total accumulates values from the beginning of a window up to the current row.
SELECT
employee_id,
sale_date,
sales,
SUM(sales) OVER (
PARTITION BY employee_id
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_sales
FROM employee_sales;PARTITION BY employee_id restarts the total for each employee. The explicit ROWS frame makes the calculation accumulate row by row.
3.2 Moving average
A moving average calculates an average over a sliding window. For a three-row moving average including the current row:
SELECT
employee_id,
sale_date,
sales,
AVG(sales) OVER (
PARTITION BY employee_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3_rows
FROM employee_sales;For each row, this averages the current value and up to the two preceding rows within the employee's partition.
4. Deduplication
Deduplication is a common Data Engineering task when source systems resend records, snapshots overlap, or event streams contain repeated events. The correct business key and record-selection rule must be defined first.
Suppose an events table can contain multiple versions of the same event, and the most recently updated version should be kept.
WITH ranked_events AS (
SELECT
event_id,
customer_id,
event_type,
updated_at,
ROW_NUMBER() OVER (
PARTITION BY event_id
ORDER BY updated_at DESC
) AS rn
FROM events
)
SELECT
event_id,
customer_id,
event_type,
updated_at
FROM ranked_events
WHERE rn = 1;The query ranks records within each event_id and keeps the newest one.
updated_at, the selected row may be nondeterministic. Add a stable tie-breaker, such as an ingestion timestamp or source sequence number. Also decide how NULL timestamps should be handled.5. Top N Per Group
The top-N-per-group pattern returns the highest-ranked rows within each category, such as the top three products per region or top two employees per department.
Example: Find the top two sales records per department.
WITH ranked_sales AS (
SELECT
employee_id,
employee_name,
department,
sales,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY sales DESC, employee_id
) AS rn
FROM employee_sales
)
SELECT
employee_id,
employee_name,
department,
sales
FROM ranked_sales
WHERE rn <= 2;This returns at most two rows per department. Here, ROW_NUMBER() ensures that ties are broken using employee_id.
ROW_NUMBER() for exactly N rows per group (where enough rows exist). Use RANK() or DENSE_RANK() if the requirement is to include tied values, understanding that the number of returned rows can exceed N.6. Gaps and Islands
“Gaps and islands” problems identify missing sequences (gaps) or consecutive sequences of values (islands). They appear in login streaks, active dates, consecutive events, inventory availability, and subscription analysis.
6.1 Find consecutive dates
Suppose a table user_activity has one row per user per activity date. To group consecutive dates into islands, compare each date with a row number sequence.
WITH numbered_dates AS (
SELECT
user_id,
activity_date,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY activity_date
) AS rn
FROM user_activity
),
grouped_dates AS (
SELECT
user_id,
activity_date,
activity_date - (rn * INTERVAL '1 day') AS island_key
FROM numbered_dates
)
SELECT
user_id,
MIN(activity_date) AS island_start,
MAX(activity_date) AS island_end,
COUNT(*) AS consecutive_days
FROM grouped_dates
GROUP BY user_id, island_key
ORDER BY user_id, island_start;This is a PostgreSQL-style interval example. Date arithmetic differs by database; for BigQuery, use a date expression such as DATE_SUB(activity_date, INTERVAL rn DAY) with an appropriate integer type.
The idea is that subtracting an increasing row number from consecutive dates produces the same grouping key for dates in the same island.
6.2 Find gaps
Use LAG() to compare each date with the previous date.
WITH activity_with_previous AS (
SELECT
user_id,
activity_date,
LAG(activity_date) OVER (
PARTITION BY user_id
ORDER BY activity_date
) AS previous_date
FROM user_activity
)
SELECT
user_id,
previous_date,
activity_date
FROM activity_with_previous
WHERE previous_date IS NOT NULL
AND activity_date > previous_date + INTERVAL '1 day';This PostgreSQL-style query finds pairs of activity dates with at least one missing day between them.
7. Recursive CTEs
A recursive CTE repeatedly references its own result to traverse hierarchical or sequential data. Common use cases include employee reporting hierarchies, category trees, bill-of-materials structures, and graph-like relationships.
Imagine an employees table with employee_id, employee_name, and manager_id.
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: start with the top-level manager
SELECT
employee_id,
employee_name,
manager_id,
0 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: find direct reports
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
h.level + 1 AS level
FROM employees e
JOIN employee_hierarchy h
ON e.manager_id = h.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level, employee_id;The anchor query selects the root employee. The recursive query finds employees reporting to the previous level and increments the hierarchy depth.
8. Pivoting and Unpivoting
Pivoting transforms row values into columns. Unpivoting transforms columns into rows. These operations are useful when reshaping data for reporting, dashboards, or downstream models.
8.1 Pivot with conditional aggregation
A portable approach is to use CASE WHEN inside aggregate functions.
Suppose monthly_sales contains department, month_name, and sales.
SELECT
department,
SUM(CASE WHEN month_name = 'Jan' THEN sales ELSE 0 END) AS jan_sales,
SUM(CASE WHEN month_name = 'Feb' THEN sales ELSE 0 END) AS feb_sales,
SUM(CASE WHEN month_name = 'Mar' THEN sales ELSE 0 END) AS mar_sales
FROM monthly_sales
GROUP BY department;This creates one row per department and a separate column for each month.
8.2 Native PIVOT
Some databases provide a native PIVOT operator. Syntax varies. For example, BigQuery supports:
SELECT *
FROM (
SELECT department, month_name, sales
FROM monthly_sales
)
PIVOT (
SUM(sales)
FOR month_name IN ('Jan', 'Feb', 'Mar')
);8.3 UNPIVOT
BigQuery also supports an UNPIVOT operator for converting month columns back into rows.
SELECT department, month_name, sales
FROM quarterly_sales
UNPIVOT (
sales FOR month_name IN (jan_sales AS 'Jan', feb_sales AS 'Feb', mar_sales AS 'Mar')
);Check your SQL engine’s syntax and NULL-handling behavior. Some unpivot operations omit NULL values by default unless you specify an option to preserve them.
9. Stored Procedures and Transactions
9.1 Stored procedures
A stored procedure is a named set of SQL statements stored in the database and executed when called. Procedures can encapsulate repeated transformations, validation steps, and administrative workflows.
For example, the following is a BigQuery scripting example that creates a procedure to refresh a reporting table:
CREATE OR REPLACE PROCEDURE
`my_dataset.refresh_sales_summary`()
BEGIN
CREATE OR REPLACE TABLE `my_dataset.sales_summary` AS
SELECT
department,
SUM(sales) AS total_sales
FROM `my_dataset.employee_sales`
GROUP BY department;
END;Call the procedure using:
CALL `my_dataset.refresh_sales_summary`();This example uses BigQuery-specific syntax. Stored procedure syntax, parameters, error handling, and transaction support differ across database systems.
9.2 Transactions
A transaction groups operations into a unit of work. In databases that support ACID transactions, the key properties are:
- Atomicity: The transaction succeeds as a whole or is rolled back.
- Consistency: Data remains within the database’s defined integrity rules.
- Isolation: Concurrent transactions are controlled according to the isolation level.
- Durability: Committed changes persist according to the system’s guarantees.
A simple transaction example in PostgreSQL:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;If an error occurs before commit and you need to cancel the changes, use ROLLBACK rather than COMMIT.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
-- Cancel the transaction if validation fails
ROLLBACK;Final Interview Practice
Try to solve each problem
Question 1: Ranking
Assign a row number to each employee’s sales ordered by date.
Question 2: LAG
Show each employee’s current sales and previous sales value.
Question 3: Running total
Calculate cumulative sales for each employee.
Question 4: Deduplication
Keep the most recently updated record for each event ID.
Question 5: Top N per group
Return the top two sales records in each department.
Question 6: Gaps and islands
Which SQL function helps compare the current activity date with the previous activity date?
Question 7: Recursive CTE
What are the two parts of a recursive CTE?
Question 8: Pivoting
How can you pivot rows into columns without a native PIVOT operator?
Question 9: Transactions
Which statement confirms a transaction’s changes, and which statement cancels uncommitted changes?
Chapter Summary
By the end of this chapter, you should be able to:
- Choose between
ROW_NUMBER,RANK, andDENSE_RANKfor ranking problems. - Compare records across rows with
LAGandLEAD. - Calculate running totals and moving averages using window frames.
- Deduplicate records deterministically using window functions.
- Solve top-N-per-group and gaps-and-islands problems.
- Traverse hierarchies using recursive CTEs.
- Reshape datasets with pivoting and unpivoting.
- Explain stored procedures, transaction control, and ACID properties.
End of Chapter 3 · Advanced SQL for Data Engineering Interviews
Comments
Post a Comment