Rider App Sessions and Booking Conversion — SQL Problem
The product team measures how often a visit to the rider app ends in a booking.
- Difficulty: Hard
- Topics: Window Functions, Conditional Logic, Dates
- Dialect: MySQL
- Problem: #317
Problem statement
The product team measures how often a visit to the rider app ends in a booking. A rider's events are split into sessions: a new session starts at the rider's first event and whenever more than 30 minutes have passed since the rider's previous event (a gap of exactly 30 minutes stays in the same session). A session converts when it contains at least one booking event.
Return rider_id, sessions, booking_sessions and conversion_pct (booking sessions ÷ sessions × 100, rounded to 2 decimals) for every rider with events. Order by conversion_pct descending, then rider_id ascending.
Tables
Table: AppEvent
| Column | Type |
|---|---|
| event_id | int |
| rider_id | int |
| event_at | datetime |
| event_type | enum(app_open, search, fare_check, booking, payment) |
Primary key: event_id.
No two events of one rider share a timestamp.
Examples
Example 1
AppEvent
| event_id | rider_id | event_at | event_type |
|---|---|---|---|
| 1 | 301 | 2024-06-01 09:00:00 | app_open |
| 2 | 301 | 2024-06-01 09:02:00 | search |
| 3 | 301 | 2024-06-01 09:32:00 | booking |
| 4 | 301 | 2024-06-01 18:00:00 | app_open |
| 5 | 301 | 2024-06-01 18:31:00 | fare_check |
| 6 | 302 | 2024-06-01 10:00:00 | app_open |
| 7 | 302 | 2024-06-01 10:05:00 | booking |
| 8 | 302 | 2024-06-01 10:06:00 | payment |
| 9 | 303 | 2024-06-01 11:00:00 | search |
| 10 | 303 | 2024-06-01 13:00:00 | search |
Output
| rider_id | sessions | booking_sessions | conversion_pct |
|---|---|---|---|
| 302 | 1 | 1 | 100 |
| 301 | 3 | 1 | 33.33 |
| 303 | 2 | 0 | 0 |
How to solve Rider App Sessions and Booking Conversion
Sessionisation is gaps-and-islands on time. Step one flags session starts: with LAG(event_at) over the rider's events in time order, an event starts a session when there is no previous event (LAG is NULL) or the gap is more than 1,800 seconds. Measuring in seconds and comparing with > keeps the exactly-30-minute case inside the session.
Step two turns flags into ids: a running SUM(new_session) per rider — with an explicit ROWS frame over a unique order — numbers sessions 1, 2, 3… and gives every event the number of the session it belongs to.
Step three collapses events into sessions; MAX(CASE WHEN event_type = 'booking' THEN 1 ELSE 0 END) is 1 for a session with any booking, however many it had. Step four counts sessions and converting sessions per rider and rounds the percentage.
The alternative defines a session start as an event with no event of the same rider in the 30 minutes before it, labels each event with its latest start at or before it, and counts distinct starts. It is correlated and quadratic per rider; the window chain sorts each rider's events once.
Reference solution (MySQL)
WITH gaps AS (
SELECT rider_id, event_at, event_type,
CASE WHEN LAG(event_at) OVER (PARTITION BY rider_id ORDER BY event_at) IS NULL
OR TIMESTAMPDIFF(SECOND, LAG(event_at) OVER (PARTITION BY rider_id ORDER BY event_at), event_at) > 1800
THEN 1 ELSE 0 END AS new_session
FROM AppEvent
), numbered AS (
SELECT rider_id, event_type,
SUM(new_session) OVER (PARTITION BY rider_id ORDER BY event_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM gaps
), per_session AS (
SELECT rider_id, session_no, MAX(CASE WHEN event_type = 'booking' THEN 1 ELSE 0 END) AS booked
FROM numbered
GROUP BY rider_id, session_no
)
SELECT rider_id, COUNT(*) AS sessions, SUM(booked) AS booking_sessions,
ROUND(100 * SUM(booked) / COUNT(*), 2) AS conversion_pct
FROM per_session
GROUP BY rider_id
ORDER BY conversion_pct DESC, rider_idAnother way
WITH starts AS (
SELECT e.rider_id, e.event_at FROM AppEvent e
WHERE NOT EXISTS (SELECT 1 FROM AppEvent p WHERE p.rider_id = e.rider_id AND p.event_at < e.event_at
AND p.event_at >= DATE_SUB(e.event_at, INTERVAL 30 MINUTE))
), tagged AS (
SELECT e.rider_id, e.event_type,
(SELECT MAX(s.event_at) FROM starts s WHERE s.rider_id = e.rider_id AND s.event_at <= e.event_at) AS session_start
FROM AppEvent e
)
SELECT rider_id, COUNT(DISTINCT session_start) AS sessions,
COUNT(DISTINCT CASE WHEN event_type = 'booking' THEN session_start END) AS booking_sessions,
ROUND(100 * COUNT(DISTINCT CASE WHEN event_type = 'booking' THEN session_start END) / COUNT(DISTINCT session_start), 2) AS conversion_pct
FROM tagged
GROUP BY rider_id
ORDER BY conversion_pct DESC, rider_id← Each Driver's Longest Streak of Active Days · The Day Each Driver Hit the Monthly ₹10,000 Target →