Meta: Instagram Reels Shopping Funnel, Steps in Order

Hard
771.

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. 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

ColumnTypeValues
event_idint
user_idint
event_typeenumreel_view, product_tap, add_to_cart, purchase
event_atdatetime

Primary key: event_id.

One row per logged event. A user can log the same event type many times, in any order.

Example

The rows must come back in the order shown.

ShopEvent

event_iduser_idevent_typeevent_at
11reel_view2025-08-01 10:00:00
21product_tap2025-08-01 10:05:00
31add_to_cart2025-08-01 10:07:00
41purchase2025-08-01 10:20:00
52product_tap2025-08-01 09:00:00
62reel_view2025-08-01 09:10:00
72add_to_cart2025-08-01 09:15:00
83reel_view2025-08-01 11:00:00
93product_tap2025-08-01 11:02:00
103purchase2025-08-01 11:30:00
5 more rows

Output

stepevent_typeusers
1reel_view4
2product_tap3
3add_to_cart2
4purchase1
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.

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

ColumnType
event_idint
user_idint
event_typeenum(reel_view, product_tap, add_to_cart, purchase)
event_atdatetime

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_iduser_idevent_typeevent_at
11reel_view2025-08-01 10:00:00
21product_tap2025-08-01 10:05:00
31add_to_cart2025-08-01 10:07:00
41purchase2025-08-01 10:20:00
52product_tap2025-08-01 09:00:00
62reel_view2025-08-01 09:10:00
72add_to_cart2025-08-01 09:15:00
83reel_view2025-08-01 11:00:00
93product_tap2025-08-01 11:02:00
103purchase2025-08-01 11:30:00
114reel_view2025-08-01 12:00:00
124product_tap2025-08-01 12:00:00
134product_tap2025-08-01 12:03:00
144add_to_cart2025-08-01 12:10:00
155purchase2025-08-01 13:00:00

Output

stepevent_typeusers
1reel_view4
2product_tap3
3add_to_cart2
4purchase1

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 step

Another 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 →