Skip to content
/ Chapter 17 · Guided Playground Missions
Lesson 17.4 · website_analytics
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 * count(distinct event.id) filter (where event.name = 'target')
             / count(distinct base.id), 1) as conversion_rate
from base
left join event on event.id = base.id
group by base.segment
Schema · Website Analytics Table · sessions 7 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 int user_id int session_date date channel text campaign_id int device text duration_seconds int
Schema Table · events 4 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 int session_id int event_name text value decimal
Your task

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

·

Run your query to inspect the result.

Expected answer
  • Columns: channel, sessions, purchasing_sessions, purchase_rate.
  • Rows: one per channel (5 rows).
  • Social has the highest purchase rate at 37.5%.
Next · 17.5 Retention cohort report →

Related

Reference
SQL Subqueries and EXISTS

Nested SELECTs, IN, EXISTS, and correlation.

Cheatsheet
SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview
SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake
SQL: Window Running Totals Can Jump on Ties

The default RANGE frame groups tied rows; use ROWS.

Error
Fix “Window Functions Are Not Allowed in WHERE” in SQL

Filter window results in a CTE or with QUALIFY.

Reference
SQL Common Table Expressions

The WITH clause, chained steps, and CTE vs subquery.