Skip to content
Onsite

Conversion funnel from view to cart to purchase

MediumP2Coding

Table events:

ColumnType
user_idint
event_nametext ('view', 'cart' or 'purchase')
created_attimestamp

A user moves through a funnel: view, then cart, then purchase, each step strictly later than the previous step (a step counts only after the user's first view, then the first cart after that, then a purchase after that cart). Events can repeat.

Return one row with the number of distinct users who reached each step.

Output columns: viewed, carted, purchased.

Schema

CREATE TABLE events (
  user_id int NOT NULL,
  event_name text NOT NULL,
  created_at timestamp NOT NULL
);

Example 1

Input
user 1: view 10:00, cart 10:05, purchase 10:10; user 2: view 10:00, cart 10:05; user 3: view 10:00
Output
viewed 3, carted 2, purchased 1

Example 2

Input
user 1: view 10:00, purchase 10:05, cart 10:10
Output
viewed 1, carted 1, purchased 0
Why
The purchase came before the cart.

Constraints

  • PostgreSQL dialect.

Hints

PostgreSQL
Run checks the 3 visible tests, Submit all 7.