Compute monthly signups, conversion, and YoY growth

You work at a subscription company and are given a user-level table.

Table

company_users

• id (INT, PK) — user/customer id
• signup_date (DATE) — date the user signed up (created an account)
• subscription_date (DATE, NULL) — date the user first started a subscription (NULL if never subscribed)
• termination_date (DATE, NULL) — date the subscription ended (NULL if still active)
• subscription_type (VARCHAR) — 'free' or 'paid' (assume this reflects the first subscription started)

Assumptions:

• Dates are in UTC.
• A “subscription” means subscription_date IS NOT NULL.
• “Conversion rate by month” is defined as: for users who signed up in a given calendar month, the fraction who ever subscribed (at any later time).
• Report results for months starting 2017-01-01 (inclusive).

Tasks

1. For each calendar month (by signup_date month), return:
   • month (e.g., 2017-01-01 for Jan 2017)
   • signups (count of users who signed up that month)
   • subscribers (count of those signups who ever subscribed)
   • conversion_rate = subscribers / signups
2. Compute YoY subscription rate growth for each month as:
   • yoy_subscription_rate_growth = $\frac{conversion\_rate - conversion\_rate\_{same\_month\_last\_year}}{conversion\_rate\_{same\_month\_last\_year}}$
   • Return NULL if the prior-year month is not available.
3. Now assume the dataset also includes free subscriptions (subscription_type='free'). Recompute (2) but only for paid subscriptions (i.e., treat a user as “subscribed” only if subscription_type='paid').

Output

A monthly table with columns:

• month, signups, subscribers, conversion_rate, yoy_subscription_rate_growth

