Write SQL to quantify outage revenue loss
Company: Capital One
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
A database outage blocked premium membership registrations from 2025-01-01 to 2025-03-31 (inclusive). Members intend to start on intended_start_date, are billed monthly in arrears at $10/month only after their registration is recorded (registered_date). Some users registered after the outage; some are still missing. For each affected user, the lost revenue equals $10 for each calendar month within the outage window during which the user was not yet registered by that month’s end. Write a single PostgreSQL query that returns: (1) total dollars lost, and (2) a breakdown of users by months_lost ∈ {1,2,3} with counts. Rules: use the earliest registered_date per user; count months precisely using calendar boundaries; include users with NULL registered_date; ignore users whose intended_start_date is after 2025-03-31; deduplicate inputs. Use the following schema and sample ASCII tables:
Tables:
- premium_intended_enrollments(user_id TEXT, intended_start_date DATE)
- premium_actual_registrations(user_id TEXT, registered_date DATE) -- earliest per user after dedup
- outage_window(start_date DATE, end_date DATE) -- here one row: (2025-01-01, 2025-03-31)
Sample data:
premium_intended_enrollments
+---------+---------------------+
| user_id | intended_start_date |
+---------+---------------------+
| U2 | 2025-01-05 |
| U3 | 2025-01-20 |
| U4 | 2025-02-03 |
| U5 | 2025-03-25 |
| U6 | 2025-03-30 |
| U8 | 2025-01-10 |
| U9 | 2025-02-20 |
| U10 | 2025-03-15 |
+---------+---------------------+
premium_actual_registrations
+---------+-----------------+
| user_id | registered_date |
+---------+-----------------+
| U2 | 2025-04-05 |
| U3 | 2025-03-10 |
| U4 | 2025-04-02 |
| U5 | 2025-04-02 |
| U6 | 2025-06-01 |
| U8 | NULL |
| U9 | NULL |
| U10 | NULL |
+---------+-----------------+
outage_window
+------------+------------+
| start_date | end_date |
+------------+------------+
| 2025-01-01 | 2025-03-31 |
+------------+------------+
Output requirements:
- Return columns: months_lost, user_count, dollars_lost (user_count×months_lost×10) and a final rollup row with totals; and separately a single scalar total dollars lost.
- Treat a month as lost if registered_date IS NULL or registered_date > last_day_of_month; only months within [start_date, end_date] count; if intended_start_date begins mid‑window (e.g., 2025‑03‑25), only count months from that month onward within the window.
- Use generate_series to enumerate calendar months; ensure it works on production‑scale tables (hint: pre‑aggregate earliest registration per user, join to a month spine, and filter).
Overview: This question evaluates proficiency in SQL-based data manipulation, specifically date arithmetic, calendar-bound aggregations, deduplication, NULL handling, and calculating business metrics for revenue impact.
Read the full Capital One Data Scientist interview experience this question came from
A database outage blocked premium membership registrations from 2025-01-01 to 2025-03-31 (inclusive). Members intend to start on intended_start_date, are billed monthly in arrears at $10/month only after their registration is recorded (registered_date). Some users registered after the outage; some are still missing.
For each affected user, the lost revenue equals $10 for each calendar month within the outage window during which the user was not yet registered by that month’s end.
Write a single PostgreSQL query that returns a breakdown table with:
- months_lost: the number of months in {1,2,3} the user missed billing during the outage window,
- user_count: how many users had that months_lost value,
- dollars_lost: user_count × months_lost × 10,
- plus a final rollup row (months_lost = NULL) whose dollars_lost is the total dollars lost across all users.
Business rules:
- Use the earliest registered_date per user from premium_actual_registrations.
- A month in the outage window is lost for a user if registered_date IS NULL or registered_date > last_day_of_month.
- Only months within [2025-01-01, 2025-03-31] count.
- If intended_start_date begins mid-window (e.g., 2025-03-25), only count months from that calendar month onward within the window.
- Include users with NULL registered_date.
- Ignore users whose intended_start_date is after 2025-03-31.
- Deduplicate inputs (multiple enrollment/registration rows per user may exist in production).
Technical requirements:
- Use generate_series to enumerate calendar months in the outage window.
- Pre-aggregate earliest registration per user before joining to the month spine.
- Ensure the query would scale to production-sized tables by avoiding per-row generate_series calls.
Return columns: months_lost, user_count, dollars_lost (with a final rollup row where months_lost is NULL and dollars_lost is the total).
Tables
premium_intended_enrollments(user_id TEXT, intended_start_date DATE)
premium_actual_registrations(user_id TEXT, registered_date DATE)
outage_window(start_date DATE, end_date DATE)
Hints
- Build a month spine for the outage window using generate_series on the outage_window row.
- First reduce premium_actual_registrations to the earliest registered_date per user, then join users to the month spine and count months where the user is not yet registered by month_end.