Interview Questions/SQL/Campaign Funnel Analysis (View → Cart → Purchase)

Campaign Funnel Analysis (View → Cart → Purchase)

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

Medium

Campaign Funnel Analysis (View → Cart → Purchase)

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

Examples

Example 1

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.

query.sql