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
product_events| id | customer_id | session_key | event_at | event_name |
|---|---|---|---|---|
| 301 | 1 | a-1 | 2025-01-02 09:00:00 | search |
| 302 | 1 | a-1 | 2025-01-02 09:01:00 | view_product |
| 303 | 1 | a-1 | 2025-01-02 09:02:00 | add_to_cart |
| 304 | 1 | a-1 | 2025-01-02 09:04:00 | purchase |
| 305 | 2 | c-1 | 2025-01-05 10:00:00 | view_product |
| 306 | 2 | c-1 | 2025-01-05 10:01:00 | purchase |
| 307 | 2 | c-1 | 2025-01-05 10:02:00 | add_to_cart |
| 308 | 3 | j-1 | 2025-01-12 11:00:00 | search |
| 309 | 3 | j-1 | 2025-01-12 11:02:00 | view_product |
| 310 | 4 | m-1 | 2025-02-01 08:00:00 | view_product |
Preview of 10 of 25 rows
Expected output
| event_date | active_customers | purchasers |
|---|---|---|
| 2025-01-02 | 1 | 1 |
| 2025-01-05 | 1 | 1 |
| 2025-01-12 | 1 | 0 |
| 2025-02-01 | 2 | 1 |
| 2025-02-03 | 1 | 1 |
| 2025-02-06 | 1 | 0 |
| 2025-02-07 | 1 | 0 |
| 2025-02-08 | 1 | 1 |
| 2025-02-20 | 1 | 0 |
| 2025-03-01 | 1 | 0 |
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;Worked solution
SELECT DATE(event_at) AS event_date,COUNT(DISTINCT customer_id) AS active_customers,COUNT(DISTINCT CASE WHEN event_name='purchase' THEN customer_id END) AS purchasers FROM product_events GROUP BY event_date ORDER BY event_date;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
