Analyze Acquisition Channels for User Value and Retention

acquisition

| user_id | acquire_channel | acquire_date |
| 101 | organic | 2023-01-05 |
| 102 | paid_search | 2023-01-06 |
| 103 | social_media | 2023-01-08 |

transactions

| transaction_id | user_id | amount | transaction_date |
| 9001 | 101 | 45.50 | 2023-01-10 |
| 9002 | 101 | 19.00 | 2023-02-05 |
| 9003 | 102 | 60.00 | 2023-02-12 |
| 9004 | 103 | 30.00 | 2023-03-01 |

Scenario

An e-commerce platform provided two tables: one logging the channel and date each user was acquired, another logging every purchase. Product managers want actionable insights on acquisition effectiveness and user value.

Question

Write a query that returns each acquire_channel together with the count of distinct users acquired from it. 2. For every acquire_channel, rank users by their cumulative spend and output the top three spenders per channel with their totals. 3. Build a monthly cohort table that shows, for each acquisition month, the percentage of users who make at least one purchase in any subsequent month (retention). 4. Within 90 days of each user’s acquire_date, compute average revenue per user (ARPU) by channel and identify the channel with the highest ARPU.

Hints

Expect multiple CTEs, DATE_DIFF/DATE_TRUNC, JOINs, window functions such as SUM() OVER and RANK().

Users per Acquisition Channel

For each acquire_channel, count distinct acquired users.

Table acquisition

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
acquire_channel	VARCHAR(50)	NOT NULL
acquire_date	DATE	NOT NULL

Sample data:

acquire_channel	acquire_date	user_id
organic	2023-01-05	101
paid_search	2023-01-06	102
social_media	2023-01-08	103

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES acquisition(user_id)
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL

Sample data:

amount	transaction_date	transaction_id	user_id
45.5	2023-01-10	9001	101
19	2023-02-05	9002	101
60	2023-02-12	9003	102
30	2023-03-01	9004	103

Expected output

User counts by acquisition channel.

acquire_channel	user_count
organic	1
paid_search	1
social_media	1

Hints

1. Use COUNT(DISTINCT user_id) grouped by channel
2. Group by acquire_channel to aggregate
3. Order output for deterministic results

Top Spenders per Channel

For each acquisition channel, rank users by their total cumulative spend and return the top three spenders per channel.

Table acquisition

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
acquire_channel	VARCHAR(50)	NOT NULL
acquire_date	DATE	NOT NULL

Sample data:

acquire_channel	acquire_date	user_id
organic	2023-01-05	101
paid_search	2023-01-06	102
social_media	2023-01-08	103

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES acquisition(user_id)
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL

Sample data:

amount	transaction_date	transaction_id	user_id
45.5	2023-01-10	9001	101
19	2023-02-05	9002	101
60	2023-02-12	9003	102
30	2023-03-01	9004	103

Expected output

Top three spenders per channel with their cumulative spend and rank.

acquire_channel	spend_rank	total_spend	user_id
organic	1	64.5	101
paid_search	1	60	102
social_media	1	30	103

Hints

1. Aggregate SUM(amount) per user per channel
2. Use RANK() partitioned by acquire_channel ordered by total_spend DESC
3. LEFT JOIN ensures zero-spend users are considered

Monthly Cohort Retention

You are given two tables:

• acquisition — one row per acquired user: user_id, acquire_channel, acquire_date.
• transactions — one row per purchase: transaction_id, user_id, amount, transaction_date.

Build a monthly cohort-retention table. Group users into cohorts by their acquisition month (the calendar month of acquire_date). For each cohort and each positive month offset (1 = the month after acquisition, 2 = two months after, and so on), report how many users from that cohort were retained in that offset month.

A user is retained at offset k (for k >= 1) if they made at least one purchase in the calendar month that is exactly k months after their acquisition month. (A purchase in the acquisition month itself is offset 0 and is not counted as retention.) Compute the month offset from the difference between the transaction's month and the acquisition month, not from raw day counts.

Required output

Return one row per (acquire_month, month_offset) pair that has at least one retained user, with these columns:

• acquire_month — the first day of the cohort's acquisition month, formatted as YYYY-MM-DD text.
• month_offset — the integer month offset (>= 1).
• retained_users — the number of distinct users from the cohort retained at that offset.
• cohort_size — the total number of users acquired in that cohort month.
• retention_pct — retained_users / cohort_size, rounded to 4 decimal places.

