Advanced SQL Interview Practice Guide

5 Scenario-Based Mastery Problems & Detailed Answer Key

This guide compiles five high-impact, advanced-level SQL problems designed to replicate interview scenarios
at premier tech and fintech companies. These problems test proficiency in window functions, recursive
execution, conditional sequence logic, and gaps-and-islands strategies. Attempt the problems first before
checking the comprehensive Answer Key at the end of the document.

Part 1: The Practice Problems

Problem 1: The Active User "Churn Buffer" Problem

Scenario: A product analytics team tracks user engagement using a user_logins log. A user is designated
as Retained if they log in within 7 days of their immediate previous login. To handle borderline engagement,
the team introduces a "grace period": if a user misses the 7-day window but logs in within 14 days, their status
becomes At Risk. If the gap exceeds 14 days, they are classified as Reactivated. Their absolute first login
must be marked as New.

Task: Write a query providing every login event for the calendar year 2026, alongside the calculated user
status category.

CREATE TABLE user_logins (

login_id INT,
user_id INT,
login_date DATE
);

Problem 2: The Multi-Currency Rolling Balance

Scenario: A ledger database tracks financial transactions in multiple local currencies alongside a
daily_rates exchange table converting amounts to USD. Crucially, exchange rates are only logged when a
rate fluctuation occurs, meaning large date gaps exist in the exchange table.

Task: Calculate a daily running total balance in USD per user for every day they executed a transaction. The
query must match transaction rows with the most recent exchange rate logged for that currency on or prior to
the transaction date.

Advanced SQL Interview Practice Guide 1

 CREATE TABLE transactions (
transaction_id INT,
user_id INT,
currency VARCHAR(3),
amount DECIMAL(10,2),
transaction_date DATE
);

CREATE TABLE daily_rates (

currency VARCHAR(3),
rate_to_usd DECIMAL(10,4),
rate_date DATE
);

Problem 3: Finding "Island" Durations (Gaps & Islands)

Scenario: Infrastructure monitoring records server failures. When a machine drops offline, recovers, and falls
offline again within a t ≤ 30 minute window, DevOps views it as part of the exact same continuous outage
window ("island").

Task: Construct a query returning machine_id , outage_start , and outage_end for all distinct
consolidated outages that lasted longer than 1 hour in overall duration.

CREATE TABLE server_errors (

error_id INT,
machine_id INT,
error_timestamp TIMESTAMP
);

Problem 4: Organizational Tree: Maximum Span of Control

Scenario: Human Resources wants to audit the enterprise reporting hierarchy. Every employee maps back to
a single manager_id , creating an asymmetric tree structure.

Task: For every manager, compute their complete cumulative span of control (including both direct reports
and all down-tree indirect reports). Return employee_id , name , and report_count ordered from largest
organizational span to lowest.

CREATE TABLE employees (

employee_id INT,
name VARCHAR(100),
manager_id INT
);

Advanced SQL Interview Practice Guide 2

Problem 5: Sessionization via Inactivity Thresholds
Scenario: Product management requires clickstream sessionization. A clickstream session aggregates
successive events by a single user where no two sequential user actions are separated by more than 30
minutes of complete inactivity.

Task: Assign a unified session ID string formatted as user_id || '_' || session_number to every click
row, and output the computed total duration (in minutes) of each consolidated session.

CREATE TABLE web_traffic (

click_id INT,
user_id INT,
click_timestamp TIMESTAMP,
page_url VARCHAR(255)
);

Advanced SQL Interview Practice Guide 3

 Part 2: Complete Answer Key & Explanations

Solution 1: The Active User "Churn Buffer" Problem

Strategy: Use the LAG() window function partitioned by user to fetch the previous login timestamp, compute
the delta using date metrics, and evaluate states via a conditional CASE WHEN statement.

WITH login_deltas AS (
SELECT
login_id,
user_id,
login_date,
LAG(login_date) OVER(PARTITION BY user_id ORDER BY login_date) AS prev_login
FROM user_logins
)
SELECT
login_id,
user_id,
login_date,
CASE
WHEN prev_login IS NULL THEN 'New'
WHEN login_date - prev_login ≤ 7 THEN 'Retained'
WHEN login_date - prev_login ≤ 14 THEN 'At Risk'
ELSE 'Reactivated'
END AS user_status
FROM login_deltas
WHERE EXTRACT(YEAR FROM login_date) = 2026;

Solution 2: The Multi-Currency Rolling Balance

Strategy: Avoid expensive, inefficient cross joins by utilizing a two-step window boundary approach. First,
perform a left join matching valid chronological history, use LAST_VALUE() or a conditional forward fill to plug
empty gaps, and then aggregate via an unbounded preceding window frame.

