Ticket Shop Daily Funnel

A theme park's mobile app sells day tickets in three steps: view_tickets, add_to_basket, checkout. Product wants a daily funnel.

For each calendar day that has at least one view_tickets event, count distinct guests who on that same day:

  • viewers: viewed tickets;
  • basket_adders: added to basket at or after one of their views;
  • buyers: checked out at or after one of those basket adds (so view ≤ basket ≤ checkout in time).

Also return view_to_basket_pct = 100 × basket_adders / viewers and basket_to_buy_pct = 100 × buyers / basket_adders, each rounded to 1 decimal place; basket_to_buy_pct is NULL when basket_adders is 0. Steps must happen on the same day; other actions are ignored.

Columns: activity_date (YYYY-MM-DD), viewers, basket_adders, buyers, view_to_basket_pct, basket_to_buy_pct, ordered by activity_date. No view events means no rows.

Tables

ticket_app_events — Clickstream from the ticket app, one row per event.

ColumnTypeDescription
event_id (key)INTEGERUnique id (not necessarily in time order)
guest_idINTEGERApp user
actionTEXT'view_tickets', 'add_to_basket', 'checkout' or other actions such as 'open_park_map'
happened_atTEXTEvent time, 'YYYY-MM-DD HH:MM:SS'

Example

ticket_app_events

event_idguest_idactionhappened_at
1201view_tickets2026-07-04 09:00:10
2201add_to_basket2026-07-04 09:03:00
3201checkout2026-07-04 09:05:30
4202view_tickets2026-07-04 10:12:00
5202add_to_basket2026-07-04 10:15:00
6203checkout2026-07-04 11:00:00
7203add_to_basket2026-07-04 11:02:00
8203view_tickets2026-07-04 11:05:00
9204view_tickets2026-07-04 12:00:00
10204open_park_map2026-07-04 12:01:00
11201view_tickets2026-07-05 08:30:00
12205view_tickets2026-07-05 14:00:00

… 3 more rows

Expected output

activity_dateviewersbasket_addersbuyersview_to_basket_pctbasket_to_buy_pct
2026-07-044215050
2026-07-0521150100

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Product Analytics · problem 70 of 70 · SQLite

Work at the grain of one guest on one day. Start from guests who viewed tickets that day.
For each such guest, the earliest view and then the earliest basket add at or after it are all you need: choosing the earliest options can only make the next step easier to satisfy.
LEFT JOIN the basket and checkout sets to the viewers so guests who drop out still count in the denominator.
Multiply by 100.0 (not 100) and wrap the basket denominator in NULLIF(..., 0).

The idea

A funnel is a chain of conditions per guest and day, then counts and ratios per day.

Step 1, viewers: group view events by day and guest and keep MIN(happened_at) as the first view.

Step 2, basket: join each viewer to their basket adds on the same day at or after the first view, and keep the earliest one. Using the earliest view is safe: any basket add that follows some view also follows the first view. Using the earliest qualifying basket add is safe for the same reason: it leaves the most room for a checkout after it.

Step 3, checkout: a guest is a buyer if any checkout happens at or after that first qualifying basket add.

Finally LEFT JOIN the three sets on (day, guest) and aggregate per day. COUNT(column) skips the NULLs from the left joins, so it counts only guests who reached each step. Multiply by 100.0 to force real division and use NULLIF(basket_adders, 0) so a day with no basket adds gives NULL rather than an error.

The alternative computes two EXISTS flags per guest and day with self-joins (view ≤ basket, view ≤ basket ≤ checkout) and sums them.

Solution

