Lesson 12.5

SQL Practice: A Web Conversion Funnel with Rates

The analytics staple: count distinct sessions per funnel stage and turn each into a percentage.

A conversion funnel counts how many sessions reach each stage: signup, add_to_cart, checkout, and purchase. The events table records these actions, one row per event.

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

Schema · Website AnalyticsTable · 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

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.

SQL Workbench

query.sqlwebsite_analytics · SQL engine loading

⌘↵ to run

Expected answer