Meta: Instagram Reels Shopping Funnel, Steps in Order
Instagram Shopping measures a four-step funnel that starts from a Reel: reel_view → product_tap → add_to_cart → purchase.
- Difficulty: Hard
- Topics: Joins, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #771
Problem statement
Instagram Shopping measures a four-step funnel that starts from a Reel: reel_view → product_tap → add_to_cart → purchase. A user reaches step k when they have events of steps 1 through k at strictly increasing times, in that order — a tap at the very same second as the view, or a cart add before any qualifying tap, does not move them forward.
Return exactly four rows with the columns step (1–4), event_type (the step's event name) and users (how many users reached that step), ordered by step. A step nobody reached shows 0.
A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.
Tables
Table: ShopEvent
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_type | enum(reel_view, product_tap, add_to_cart, purchase) |
| event_at | datetime |
Primary key: event_id.
One row per logged event. A user can log the same event type many times, in any order.
Examples
Example 1
ShopEvent
| event_id | user_id | event_type | event_at |
|---|---|---|---|
| 1 | 1 | reel_view | 2025-08-01 10:00:00 |
| 2 | 1 | product_tap | 2025-08-01 10:05:00 |
| 3 | 1 | add_to_cart | 2025-08-01 10:07:00 |
| 4 | 1 | purchase | 2025-08-01 10:20:00 |
| 5 | 2 | product_tap | 2025-08-01 09:00:00 |
| 6 | 2 | reel_view | 2025-08-01 09:10:00 |
| 7 | 2 | add_to_cart | 2025-08-01 09:15:00 |
| 8 | 3 | reel_view | 2025-08-01 11:00:00 |
| 9 | 3 | product_tap | 2025-08-01 11:02:00 |
| 10 | 3 | purchase | 2025-08-01 11:30:00 |
| 11 | 4 | reel_view | 2025-08-01 12:00:00 |
| 12 | 4 | product_tap | 2025-08-01 12:00:00 |
| 13 | 4 | product_tap | 2025-08-01 12:03:00 |
| 14 | 4 | add_to_cart | 2025-08-01 12:10:00 |
| 15 | 5 | purchase | 2025-08-01 13:00:00 |
Output
| step | event_type | users |
|---|---|---|
| 1 | reel_view | 4 |
| 2 | product_tap | 3 |
| 3 | add_to_cart | 2 |
| 4 | purchase | 1 |
How to solve Meta: Instagram Reels Shopping Funnel, Steps in Order
A naive funnel counts users who ever logged each event type; this one requires the steps to happen in order. The question for every user is whether there is a chain view < tap < cart < purchase of strictly increasing times.
A greedy walk answers it. Take the user's earliest reel_view; from it, the earliest product_tap strictly later; from that, the earliest add_to_cart strictly later; and so on. Choosing the earliest event at each step is never worse than any other choice — an earlier time leaves more room for the next step — so a user who has some ordered chain is found by the greedy one. Each step is a CTE joined to the previous step's time with event_at > t, so equal timestamps do not advance anyone. The output is four constant rows glued with UNION ALL, each with a scalar count, which guarantees a 0 rather than a missing row for a step nobody reached.
The alternative checks for the existence of an ordered chain with self-joins and COUNT(DISTINCT user_id). It needs no greedy argument, but the joins multiply every combination of events before counting — fine for small logs, expensive for heavy users. The greedy CTEs keep one row per user per step.
Reference solution (MySQL)
WITH s1 AS (
SELECT user_id, MIN(event_at) AS t FROM ShopEvent WHERE event_type = 'reel_view' GROUP BY user_id
), s2 AS (
SELECT s1.user_id, MIN(e.event_at) AS t FROM s1
JOIN ShopEvent e ON e.user_id = s1.user_id AND e.event_type = 'product_tap' AND e.event_at > s1.t
GROUP BY s1.user_id
), s3 AS (
SELECT s2.user_id, MIN(e.event_at) AS t FROM s2
JOIN ShopEvent e ON e.user_id = s2.user_id AND e.event_type = 'add_to_cart' AND e.event_at > s2.t
GROUP BY s2.user_id
), s4 AS (
SELECT s3.user_id FROM s3
JOIN ShopEvent e ON e.user_id = s3.user_id AND e.event_type = 'purchase' AND e.event_at > s3.t
GROUP BY s3.user_id
)
SELECT 1 AS step, 'reel_view' AS event_type, (SELECT COUNT(*) FROM s1) AS users
UNION ALL SELECT 2, 'product_tap', (SELECT COUNT(*) FROM s2)
UNION ALL SELECT 3, 'add_to_cart', (SELECT COUNT(*) FROM s3)
UNION ALL SELECT 4, 'purchase', (SELECT COUNT(*) FROM s4)
ORDER BY stepAnother way
SELECT 1 AS step, 'reel_view' AS event_type, COUNT(DISTINCT user_id) AS users FROM ShopEvent WHERE event_type = 'reel_view'
UNION ALL
SELECT 2, 'product_tap', COUNT(DISTINCT a.user_id)
FROM ShopEvent a JOIN ShopEvent b ON b.user_id = a.user_id AND b.event_type = 'product_tap' AND b.event_at > a.event_at
WHERE a.event_type = 'reel_view'
UNION ALL
SELECT 3, 'add_to_cart', COUNT(DISTINCT a.user_id)
FROM ShopEvent a
JOIN ShopEvent b ON b.user_id = a.user_id AND b.event_type = 'product_tap' AND b.event_at > a.event_at
JOIN ShopEvent c ON c.user_id = a.user_id AND c.event_type = 'add_to_cart' AND c.event_at > b.event_at
WHERE a.event_type = 'reel_view'
UNION ALL
SELECT 4, 'purchase', COUNT(DISTINCT a.user_id)
FROM ShopEvent a
JOIN ShopEvent b ON b.user_id = a.user_id AND b.event_type = 'product_tap' AND b.event_at > a.event_at
JOIN ShopEvent c ON c.user_id = a.user_id AND c.event_type = 'add_to_cart' AND c.event_at > b.event_at
JOIN ShopEvent d ON d.user_id = a.user_id AND d.event_type = 'purchase' AND d.event_at > c.event_at
WHERE a.event_type = 'reel_view'
ORDER BY step← Meta: The Day Each Campaign Exhausted Its Lifetime Budget · Meta: Accounts Linked to Banned Accounts Through Shared Devices →