(And a paid-only version for task #3.)

Monthly signups, conversion rate, and YoY conversion-rate growth (all subscriptions)

You are given a companies table (signups) and a subscriptions table (who converted). Starting from 2017-01-01 (inclusive), compute monthly metrics by signup month:

• signups: number of companies that signed up in the month

• conversions: number of those companies that have a subscription of any plan type (free or paid)

• conversion_rate: conversions / signups

• yoy_conversion_rate_growth: the year-over-year growth of conversion_rate vs the same calendar month in the prior year, computed as:

  (conversion_rate - prior_year_conversion_rate) / prior_year_conversion_rate

Return one row per signup month that exists in the data. If there is no prior-year month to compare, return NULL for yoy_conversion_rate_growth.

Output columns: month_start, signups, conversions, conversion_rate, yoy_conversion_rate_growth.

Round rates/growth to 4 decimal places.

Table companies

column	type	constraints
company_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL

Sample data:

company_id	signup_date
1	2017-01-05
2	2017-01-10
3	2017-01-15
4	2017-02-02
5	2017-02-10
6	2017-02-25
13	2017-03-12
14	2017-03-20
7	2018-01-03
8	2018-01-20
9	2018-02-05
10	2018-02-18
11	2018-02-20
12	2018-03-01
15	2018-03-10
16	2018-03-22

Table subscriptions

column	type	constraints
subscription_id	INT	PRIMARY KEY
company_id	INT	NOT NULL
start_date	DATE	NOT NULL
plan_type	VARCHAR(10)	NOT NULL
termination_date	DATE

Sample data:

company_id	plan_type	start_date	subscription_id	termination_date
1	paid	2017-01-20	101	NULL
3	free	2017-02-01	103	NULL
4	paid	2017-02-15	104	2017-12-31
5	free	2017-02-28	105	NULL
14	paid	2017-04-01	114	NULL
7	paid	2018-01-25	107	NULL
8	free	2018-02-10	108	NULL
9	paid	2018-02-20	109	NULL
11	paid	2018-03-05	111	NULL
12	free	2018-03-15	112	NULL
15	paid	2018-03-28	115	NULL

Expected output

One row per signup month with counts and rates for all subscriptions (free + paid). month_start is the first day of the month.

conversion_rate	conversions	month_start	signups	yoy_conversion_rate_growth
0.6667	2	2017-01-01	3	NULL
0.6667	2	2017-02-01	3	NULL
0.5	1	2017-03-01	2	NULL
1	2	2018-01-01	2	0.4999
0.6667	2	2018-02-01	3	0
0.6667	2	2018-03-01	3	0.3334

Hints

1. Compute monthly signups first, then join the monthly result to itself shifted by 1 year.
2. Use NULLIF to avoid division-by-zero and return NULL when prior-year data is missing.

Monthly signups and YoY growth for paid-only conversion rate

Using the same tables, compute the same monthly metrics starting from 2017-01-01 (inclusive), but only count conversions where the company has a PAID subscription (plan_type = 'paid').

• signups: companies signed up in that month

• paid_conversions: those signups that have a paid subscription

• paid_conversion_rate: paid_conversions / signups

• yoy_paid_conversion_rate_growth: year-over-year growth of paid_conversion_rate vs the same month in the prior year:

  (paid_conversion_rate - prior_year_paid_conversion_rate) / prior_year_paid_conversion_rate

Return one row per signup month that exists in the data. Round rates/growth to 4 decimals.

Output columns: month_start, signups, paid_conversions, paid_conversion_rate, yoy_paid_conversion_rate_growth.

Table companies

column	type	constraints
company_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL

Sample data:

company_id	signup_date
1	2017-01-05
2	2017-01-10
3	2017-01-15
4	2017-02-02
5	2017-02-10
6	2017-02-25
13	2017-03-12
14	2017-03-20
7	2018-01-03
8	2018-01-20
9	2018-02-05
10	2018-02-18
11	2018-02-20
12	2018-03-01
15	2018-03-10
16	2018-03-22

Table subscriptions

column	type	constraints
subscription_id	INT	PRIMARY KEY
company_id	INT	NOT NULL
start_date	DATE	NOT NULL
plan_type	VARCHAR(10)	NOT NULL
termination_date	DATE

Sample data:

company_id	plan_type	start_date	subscription_id	termination_date
1	paid	2017-01-20	101	NULL
3	free	2017-02-01	103	NULL
4	paid	2017-02-15	104	2017-12-31
5	free	2017-02-28	105	NULL
14	paid	2017-04-01	114	NULL
7	paid	2018-01-25	107	NULL
8	free	2018-02-10	108	NULL
9	paid	2018-02-20	109	NULL
11	paid	2018-03-05	111	NULL
12	free	2018-03-15	112	NULL
15	paid	2018-03-28	115	NULL

Expected output

One row per signup month with paid-only conversion rate and YoY growth (vs the same month in the prior year).

month_start	paid_conversion_rate	paid_conversions	signups	yoy_paid_conversion_rate_growth
2017-01-01	0.3333	1	3	NULL
2017-02-01	0.3333	1	3	NULL
2017-03-01	0.5	1	2	NULL
2018-01-01	0.5	1	2	0.5
2018-02-01	0.6667	2	3	1
2018-03-01	0.3333	1	3	-0.3333

Hints

1. Filter paid subscriptions in the JOIN condition so signups with no paid conversion are still kept.
2. Self-join the monthly aggregate on (month_start - interval '1 year') to compute YoY.

Pivot daily users and revenue by platform (web vs mobile)

You are given a purchases table with one row per purchase event.

For each file_date, compute:

• distinct purchasers and total revenue on web
• distinct purchasers and total revenue on mobile

Return one row per day with the following columns:

• day
• web_total_users
• web_total_revenue
• mobile_total_users
• mobile_total_revenue

If a platform has no purchases on a given day, return 0 for its totals.

This is a pivot-style output: one row per day, platforms as columns.

Table purchases

column	type	constraints
file_date	DATE	NOT NULL
user_id	INT	NOT NULL
sku	VARCHAR(10)	NOT NULL
price	DECIMAL(10,2)	NOT NULL
channel	VARCHAR(20)	NOT NULL
customer_segment	VARCHAR(20)	NOT NULL
platform	VARCHAR(10)	NOT NULL

Sample data:

channel	customer_segment	file_date	platform	price	sku	user_id
seo	alpha	2024-01-01	web	57	A	123
brand	beta	2024-01-01	web	59	B	765
seo	alpha	2024-01-01	web	10	E	123
app	gamma	2024-01-01	mobile	20	M	111
tv	alpha	2024-01-07	mobile	100	C	943
seo	beta	2024-01-07	web	30	F	222
radio	gamma	2024-01-09	mobile	103	A	587
tv	alpha	2024-01-11	web	102	D	976
brand	alpha	2024-01-11	mobile	50	G	333

Expected output

One row per day with platform totals pivoted into columns.

day	mobile_total_revenue	mobile_total_users	web_total_revenue	web_total_users
2024-01-01	20	1	126	2
2024-01-07	100	1	30	1
2024-01-09	103	1	0	0
2024-01-11	50	1	102	1

Hints

1. Use conditional aggregation (CASE WHEN) to pivot platform values into columns.
2. Count distinct user_id per platform to get total users, and sum price for revenue.