Lesson 17.4

SQL Playground Mission: Channel Conversion Report

Rank marketing channels by purchase rate without dropping zero-conversion channels.

Conversion reports need a careful base. If you start from purchase events, channels with no purchases vanish. Starting from sessions and using a LEFT JOIN keeps every channel in view.

This mission also uses distinct counts because a session can contain more than one event row. The result is a clean channel report: total sessions, purchasing sessions, and purchase rate.

Pattern

select base.segment,
       count(distinct base.id) as total,
       count(distinct event.id) filter (where event.name = 'target') as converted,
       round(100.0 * converted / total, 1) as conversion_rate
from base
left join event on event.id = base.id
group by base.segment

Schema · Website AnalyticsTable · sessions7 columns · 30 rows

Table · sessions

One row per visit. Anonymous visits have a null user_id; organic and direct visits have a null campaign_id.

7 columns · 30 rows

session_id intuser_id intsession_date datechannel textcampaign_id intdevice textduration_seconds int

SchemaTable · events4 columns · 39 rows

Table · events

Funnel events (signup, add_to_cart, checkout, purchase). Only purchase events carry a value.

4 columns · 39 rows

event_id intsession_id intevent_name textvalue decimal

Your task

Return one row per channel with channel, sessions,purchasing_sessions, and purchase_rate. Sort by purchase rate descending, then channel.