Sort the result by acquire_month ascending, then month_offset ascending.

Table acquisition

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
acquire_channel	VARCHAR(50)	NOT NULL
acquire_date	DATE	NOT NULL

Sample data:

acquire_channel	acquire_date	user_id
organic	2023-01-05	101
paid_search	2023-01-06	102
social_media	2023-01-08	103

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES acquisition(user_id)
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL

Sample data:

amount	transaction_date	transaction_id	user_id
45.5	2023-01-10	9001	101
19	2023-02-05	9002	101
60	2023-02-12	9003	102
30	2023-03-01	9004	103

Expected output

One row per (acquisition month, month offset >= 1) with retained users, cohort size, and retention percentage, ordered by month then offset.

acquire_month	cohort_size	month_offset	retained_users	retention_pct
2023-01-01	3	1	2	0.6667
2023-01-01	3	2	1	0.3333

Hints

1. Use DATE_TRUNC('month', col) to bucket both acquisition and transaction dates into months.
2. In PostgreSQL there is no DATE_DIFF in months — derive the offset as (year_diff * 12 + month_diff) using EXTRACT(YEAR ...) and EXTRACT(MONTH ...).
3. Count DISTINCT users per offset, then divide by the cohort size using ::numeric and NULLIF to round safely to 4 decimals.

90-Day ARPU by Channel

90-Day ARPU by Acquisition Channel

You are given two tables that describe how users were acquired and the revenue they generated.

• acquisition — one row per user: user_id, acquire_channel (e.g. organic, paid_search, social_media), and acquire_date (the date the user was acquired).
• transactions — one row per transaction: transaction_id, user_id, amount, and transaction_date.

For each acquisition channel, compute the 90-day ARPU (Average Revenue Per User). A user's 90-day revenue is the sum of amount for that user's transactions whose transaction_date falls on or after the user's acquire_date and on or before acquire_date + 90 days (an inclusive 90-day window). Users with no qualifying transactions count as 0 revenue but must still be counted in the channel's user total.

Return one row per channel with the following columns:

• acquire_channel — the channel name.
• users — the number of users acquired through that channel.
• revenue_90d — the total 90-day revenue across that channel's users.
• arpu_90d — revenue_90d / users, rounded to 2 decimal places.
• arpu_rank — the channel's rank by arpu_90d descending (rank 1 = highest ARPU). Break ties alphabetically by acquire_channel (so tied channels receive distinct, deterministic ranks).

Order the result by arpu_rank ascending, then by acquire_channel ascending.

Table acquisition

column	type	constraints
user_id	INTEGER	PRIMARY KEY, NOT NULL
acquire_channel	VARCHAR(50)	NOT NULL
acquire_date	DATE	NOT NULL

Sample data:

acquire_channel	acquire_date	user_id
organic	2023-01-05	101
paid_search	2023-01-06	102
social_media	2023-01-08	103
organic	2023-01-10	104
paid_search	2023-02-01	105

Table transactions

column	type	constraints
transaction_id	INTEGER	PRIMARY KEY, NOT NULL
user_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES acquisition(user_id)
amount	DECIMAL(10,2)	NOT NULL
transaction_date	DATE	NOT NULL

Sample data:

amount	transaction_date	transaction_id	user_id
45.5	2023-01-10	9001	101
19	2023-02-05	9002	101
100	2023-05-01	9003	101
60	2023-02-12	9004	102
30	2023-03-01	9005	103
80	2023-02-20	9006	104

Expected output

One row per acquisition channel with user count, 90-day revenue, 90-day ARPU (rounded to 2 dp), and ARPU rank (rank 1 = highest), ordered by rank then channel.

acquire_channel	arpu_90d	arpu_rank	revenue_90d	users
organic	72.25	1	144.50	2
paid_search	30.00	2	60.00	2
social_media	30.00	3	30.00	1

Hints

1. Left-join acquisition to transactions so users with zero qualifying transactions still appear, then sum amounts only inside the 90-day window with a CASE.
2. In PostgreSQL, add 90 days with acquire_date + INTERVAL '90 days' (not DATE_ADD); guard the ARPU division with NULLIF(count, 0).
3. Rank with RANK() OVER (ORDER BY arpu_90d DESC, acquire_channel) so ties break deterministically.