Write SQL to quantify outage revenue loss

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).

Quantify Revenue Lost During Membership Registration Outage

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).

Table premium_intended_enrollments

column	type	constraints
user_id	TEXT	NOT NULL
intended_start_date	DATE	NOT NULL

Sample data:

intended_start_date	user_id
2025-01-05	U2
2025-01-20	U3
2025-02-03	U4
2025-03-25	U5
2025-03-30	U6
2025-01-10	U8
2025-02-20	U9
2025-03-15	U10

Table premium_actual_registrations

column	type	constraints
user_id	TEXT	NOT NULL
registered_date	DATE

Sample data:

registered_date	user_id
2025-04-05	U2
2025-03-10	U3
2025-04-02	U4
2025-04-02	U5
2025-06-01	U6
NULL	U8
NULL	U9
NULL	U10

Table outage_window

column	type	constraints
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

Sample data:

end_date	start_date
2025-03-31	2025-01-01

Expected output

For the sample data, the query should compute months_lost per user over the outage window (Jan–Mar 2025), then aggregate to a distribution with a rollup row. Users U5, U6, and U10 each lose 1 month; U3, U4, and U9 each lose 2 months; U2 and U8 each lose 3 months. Total lost months = 15, so total dollars lost = 15 × $10 = $150. The rollup row (months_lost = NULL) carries these totals.

dollars_lost	months_lost	user_count
30	1	3
60	2	3
60	3	2
150	NULL	8

Hints

1. Build a month spine for the outage window using generate_series on the outage_window row.
2. 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.