Advanced SQL Patterns
Experienced SQL developers recognize recurring problem shapes and apply known patterns.
Concept
Experienced SQL developers recognize recurring problem shapes and apply known patterns. This module collects the most important ones, several of which build on window functions and recursive CTEs.
Why It Matters
These patterns appear in senior interviews and in real analytics/engineering work: streaks, sessions, hierarchies, history tracking, and reshaping data.
Syntax
The main building blocks: ROW_NUMBER, LAG/LEAD, running SUM over flags, recursive CTEs, conditional aggregation, and effective-date ranges.
Example
41.1 Top-N per group Alternative for "top 1 per group" at scale with an index on (group, sort): LATERAL / CROSS APPLY with LIMIT 1.
41.2 Gaps and islands Islands are runs of consecutive values; gaps are the missing values between them.
Technique: for consecutive dates, login_date - ROW_NUMBER() is constant within an island.
login_date − rn days (group key) Gaps using LEAD:
Engineering uses: continuous sensor-over-threshold periods, ECU uptime windows, consecutive days with a DTC present, missing sequence numbers in telemetry.
41.3 Consecutive records "Find test cases that failed 3 times in a row":
For "N in a row" with a variable N, use the islands technique with a flag: ROW_NUMBER() OVER (PARTITION BY test_case ORDER BY executed_at) - ROW_NUMBER() OVER (PARTITION BY test_case, result ORDER BY executed_at) groups consecutive equal results; then HAVING COUNT(*) >= N.
Group events into sessions separated by more than 30 minutes of inactivity.
The "flag + running sum" technique generalizes to any "start a new group when condition X happens" problem (e.g., new drive cycle when ignition turns on).
41.5 Hierarchical data and recursive queries Models for hierarchies:
parent_id column Simple, easy updates Needs recursive CTE for subtrees path = '/1/4/5/' Fast subtree via LIKE '/1/4/%' Path maintenance on moves lft, rgt numbers Fast subtree reads Expensive inserts/moves Table of (ancestor, descendant, depth) Fast any-depth queries, flexible Extra table, more writes
PostgreSQL ltree, SQL Server hierarchyid Bill of materials (BOM) explosion — total quantity of each part needed to build one assembly:
(SCREW: 4 directly + 2 via HOUSING.) 41.6 Pivoting and unpivoting
See Module 37.8–37.9. Pattern summary: pivot = GROUP BY row_key + SUM(CASE WHEN col_key = X THEN value END) per output column; unpivot = one SELECT per column combined with UNION ALL, or CROSS JOIN LATERAL (VALUES ...).
41.7 Conditional aggregation One pass, many metrics:
See Module 39: ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY preference) = 1.
41.9 Slowly Changing Dimensions (SCD) concept In data warehouses, dimension attributes (customer city, vehicle owner, product category) change over time. SCD types describe how to handle changes:
Never change (original value retained) Overwrite the old value
Insert a new row version with validity dates and a current flag
Keep previous value in an extra column (previous_city) Limited (one prior value) Current table + separate history table Hybrid of 1 + 2 + 3 Full + current value on every row SCD Type 2 table:
Facts (orders) store the surrogate customer_sk valid at transaction time, so historical reports show the city at the time of the order.
41.10 Change tracking concepts Ways to know what changed and when:
created_at, updated_at, updated_by Simple incremental loads: WHERE updated_at >:last_run Audit/history tables via triggers Old/new values per change Module 27 salary audit
System-versioned temporal tables (SQL:2011) Database keeps row history automatically; query FOR SYSTEM_TIME AS OF
SQL Server, MariaDB, Db2, Oracle Flashback Change Data Capture (CDC)
Read the transaction log and stream row changes SQL Server CDC, PostgreSQL logical decoding, MySQL binlog, Debezium, Oracle GoldenGate Change Tracking (lightweight) Which rows changed, not full values SQL Server Change Tracking deleted_at instead of DELETE Allows restoring and syncing deletions version integer or rowversion Optimistic concurrency and sync SQL Server temporal table:
Incremental extract pattern for ETL:
WITH ranked AS (
SELECT p.category, p.product_name, p.price,
DENSE_RANK() OVER (PARTITION BY p.category ORDER BY p.price DESC) AS rnk
FROM products p
)
SELECT category, product_name, price
FROM ranked
WHERE rnk <= 2
ORDER BY category, rnk;CREATE TABLE user_logins (user_id INT, login_date DATE, PRIMARY KEY (user_id, login_date));
INSERT INTO user_logins VALUES
(1, DATE '2026-03-01'), (1, DATE '2026-03-02'), (1, DATE '2026-03-03'),
(1, DATE '2026-03-05'), (1, DATE '2026-03-06'), (1, DATE '2026-03-09');-- PostgreSQL (DATE - INTEGER); MySQL: DATE_SUB(login_date, INTERVAL rn DAY); SQL Server: DATEADD(day, -rn, login_date)
WITH numbered AS (
SELECT user_id, login_date,
login_date - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS INT) AS grp
FROM user_logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY streak_start;SELECT login_date + 1 AS gap_start, next_date - 1 AS gap_end
FROM (SELECT login_date, LEAD(login_date) OVER (ORDER BY login_date) AS next_date
FROM user_logins WHERE user_id = 1) t
WHERE next_date - login_date > 1;WITH seq AS (
SELECT test_case, executed_at, result,
LAG(result, 1) OVER (PARTITION BY test_case ORDER BY executed_at) AS prev1,
LAG(result, 2) OVER (PARTITION BY test_case ORDER BY executed_at) AS prev2
FROM test_result
)
SELECT DISTINCT test_case
FROM seq
WHERE result = 'FAIL' AND prev1 = 'FAIL' AND prev2 = 'FAIL';CREATE TABLE click_events (user_id INT, event_time TIMESTAMP);
INSERT INTO click_events VALUES
(1, TIMESTAMP '2026-03-10 10:00'), (1, TIMESTAMP '2026-03-10 10:10'),
(1, TIMESTAMP '2026-03-10 10:50'), (1, TIMESTAMP '2026-03-10 11:05'),
(1, TIMESTAMP '2026-03-10 13:00');
WITH flagged AS (
SELECT user_id, event_time,
CASE WHEN event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
<= INTERVAL '30 minutes'
THEN 0 ELSE 1 END AS new_session -- first event: LAG is NULL → ELSE → 1
FROM click_events
),
sessions AS (
SELECT user_id, event_time,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) AS session_no
FROM flagged
)
SELECT user_id, session_no, MIN(event_time) AS session_start, MAX(event_time) AS session_end, COUNT(*) AS events
FROM sessions
GROUP BY user_id, session_no
ORDER BY session_no;CREATE TABLE bom (parent_part VARCHAR(20), child_part VARCHAR(20), qty INT);
INSERT INTO bom VALUES
('ECU-ASSY', 'PCB', 1), ('ECU-ASSY', 'HOUSING', 1), ('ECU-ASSY', 'SCREW', 4),
('PCB', 'MCU', 1), ('PCB', 'CAN-XCVR', 2), ('PCB', 'CAPACITOR', 20),
('HOUSING', 'SCREW', 2);
WITH RECURSIVE explode AS (
SELECT child_part, qty AS total_qty, 1 AS lvl
FROM bom WHERE parent_part = 'ECU-ASSY'
UNION ALL
SELECT b.child_part, e.total_qty * b.qty, e.lvl + 1
FROM bom b JOIN explode e ON b.parent_part = e.child_part
)
SELECT child_part, SUM(total_qty) AS qty_needed
FROM explode
GROUP BY child_part
ORDER BY child_part;SELECT COUNT(*) AS total_orders,
SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) AS delivered,
SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled,
ROUND(100.0 * SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) / COUNT(*), 1) AS cancel_rate_pct,
SUM(CASE WHEN total_amount >= 50000 THEN total_amount ELSE 0 END) AS big_order_revenue
FROM orders;CREATE TABLE dim_customer (
customer_sk BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- surrogate key per version
customer_id INT NOT NULL, -- business key
name VARCHAR(100) NOT NULL,
city VARCHAR(50) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL DEFAULT DATE '9999-12-31',
is_current BOOLEAN NOT NULL DEFAULT TRUE
);
-- Customer 1 moves from Mumbai to Pune on 2026-04-01
BEGIN;
UPDATE dim_customer
SET valid_to = DATE '2026-03-31', is_current = FALSE
WHERE customer_id = 1 AND is_current = TRUE;
INSERT INTO dim_customer (customer_id, name, city, valid_from)
VALUES (1, 'Ananya Gupta', 'Pune', DATE '2026-04-01');
COMMIT;
-- Point-in-time lookup: where did customer 1 live on 2026-02-15?
SELECT city FROM dim_customer
WHERE customer_id = 1
AND DATE '2026-02-15' BETWEEN valid_from AND valid_to; -- MumbaiCREATE TABLE dbo.price_list (
product_id INT PRIMARY KEY,
price DECIMAL(10,2) NOT NULL,
valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
valid_to DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.price_list_history));
SELECT * FROM dbo.price_list FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00';Expected Result
The islands query returns three login streaks (3, 2, and 1 days); the sessionization query returns three sessions; the BOM explosion returns 6 screws.
Common Mistakes
Using LAG without PARTITION BY across different entities.
Non-unique ORDER BY in window functions used for sequencing.
Recursive queries without cycle protection.
SCD2 updates without a transaction (two current rows or none).
Using BETWEEN with inclusive valid_to and adjacent periods that share a boundary date (choose inclusive or half-open consistently).
Incremental loads that miss rows updated in the same second as the watermark (use > / <= carefully, or CDC).
Best Practices
Learn to recognize the pattern first, then write the SQL.
Keep a personal library of tested pattern templates.
Validate pattern output on small hand-checked datasets (like the ones in this course).
Enforce SCD invariants (one current row per business key) with a partial unique index: CREATE UNIQUE INDEX ... ON dim_customer (customer_id) WHERE is_current;.
Interview Relevance
Senior-level differentiators: gaps and islands, sessionization, consecutive events, BOM/hierarchy recursion, SCD Type 2 design, CDC concepts.
Practice Questions
Find the longest streak of consecutive days each vehicle reported at least one DTC.
Sessionize ECU log events with a 5-minute inactivity threshold.
Find employees who received salary increases in three consecutive reviews.
Implement an SCD Type 2 update for product price changes.
Build a closure table for the employee hierarchy and query all subordinates of Asha.
Quiz Checkpoint 7 (Modules 35 – 41)
Q1. (Predict the output) SELECT COALESCE(NULL, NULL, 'x', 'y'); Answer: 'x'.
Q2. (True/False) GROUP BY puts all NULL values of the grouping column into a single group.
Answer: True. Q3. (Multiple choice) Which technique groups consecutive dates into islands? A) date + RANK() B) date - ROW_NUMBER() C) NTILE D) CROSS JOIN Answer: B. Q4. (Find the error) (order_date is a TIMESTAMP.) What is wrong?
Answer: Orders on January 31 after midnight are excluded. Use order_date >= '2026-01-01' AND order_date < '2026-02-01'.
Q5. (Write the query) Keep only the latest row per vin in vehicle_snapshots(vin, captured_at, odometer_km).
Q6. (Multiple choice) An SCD Type 2 dimension handles a changed attribute by: A) Overwriting B) Adding a new row version with validity dates C) Adding a "previous value" column D) Ignoring the change
Q7. (Predict the output) SELECT AVG(x) FROM (VALUES (10), (NULL), (20)) AS t(x); Answer: 15. NULL is ignored.
Q8. (True/False) A native UNPIVOT in SQL Server keeps rows whose value is NULL.
Answer: False. It removes them.
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31 00:00:00';WITH r AS (
SELECT vin, captured_at, ROW_NUMBER() OVER (PARTITION BY vin ORDER BY captured_at DESC) AS rn
FROM vehicle_snapshots
)
DELETE FROM vehicle_snapshots v
WHERE EXISTS (SELECT 1 FROM r WHERE r.vin = v.vin AND r.captured_at = v.captured_at AND r.rn > 1);
-- (Assumes (vin, captured_at) is unique; PostgreSQL syntax.)