SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 247 of 250

Moderate

Compare daily active users and purchasers

Your task

For each calendar day with any event, return event_date, active_customers, and purchasers. A purchaser is a distinct customer with a purchase event on that day. Sort by date.

Counting unique customers gives a daily audience measure even when people generate several events.

Sample data

Table:product_events
idcustomer_idsession_keyevent_atevent_name
3011a-12025-01-02 09:00:00search
3021a-12025-01-02 09:01:00view_product
3031a-12025-01-02 09:02:00add_to_cart
3041a-12025-01-02 09:04:00purchase
3052c-12025-01-05 10:00:00view_product
3062c-12025-01-05 10:01:00purchase
3072c-12025-01-05 10:02:00add_to_cart
3083j-12025-01-12 11:00:00search
3093j-12025-01-12 11:02:00view_product
3104m-12025-02-01 08:00:00view_product

Preview of 10 of 25 rows

Expected output

event_dateactive_customerspurchasers
2025-01-0211
2025-01-0511
2025-01-1210
2025-02-0121
2025-02-0311
2025-02-0610
2025-02-0710
2025-02-0811
2025-02-2010
2025-03-0110
Show a hint

Group by DATE(event_at), using COUNT(DISTINCT CASE WHEN ... THEN customer_id END) for purchasers.

Product events and funnels guide

Choose the event, session, or customer grain

An event is one logged action. A session is a group of events with the same session_key. A customer can have several sessions. Choose the grain before counting so a busy visitor cannot accidentally count as many people.

SELECT event_name, COUNT(*) AS events, COUNT(DISTINCT session_key) AS sessions
FROM product_events
GROUP BY event_name
ORDER BY event_name;

Order events before evaluating a funnel

A funnel measures steps completed in a defined order. Events in the same session can share an exact timestamp, so use the event ID as a tie-breaker. A session with a purchase before the cart event does not meet a view, cart, purchase funnel.

SELECT session_key, event_name,
  ROW_NUMBER() OVER (PARTITION BY session_key ORDER BY event_at, id) AS position
FROM product_events
ORDER BY session_key, position;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…