Solve window-function SQL without joins
Problem Description
This interview question focuses on advanced SQL data manipulation using window functions, with a strict constraint against using JOINs. You are provided with two schemas and sample data, and asked to solve four distinct sub-problems. Common Table Expressions (CTEs) are permitted.
Schemas
payments
Tracks loan payment information.
loan_id(INT): Unique identifier for a loan.payment_date(DATE): Date the payment was made.payment_amount(DECIMAL(10,2)): Amount of the payment.loan_amount(DECIMAL(10,2)): Total principal amount of the loan.
Sample Data:
| loan_id | payment_date | payment_amount | loan_amount |
|---|---|---|---|
| 1 | 2025-08-01 | 200.00 | 1000.00 |
| 1 | 2025-08-15 | 300.00 | 1000.00 |
| 1 | 2025-09-01 | 500.00 | 1000.00 |
| 2 | 2025-08-02 | 100.00 | 600.00 |
| 2 | 2025-09-01 | 100.00 | 600.00 |
| 2 | 2025-09-05 | 200.00 | 600.00 |
| 2 | 2025-09-12 | 200.00 | 600.00 |
subscription_events
Records subscription lifecycle events for accounts.
account_id(INT): Unique identifier for an account.event_date(DATE): Date the event occurred.event_type(STRING): Type of event, one of 'start', 'upgrade', 'downgrade', 'churn', 'reactivate'.mrr(INT): Monthly Recurring Revenue associated with the event.
Sample Data:
| account_id | event_date | event_type | mrr |
|---|---|---|---|
| 101 | 2025-07-01 | start | 50 |
| 101 | 2025-08-10 | upgrade | 100 |
| 101 | 2025-08-25 | churn | 0 |
| 101 | 2025-08-28 | reactivate | 80 |
| 102 | 2025-06-15 | start | 30 |
| 102 | 2025-08-31 | churn | 0 |
| 103 | 2025-08-20 | start | 20 |
Tasks
You must use only window functions (no JOINs). CTEs are allowed.
Task (a): Loan Payment Threshold Date
For each loan_id, identify the earliest payment_date at which the running sum of payment_amount first reaches or exceeds 80% of that loan's principal amount. The principal amount is defined as 0.8 * MAX(loan_amount) OVER (PARTITION BY loan_id).
Output: loan_id, threshold_date.
Tie-breaking: In case of ties (multiple payments on the same date causing the threshold to be met), select the earliest payment_date. If still tied, select the payment corresponding to the smallest ROW_NUMBER() ordered by payment_date (ascending) and then payment_amount (descending) within each loan_id.
Task (b): Detect Payment Gaps
For each loan_id, detect any gaps longer than 1 calendar month between consecutive payments.
Output: loan_id, gap_start_date, gap_end_date, gap_months (integer number of months between dates).
Hint: Consider using LAG(payment_date) OVER (PARTITION BY loan_id ORDER BY payment_date).
Task (c): Cumulative Payments and Ranking
For the specific date '2025-09-01', compute each loan_id's cumulative paid-through amount up to and including that date. Then, within that date, rank loans by their cumulative amount using RANK(), DENSE_RANK(), and ROW_NUMBER() to illustrate the differences between these ranking functions when ties occur.
Output: loan_id, cum_paid_through_2025_09_01, rank_r, rank_dense, rownum.
Task (d): Current Subscription Status
As of '2025-09-01' (today), compute each account's current MRR, lifecycle_status, and the date of the last status-changing event.
lifecycle_statuscan be 'active', 'churned', or 'reactivated_last_7_days'.- An account is considered 'reactivated_last_7_days' if it has a 'reactivate' event within the date range
[2025-08-25, 2025-09-01]. - You must use only window functions to forward-fill the latest event data (MRR and event type) up to and including '2025-09-01' to derive the current status.
Output: account_id, current_mrr, lifecycle_status, last_status_event_date.
AI interview prep
Core dilemma
The central engineering conflict is performing complex relational operations and state management (like self-joins for previous/next rows, or propagating state over time) using *only* window functions, which are typically designed for operations within a single partition. This forces creative application of window frame clauses, nested CTEs, and careful ordering to simulate join-like behavior and stateful logic.
3 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Solve window-function SQL without joins
Problem Description
This interview question focuses on advanced SQL data manipulation using window functions, with a strict constraint against using JOINs. You are provided with two schemas and sample data, and asked to solve four distinct sub-problems. Common Table Expressions (CTEs) are permitted.
Schemas
payments
Tracks loan payment information.
loan_id(INT): Unique identifier for a loan.payment_date(DATE): Date the payment was made.payment_amount(DECIMAL(10,2)): Amount of the payment.loan_amount(DECIMAL(10,2)): Total principal amount of the loan.
Sample Data:
| loan_id | payment_date | payment_amount | loan_amount |
|---|---|---|---|
| 1 | 2025-08-01 | 200.00 | 1000.00 |
| 1 | 2025-08-15 | 300.00 | 1000.00 |
| 1 | 2025-09-01 | 500.00 | 1000.00 |
| 2 | 2025-08-02 | 100.00 | 600.00 |
| 2 | 2025-09-01 | 100.00 | 600.00 |
| 2 | 2025-09-05 | 200.00 | 600.00 |
| 2 | 2025-09-12 | 200.00 | 600.00 |
subscription_events
Records subscription lifecycle events for accounts.
account_id(INT): Unique identifier for an account.event_date(DATE): Date the event occurred.event_type(STRING): Type of event, one of 'start', 'upgrade', 'downgrade', 'churn', 'reactivate'.mrr(INT): Monthly Recurring Revenue associated with the event.
Sample Data:
| account_id | event_date | event_type | mrr |
|---|---|---|---|
| 101 | 2025-07-01 | start | 50 |
| 101 | 2025-08-10 | upgrade | 100 |
| 101 | 2025-08-25 | churn | 0 |
| 101 | 2025-08-28 | reactivate | 80 |
| 102 | 2025-06-15 | start | 30 |
| 102 | 2025-08-31 | churn | 0 |
| 103 | 2025-08-20 | start | 20 |
Tasks
You must use only window functions (no JOINs). CTEs are allowed.
Task (a): Loan Payment Threshold Date
For each loan_id, identify the earliest payment_date at which the running sum of payment_amount first reaches or exceeds 80% of that loan's principal amount. The principal amount is defined as 0.8 * MAX(loan_amount) OVER (PARTITION BY loan_id).
Output: loan_id, threshold_date.
Tie-breaking: In case of ties (multiple payments on the same date causing the threshold to be met), select the earliest payment_date. If still tied, select the payment corresponding to the smallest ROW_NUMBER() ordered by payment_date (ascending) and then payment_amount (descending) within each loan_id.
Task (b): Detect Payment Gaps
For each loan_id, detect any gaps longer than 1 calendar month between consecutive payments.
Output: loan_id, gap_start_date, gap_end_date, gap_months (integer number of months between dates).
Hint: Consider using LAG(payment_date) OVER (PARTITION BY loan_id ORDER BY payment_date).
Task (c): Cumulative Payments and Ranking
For the specific date '2025-09-01', compute each loan_id's cumulative paid-through amount up to and including that date. Then, within that date, rank loans by their cumulative amount using RANK(), DENSE_RANK(), and ROW_NUMBER() to illustrate the differences between these ranking functions when ties occur.
Output: loan_id, cum_paid_through_2025_09_01, rank_r, rank_dense, rownum.
Task (d): Current Subscription Status
As of '2025-09-01' (today), compute each account's current MRR, lifecycle_status, and the date of the last status-changing event.
lifecycle_statuscan be 'active', 'churned', or 'reactivated_last_7_days'.- An account is considered 'reactivated_last_7_days' if it has a 'reactivate' event within the date range
[2025-08-25, 2025-09-01]. - You must use only window functions to forward-fill the latest event data (MRR and event type) up to and including '2025-09-01' to derive the current status.
Output: account_id, current_mrr, lifecycle_status, last_status_event_date.