WITH ev AS (
  SELECT guest_id, date(happened_at) AS activity_date, action, happened_at
  FROM ticket_app_events
),
viewed AS (          -- earliest view per guest and day
  SELECT activity_date, guest_id, MIN(happened_at) AS first_view
  FROM ev
  WHERE action = 'view_tickets'
  GROUP BY activity_date, guest_id
),
basketed AS (        -- earliest basket add at or after that view
  SELECT v.activity_date, v.guest_id, MIN(e.happened_at) AS first_basket
  FROM viewed AS v
  JOIN ev AS e
    ON e.guest_id = v.guest_id
   AND e.activity_date = v.activity_date
   AND e.action = 'add_to_basket'
   AND e.happened_at >= v.first_view
  GROUP BY v.activity_date, v.guest_id
),
bought AS (          -- any checkout at or after that basket add
  SELECT DISTINCT b.activity_date, b.guest_id
  FROM basketed AS b
  JOIN ev AS e
    ON e.guest_id = b.guest_id
   AND e.activity_date = b.activity_date
   AND e.action = 'checkout'
   AND e.happened_at >= b.first_basket
)
SELECT
  v.activity_date,
  COUNT(*)            AS viewers,
  COUNT(b.guest_id)   AS basket_adders,
  COUNT(p.guest_id)   AS buyers,
  ROUND(100.0 * COUNT(b.guest_id) / COUNT(*), 1) AS view_to_basket_pct,
  ROUND(100.0 * COUNT(p.guest_id) / NULLIF(COUNT(b.guest_id), 0), 1) AS basket_to_buy_pct
FROM viewed AS v
LEFT JOIN basketed AS b
  ON b.activity_date = v.activity_date AND b.guest_id = v.guest_id
LEFT JOIN bought AS p
  ON p.activity_date = v.activity_date AND p.guest_id = v.guest_id
GROUP BY v.activity_date
ORDER BY v.activity_date;

Another way

WITH ev AS (
  SELECT guest_id, date(happened_at) AS d, action, happened_at
  FROM ticket_app_events
),
guest_days AS (
  SELECT DISTINCT d, guest_id FROM ev WHERE action = 'view_tickets'
),
flags AS (
  SELECT
    g.d,
    EXISTS (
      SELECT 1
      FROM ev v JOIN ev b
        ON b.guest_id = v.guest_id AND b.d = v.d
       AND b.action = 'add_to_basket' AND b.happened_at >= v.happened_at
      WHERE v.guest_id = g.guest_id AND v.d = g.d AND v.action = 'view_tickets'
    ) AS did_basket,
    EXISTS (
      SELECT 1
      FROM ev v
      JOIN ev b
        ON b.guest_id = v.guest_id AND b.d = v.d
       AND b.action = 'add_to_basket' AND b.happened_at >= v.happened_at
      JOIN ev c
        ON c.guest_id = b.guest_id AND c.d = b.d
       AND c.action = 'checkout' AND c.happened_at >= b.happened_at
      WHERE v.guest_id = g.guest_id AND v.d = g.d AND v.action = 'view_tickets'
    ) AS did_buy
  FROM guest_days AS g
)
SELECT
  d AS activity_date,
  COUNT(*) AS viewers,
  SUM(did_basket) AS basket_adders,
  SUM(did_buy) AS buyers,
  ROUND(100.0 * SUM(did_basket) / COUNT(*), 1) AS view_to_basket_pct,
  ROUND(100.0 * SUM(did_buy) / NULLIF(SUM(did_basket), 0), 1) AS basket_to_buy_pct
FROM flags
GROUP BY d
ORDER BY d;

Why common answers fail

Checks that each step happened on the day but ignores their order, so a guest who checked out before viewing still counts as a buyer.

WITH gd AS (
  SELECT date(happened_at) AS d, guest_id,
         MAX(action = 'view_tickets') AS v,
         MAX(action = 'add_to_basket') AS b,
         MAX(action = 'checkout') AS c
  FROM ticket_app_events
  GROUP BY d, guest_id
)
SELECT d AS activity_date, SUM(v) AS viewers, SUM(v * b) AS basket_adders, SUM(v * b * c) AS buyers,
       ROUND(100.0 * SUM(v * b) / SUM(v), 1) AS view_to_basket_pct,
       ROUND(100.0 * SUM(v * b * c) / NULLIF(SUM(v * b), 0), 1) AS basket_to_buy_pct
FROM gd
GROUP BY d
HAVING SUM(v) > 0
ORDER BY d;

Uses integer arithmetic (100 * a / b), so 1 out of 3 becomes 33.0 instead of 33.3.

