Problem statement :

Data Structure:

Example data is provided in 'Data' worksheet

User_id: The unique identifier for each user. Billing_start_date: The start date of a user's billing cycle. Billing_end_date: The end date of a user's billing cycle. Billing_amount: The amount due for that billing cycle.

Sample data : Data

User_id	Billing_start_date	Billing_end_date	Billing_amount
1	2/17/2023 9:14	3/20/2023 9:14	462.87
1	3/20/2023 9:14	4/20/2023 9:14	116.55
1	4/20/2023 9:14	5/21/2023 9:14	188.54
1	5/21/2023 9:14	6/21/2023 9:14	247
1	6/21/2023 9:14	7/22/2023 9:14	753.45
2	8/26/2022 20:49	9/26/2022 20:49	597.59
2	9/26/2022 20:49	10/27/2022 20:49	438.42
2	10/27/2022 20:49	11/27/2022 20:49	551.41
2	11/27/2022 20:49	12/28/2022 20:49	447.75
2	12/28/2022 20:49	1/28/2023 20:49	75.7
2	1/28/2023 20:49	2/28/2023 20:49	587.77

Objective:

We want to transform this data into the below format with the following columns with a sql query:

Cohort_month: The month when the user's first billing cycle started, serving as the cohort identifier. Period: The number of months since the first billing cycle for each subsequent billing cycle. Total_users: The total number of users who started in the cohort month. User_count: The number of users from the cohort who are still active in each subsequent period.

Cohort_month	period	total_users	user_count
January-22	0	1250	1250
January-22	1	1250	1208
January-22	2	1250	1189
January-22	3	1250	1108
January-22	4	1250	1081
January-22	5	1250	1072
January-22	6	1250	1040
January-22	7	1250	1009
January-22	8	1250	967
February-22	0	780	780
February-22	1	780	764
February-22	2	780	741
February-22	3	780	722

Solution & Approach :

Utility :

We are trying to transform the data from a user level to a cohort x period level
This can help us do cohort and user behavior analytics, while giving a view of the business growth as well at a cohort level

Actions :