Advanced SQL Interview Practice Guide 4

 WITH currency_mapping AS (
SELECT
t.user_id,
t.transaction_date,
[Link],
[Link],
r.rate_to_usd,
ROW_NUMBER() OVER(
PARTITION BY t.transaction_id
ORDER BY r.rate_date DESC
) as rn
FROM transactions t
LEFT JOIN daily_rates r
ON [Link] = [Link]
AND r.rate_date ≤ t.transaction_date
),
normalized_tx AS (
SELECT
user_id,
transaction_date,
(amount * COALESCE(rate_to_usd, 1.0000)) AS amount_usd
FROM currency_mapping
WHERE rn = 1
)
SELECT
user_id,
transaction_date,
SUM(amount_usd) OVER(
PARTITION BY user_id
ORDER BY transaction_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance_usd
FROM normalized_tx;

Solution 3: Finding "Island" Durations

Strategy: Implement a step-indicator lag comparison. If the difference between the current error timestamp
and the preceding timestamp exceeds 30 minutes, mark it as a new distinct step (1, else 0). Compute a
running cumulative sum of this step flag to assign unique structural island numbers to connected incidents.

Advanced SQL Interview Practice Guide 5

 WITH time_deltas AS (
SELECT
machine_id,
error_timestamp,
LAG(error_timestamp) OVER(
PARTITION BY machine_id
ORDER BY error_timestamp
) AS prev_ts
FROM server_errors
),
island_identifiers AS (
SELECT
machine_id,
error_timestamp,
SUM(CASE
WHEN prev_ts IS NULL THEN 1
WHEN error_timestamp - prev_ts > INTERVAL '30 minutes' THEN 1
ELSE 0
END) OVER(PARTITION BY machine_id ORDER BY error_timestamp) AS island_id
FROM time_deltas
),
island_bounds AS (
SELECT
machine_id,
island_id,
MIN(error_timestamp) AS outage_start,
MAX(error_timestamp) AS outage_end
FROM island_identifiers
GROUP BY machine_id, island_id
)
SELECT
machine_id,
outage_start,
outage_end
FROM island_bounds
WHERE outage_end - outage_start > INTERVAL '1 hour';

Solution 4: Organizational Tree: Maximum Span of Control

Strategy: A Recursive CTE maps down the tree. By anchoring each individual manager and walking down
down-stream branches recursively, we preserve the trace pathways. Then, grouping globally by anchor
records aggregates the entire organization down-tree.

Advanced SQL Interview Practice Guide 6

 WITH RECURSIVE org_hierarchy AS (
-- Anchor member: Target every employee as a potential manager root
SELECT
employee_id AS manager_anchor_id,
employee_id AS report_id
FROM employees

UNION ALL

-- Recursive step: Find reports of the current children

SELECT
oh.manager_anchor_id,
e.employee_id
FROM org_hierarchy oh
JOIN employees e ON oh.report_id = e.manager_id
)
SELECT
e.employee_id,
[Link],
COUNT(DISTINCT oh.report_id) - 1 AS total_span_of_control
FROM org_hierarchy oh
JOIN employees e ON oh.manager_anchor_id = e.employee_id
GROUP BY e.employee_id, [Link]
ORDER BY total_span_of_control DESC;

Solution 5: Sessionization via Inactivity Thresholds

Strategy: Evaluate user click deltas against a 30-minute threshold. Generate distinct increments whenever
the window boundaries break, formulate the multi-part session ID, and then group to yield precise global
session metrics.

Advanced SQL Interview Practice Guide 7

 WITH raw_deltas AS (
SELECT
click_id,
user_id,
click_timestamp,
LAG(click_timestamp) OVER(
PARTITION BY user_id
ORDER BY click_timestamp
) AS last_click
FROM web_traffic
),
session_markers AS (
SELECT
click_id,
user_id,
click_timestamp,
SUM(CASE
WHEN last_click IS NULL THEN 1
WHEN click_timestamp - last_click > INTERVAL '30 minutes' THEN 1
ELSE 0
END) OVER(PARTITION BY user_id ORDER BY click_timestamp) AS session_num
FROM raw_deltas
),
session_aggregates AS (
SELECT
user_id,
session_num,
(user_id || '_' || session_num) AS session_id,
MIN(click_timestamp) AS session_start,
MAX(click_timestamp) AS session_end
FROM session_markers
GROUP BY user_id, session_num
)
SELECT
session_id,
user_id,
EXTRACT(EPOCH FROM (session_end - session_start))/60 AS session_duration_minutes
FROM session_aggregates;

Advanced SQL Interview Practice Guide 8