SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 244 of 250

Moderate

Find each active customer’s first event

Your task

Return customer_id, first_event_id, and event_name for each active customer’s first event. Order events by timestamp and then ID. Sort the result by customer ID.

The first timestamp alone does not identify which action happened first when timestamps tie.

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

customer_idfirst_event_idevent_name
1301search
2305view_product
3308search
4310view_product
6321search
7324view_product
8318view_product
Show a hint

Assign ROW_NUMBER by customer and keep the first row.

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…