Compare Conversion Rates by Channel

EasySQL · PostgreSQL

Problem

A company’s growth team wants to compare signup conversion performance across acquisition channels for a recent campaign. Write a SQL query to calculate conversion rates by channel using a single event table.

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Output details

Count matching event rows rather than distinct users for visits and signups.
Round conversion_rate to 4 decimal places.
Return NULL for conversion_rate when visits equals 0.

Schema

rippling_growth_events

Column	Type	Description
event_idPK	INT	Unique event row ID
user_id	INT	User identifier
channel	VARCHAR(50)	Acquisition channel associated with the event
event_type	VARCHAR(20)	Event type such as visit or signup
event_date	DATE	Date the event occurred

Tablesrippling_growth_events

Requirements

For each channel, count how many users had a visit event.
Count how many users had a signup event.
Return channel, visits, signups, and conversion_rate where conversion_rate = signups / visits.
Exclude rows where channel is NULL.
Order results by conversion_rate descending, then channel ascending.

Output details

Count matching event rows rather than distinct users for visits and signups.
Round conversion_rate to 4 decimal places.
Return NULL for conversion_rate when visits equals 0.