WITH ev AS (SELECT guest_id, date(happened_at) AS d, action, happened_at FROM ticket_app_events),
viewed AS (SELECT d, guest_id, MIN(happened_at) AS fv FROM ev WHERE action = 'view_tickets' GROUP BY d, guest_id),
basketed AS (SELECT v.d, v.guest_id, MIN(e.happened_at) AS fb FROM viewed v JOIN ev e ON e.guest_id = v.guest_id AND e.d = v.d AND e.action = 'add_to_basket' AND e.happened_at >= v.fv GROUP BY v.d, v.guest_id),
bought AS (SELECT DISTINCT b.d, b.guest_id FROM basketed b JOIN ev e ON e.guest_id = b.guest_id AND e.d = b.d AND e.action = 'checkout' AND e.happened_at >= b.fb)
SELECT v.d AS activity_date, COUNT(*) AS viewers, COUNT(b.guest_id) AS basket_adders, COUNT(p.guest_id) AS buyers,
       ROUND(100 * COUNT(b.guest_id) / COUNT(*), 1) AS view_to_basket_pct,
       ROUND(100 * COUNT(p.guest_id) / NULLIF(COUNT(b.guest_id), 0), 1) AS basket_to_buy_pct
FROM viewed v LEFT JOIN basketed b ON b.d = v.d AND b.guest_id = v.guest_id
LEFT JOIN bought p ON p.d = v.d AND p.guest_id = v.guest_id
GROUP BY v.d ORDER BY v.d;

Only requires the checkout to follow the view, not the basket add, so a guest who checked out before adding to basket is still a buyer.

WITH ev AS (SELECT guest_id, date(happened_at) AS d, action, happened_at FROM ticket_app_events),
viewed AS (SELECT d, guest_id, MIN(happened_at) AS fv FROM ev WHERE action = 'view_tickets' GROUP BY d, guest_id),
basketed AS (SELECT v.d, v.guest_id, MIN(e.happened_at) AS fb FROM viewed v JOIN ev e ON e.guest_id = v.guest_id AND e.d = v.d AND e.action = 'add_to_basket' AND e.happened_at >= v.fv GROUP BY v.d, v.guest_id),
bought AS (SELECT DISTINCT v.d, v.guest_id FROM viewed v JOIN ev e ON e.guest_id = v.guest_id AND e.d = v.d AND e.action = 'checkout' AND e.happened_at >= v.fv)
SELECT v.d AS activity_date, COUNT(*) AS viewers, COUNT(b.guest_id) AS basket_adders, COUNT(p.guest_id) AS buyers,
       ROUND(100.0 * COUNT(b.guest_id) / COUNT(*), 1) AS view_to_basket_pct,
       ROUND(100.0 * COUNT(p.guest_id) / NULLIF(COUNT(b.guest_id), 0), 1) AS basket_to_buy_pct
FROM viewed v LEFT JOIN basketed b ON b.d = v.d AND b.guest_id = v.guest_id
LEFT JOIN bought p ON p.d = v.d AND p.guest_id = v.guest_id
GROUP BY v.d ORDER BY v.d;

Counts events rather than distinct guests, so repeated clicks inflate every step.

SELECT date(happened_at) AS activity_date,
       SUM(action = 'view_tickets') AS viewers,
       SUM(action = 'add_to_basket') AS basket_adders,
       SUM(action = 'checkout') AS buyers,
       ROUND(100.0 * SUM(action = 'add_to_basket') / SUM(action = 'view_tickets'), 1) AS view_to_basket_pct,
       ROUND(100.0 * SUM(action = 'checkout') / NULLIF(SUM(action = 'add_to_basket'), 0), 1) AS basket_to_buy_pct
FROM ticket_app_events
GROUP BY date(happened_at)
HAVING SUM(action = 'view_tickets') > 0
ORDER BY activity_date;

What the interviewer is checking

Interviewers want to see that you define funnel steps per user with ordering in time, keep drop-offs in the denominator and avoid integer division. Ignoring order is the most common way funnel numbers come out too high.

Mistakes to avoid

  • Ignoring event order, so out-of-sequence actions count as conversions.
  • Counting events instead of distinct guests.
  • Integer division, or dividing by zero on days without basket adds.
  • Grouping by the raw timestamp instead of date(happened_at).

Follow-ups to expect

  • Allow the steps to span up to 24 hours instead of a calendar day (compare julianday differences).
  • Add median time from view to checkout (window ROW_NUMBER over the time differences).
  • Break the funnel down by platform or traffic source (add it to the grain and the GROUP BY).

In real work

Product analytics dashboards track exactly this kind of ordered step conversion for sign-up flows, checkouts and onboarding.

Practice next

Previous Post
No Comment
Add Comment
comment url