Here's an interesting SQL question I saw from a Chinese social media app, and it has a nice and simple solution.
Given this table:
CREATE TABLE membership_purchase_history (
purchase_id number primary key
,user_id number
,purchase_date date check (trunc(purchase_date,'mm')=purchase_date) --- let's assume it's always the first day of the month
,purchase_months number
) ;
INSERT INTO membership_purchase_history
select 101,1, DATE '2025-01-01', 2 from dual union all ---- user 1 purchases 2 months of membership and this will expire on 2025-3-1
select 102,1, DATE '2025-02-01', 1 from dual union all ---- add one more month and this extends the expiry date to 2025-4-1
select 103,1, DATE '2025-05-01', 1 from dual union all ---- new purchase beyond the last period and it creates a new period from 2025-05-01 to 2025-06-01
select 104,1, DATE '2025-05-01', 2 from dual union all ---- second purchase in the same day which extends the period to 2025-8-1
select 105,1, DATE '2025-10-01', 3 from dual union all
select 106,1, DATE '2025-11-01', 1 from dual union all
select 201,2, DATE '2025-01-01', 2 from dual union all
select 202,2, DATE '2025-02-01', 2 from dual union all
select 203,2, DATE '2025-04-01', 2 from dual union all
select 204,2, DATE '2025-06-01', 2 from dual union all
select 301,3, DATE '2025-03-01', 3 from dual union all
select 302,3, DATE '2025-01-01', 2 from dual union all
select 303,3, DATE '2025-02-01', 2 from dual
;
-- And we expect this output:
USER_ID START_DATE END_DATE
---------- ------------------- -------------------
1 2025-01-01 00:00:00 2025-04-01 00:00:00
1 2025-05-01 00:00:00 2025-08-01 00:00:00
1 2025-10-01 00:00:00 2026-02-01 00:00:00
2 2025-01-01 00:00:00 2025-09-01 00:00:00
3 2025-01-01 00:00:00 2025-08-01 00:00:00
Please solve it without MODEL clause or recursive CTE.