Food App Checkout Funnel, Step by Step

Hard
170.

Food App Checkout Funnel, Step by Step

The checkout funnel has four steps in order: menu_view → add_to_cart → checkout → payment_success. A session reaches step 1 if it has a menu_view, and reaches each later step if it has an event of that step strictly later than the moment it reached the step before (the earliest such event). An add_to_cart before any menu view, for example, does not count.

Return four rows, one per step, with step_no (1–4), step (the event name), sessions (sessions that reached the step) and conversion_pct (sessions as a percentage of the previous step's sessions, rounded to 1 decimal place; NULL for step 1 and whenever the previous step has 0 sessions). Order the rows by step_no.

Tables

Table: AppEvent

ColumnTypeValues
event_idint
session_idint
event_typeenummenu_view, add_to_cart, checkout, payment_success
event_atdatetime

Primary key: event_id.

One row per tracked event in the food app; a session can repeat an event.

Example

The rows must come back in the order shown.

AppEvent

event_idsession_idevent_typeevent_at
1501menu_view2024-09-14 20:00:00
2501add_to_cart2024-09-14 20:02:00
3501checkout2024-09-14 20:04:00
4501payment_success2024-09-14 20:05:00
5502add_to_cart2024-09-14 20:10:00
6502menu_view2024-09-14 20:11:00
7503menu_view2024-09-14 20:20:00
8503add_to_cart2024-09-14 20:21:00
9503checkout2024-09-14 20:25:00
10504menu_view2024-09-14 21:00:00
1 more row

Output

step_nostepsessionsconversion_pct
1menu_view4NULL
2add_to_cart250
3checkout2100
4payment_success150
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Food App Checkout Funnel, Step by Step — SQL Problem

The checkout funnel has four steps in order: menu_view → add_to_cart → checkout → payment_success.

  • Difficulty: Hard
  • Topics: Conditional Logic, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #170

Problem statement

The checkout funnel has four steps in order: menu_view → add_to_cart → checkout → payment_success. A session reaches step 1 if it has a menu_view, and reaches each later step if it has an event of that step strictly later than the moment it reached the step before (the earliest such event). An add_to_cart before any menu view, for example, does not count.

Return four rows, one per step, with step_no (1–4), step (the event name), sessions (sessions that reached the step) and conversion_pct (sessions as a percentage of the previous step's sessions, rounded to 1 decimal place; NULL for step 1 and whenever the previous step has 0 sessions). Order the rows by step_no.

Tables

Table: AppEvent

ColumnType
event_idint
session_idint
event_typeenum(menu_view, add_to_cart, checkout, payment_success)
event_atdatetime

Primary key: event_id.

One row per tracked event in the food app; a session can repeat an event.

Examples

Example 1

AppEvent

event_idsession_idevent_typeevent_at
1501menu_view2024-09-14 20:00:00
2501add_to_cart2024-09-14 20:02:00
3501checkout2024-09-14 20:04:00
4501payment_success2024-09-14 20:05:00
5502add_to_cart2024-09-14 20:10:00
6502menu_view2024-09-14 20:11:00
7503menu_view2024-09-14 20:20:00
8503add_to_cart2024-09-14 20:21:00
9503checkout2024-09-14 20:25:00
10504menu_view2024-09-14 21:00:00
11504payment_success2024-09-14 21:01:00

Output

step_nostepsessionsconversion_pct
1menu_view4NULL
2add_to_cart250
3checkout2100
4payment_success150

How to solve Food App Checkout Funnel, Step by Step

An ordered funnel cannot be counted from event types alone: session 502 has a cart event, but only before it viewed a menu, and session 504 paid without a cart or checkout. So each session is walked through the steps in order, remembering when it reached each one.

Step 1 is the earliest menu_view per session. Step 2 starts from those sessions and takes the earliest add_to_cart strictly after that time; the inner join drops sessions that have none. Steps 3 and 4 repeat the pattern. Taking the earliest qualifying event at each step is the greedy choice that is always right: reaching a step sooner can only leave more later events eligible for the next one.

Each CTE's row count is the number of sessions at that step. Four scalar subqueries joined with UNION ALL turn them into the four required rows, and the conversion is sessions / previous — LAG(sessions) OVER (ORDER BY step_no), NULL on step 1, with NULLIF(…, 0) returning NULL instead of dividing by zero when nobody reached the previous step. The alternative computes the four times per session with correlated subqueries and counts the non-NULL ones with conditional aggregation. Each step is one join of the surviving sessions to the events.

Reference solution (MySQL)

WITH s1 AS (
  SELECT session_id, MIN(event_at) AS t FROM AppEvent WHERE event_type = 'menu_view' GROUP BY session_id
), s2 AS (
  SELECT p.session_id, MIN(e.event_at) AS t FROM s1 p
  JOIN AppEvent e ON e.session_id = p.session_id AND e.event_type = 'add_to_cart' AND e.event_at > p.t
  GROUP BY p.session_id
), s3 AS (
  SELECT p.session_id, MIN(e.event_at) AS t FROM s2 p
  JOIN AppEvent e ON e.session_id = p.session_id AND e.event_type = 'checkout' AND e.event_at > p.t
  GROUP BY p.session_id
), s4 AS (
  SELECT p.session_id, MIN(e.event_at) AS t FROM s3 p
  JOIN AppEvent e ON e.session_id = p.session_id AND e.event_type = 'payment_success' AND e.event_at > p.t
  GROUP BY p.session_id
), counts AS (
  SELECT 1 AS step_no, 'menu_view' AS step, (SELECT COUNT(*) FROM s1) AS sessions
  UNION ALL SELECT 2, 'add_to_cart', (SELECT COUNT(*) FROM s2)
  UNION ALL SELECT 3, 'checkout', (SELECT COUNT(*) FROM s3)
  UNION ALL SELECT 4, 'payment_success', (SELECT COUNT(*) FROM s4)
)
SELECT step_no, step, sessions,
       ROUND(100 * sessions / NULLIF(LAG(sessions) OVER (ORDER BY step_no), 0), 1) AS conversion_pct
FROM counts
ORDER BY step_no

Another way

WITH t AS (
  SELECT session_id, MIN(event_at) AS t1 FROM AppEvent WHERE event_type = 'menu_view' GROUP BY session_id
), t2 AS (
  SELECT session_id, t1, (SELECT MIN(e.event_at) FROM AppEvent e WHERE e.session_id = t.session_id
                         AND e.event_type = 'add_to_cart' AND e.event_at > t.t1) AS t2 FROM t
), t3 AS (
  SELECT session_id, t2, (SELECT MIN(e.event_at) FROM AppEvent e WHERE e.session_id = t2.session_id
                         AND e.event_type = 'checkout' AND e.event_at > t2.t2) AS t3 FROM t2
), t4 AS (
  SELECT session_id, t2, t3, (SELECT MIN(e.event_at) FROM AppEvent e WHERE e.session_id = t3.session_id
                             AND e.event_type = 'payment_success' AND e.event_at > t3.t3) AS t4 FROM t3
), totals AS (
  SELECT COUNT(*) AS c1,
         SUM(CASE WHEN t2 IS NOT NULL THEN 1 ELSE 0 END) AS c2,
         SUM(CASE WHEN t3 IS NOT NULL THEN 1 ELSE 0 END) AS c3,
         SUM(CASE WHEN t4 IS NOT NULL THEN 1 ELSE 0 END) AS c4
  FROM t4
)
SELECT 1 AS step_no, 'menu_view' AS step, c1 AS sessions, NULL AS conversion_pct FROM totals
UNION ALL SELECT 2, 'add_to_cart', COALESCE(c2, 0), CASE WHEN c1 > 0 THEN ROUND(100 * COALESCE(c2, 0) / c1, 1) END FROM totals
UNION ALL SELECT 3, 'checkout', COALESCE(c3, 0), CASE WHEN c2 > 0 THEN ROUND(100 * c3 / c2, 1) END FROM totals
UNION ALL SELECT 4, 'payment_success', COALESCE(c4, 0), CASE WHEN c3 > 0 THEN ROUND(100 * c4 / c3, 1) END FROM totals
ORDER BY step_no

← Dark Store Daily Orders, Zero Days Included · Rider Cash-in-Hand Between Deposits →