Level 2 · Check and build the web funnel in SQL

What you are checking, and why

web_sessions is a tracking table: every visit to the site, its device and source, how many pages it saw and whether it ended in a purchase. Tracking tables are full of holes, so before you build a funnel on one, find out what it can and cannot show. Step 1 asks four questions in a single pass:

• Is any session counted twice? Compare COUNT(*) with COUNT(DISTINCT session_id).
• Are there empty sessions? A visit with zero pages is a tracking glitch.
• Who can convert? Count anonymous sessions, and how many of them converted.
• Does the definition hold? Count conversions with fewer than three pages.

All four are conditional counts with no GROUP BY, so they come back as one row:

SUM(CASE WHEN customer_id IS NULL AND converted THEN 1 ELSE 0 END) AS anonymous_converted

The zero in that column is the most important number in the case. More than half the sessions can never show a purchase, because purchases are only recorded against an account. The rates you build next are rates of recorded purchases, and the funnel needs a stage for signing in.

A funnel is a chain of conditional counts

A funnel follows the same sessions through stages that narrow down:

stage	condition
sessions	every row
engaged	pages_viewed >= 3
known and engaged	also customer_id IS NOT NULL
converted	converted is true

Each stage is a SUM(CASE WHEN ...) in the same GROUP BY device query. The rates divide a stage by the one before it, so you can see where people drop out: engaged_pct is the browsing step, close_pct is the checkout step. Multiply by 100.0, with the decimal point, before you divide; 563 * 100 / 2634 in integer arithmetic loses the decimals.

Reading the funnel

device	engaged_pct	close_pct	conversion_pct
desktop	44.1	21.4	3.87
mobile	44.1	12.9	2.41
tablet	44.8	14.6	2.81

The browsing step is the same on every device. The checkout step is where mobile falls behind. That changes the brief for the mobile team: the product pages are doing their job; the basket, the sign-in and the payment form are where to look.

Why the year view matters

Step 3 repeats the conversion rate by year, one column per device. Mobile’s share of traffic grew every year, and mobile converts less, so the overall rate is a weighted average whose weights keep moving towards the low rate. Every device improved from 2023 to 2025, while the overall rate grew much less. If you kept 2023’s device mix and applied 2025’s rates, the overall rate would be about 3.24% instead of 3.08%. Always split a rate by its segments before you call it a trend.

Pitfalls

• AVG(converted) on a boolean works in DuckDB, but SUM(CASE ...) makes the numerator explicit and works in every database.
• Dividing by COUNT(*) for the close rate mixes stages: divide by the stage just before.
• year(started_at) groups by calendar year; 2025 has more sessions than 2023, so compare rates, not counts.