Lesson 13.5

SQL Practice: Conversion Stage Reach with Rates

Count distinct sessions that reached each conversion stage and turn each count into a percentage of all sessions.

This report counts how many sessions recorded each conversion-stage event: signup, add_to_cart, checkout, and purchase. The events table records these actions, one row per event. Because the query doesn't enforce stage order, it measures stage reach rather than a strict sequential funnel.

The catch: a single session can fire the same event twice. Counting rows would overstate reach, so you count distinct sessions per stage. It is a small change with a big effect on the numbers.

Two classic traps, together

This query combines two things beginners get wrong. First, count(distinct session_id) instead of count(*), so a chatty session is counted once. Second, 100.0 * rather than 100 *. DuckDB keeps this division fractional either way, but the decimal multiplier is a portable habit on engines where integer division truncates. A subquery supplies the total session count as the denominator.

Pattern

select category,
       count(distinct entity_id) as reached,
       round(100.0 * count(distinct entity_id) / (select count(*) from base_table), 1) as pct
from events_table
group by category
order by reached desc

Preparing for an interview? The funnel analysis interview questions extend this report, with model answers to compare against.

Schema · Website Analytics Tables · events, sessions 2 tables

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

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

Your task

For each event_name, return the event, the number of distinct sessions that reached it (sessions), and that as a percentage of all sessions, rounded to one decimal (pct_of_sessions). Sort by sessions descending, then event name.