Campaign Funnel Analysis (View → Cart → Purchase)

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of user events. Each event belongs to a campaign and is one of three types: 'view', 'cart', or 'purchase'. A user may fire multiple events of the same type. Write a SQL query to compute, per campaign: - viewers: distinct users who fired a 'view' event - cart_adds: distinct users who fired a 'cart' event - purchasers: distinct users who fired a 'purchase' event - view_to_cart_pct: ROUND(cart_adds / viewers * 100, 2) - use NULLIF to avoid division by zero - cart_to_purchase_pct: ROUND(purchasers / cart_adds * 100, 2) - use NULLIF to avoid division by zero Return results ordered by campaign. Table: Events

Column Name	Type	Description
id	INT	Primary key
user_id	INT	ID of the user
campaign	VARCHAR	Campaign identifier
event_type	VARCHAR	'view', 'cart', or 'purchase'
event_date	DATE	Date of the event

Database Schema (Inferred)

Events

Column Name	Example Value
id	1
user_id	1
campaign	CMP_A
event_type	view
event_date	2023-01-01

Example

Events

id	user_id	campaign	event_type	event_date
1	1	CMP_A	view	2023-01-01
2	1	CMP_A	cart	2023-01-02
3	1	CMP_A	purchase	2023-01-03
4	2	CMP_A	view	2023-01-01
5	2	CMP_A	cart	2023-01-04
6	3	CMP_A	view	2023-01-05
7	4	CMP_B	view	2023-01-01
8	4	CMP_B	purchase	2023-01-03
9	5	CMP_B	view	2023-01-01
10	5	CMP_B	cart	2023-01-02
11	5	CMP_B	purchase	2023-01-04
12	6	CMP_B	view	2023-01-02

Output

campaign	viewers	cart_adds	purchasers	view_to_cart_pct	cart_to_purchase_pct
CMP_A	3	2	1	66.67	50
CMP_B	3	1	2	33.33	200

Explanation:

A user who skips 'cart' and goes straight to 'purchase' counts as a purchaser but not a cart_add, which can make cart_to_purchase_pct exceed 100%. Use COUNT(DISTINCT CASE WHEN event_type='x' THEN user_id END) for each stage.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.