Rider App Sessions and Booking Conversion

Hard
317.

Rider App Sessions and Booking Conversion

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

ColumnTypeValues
event_idint
rider_idint
event_atdatetime
event_typeenumapp_open, search, fare_check, booking, payment

Primary key: event_id.

No two events of one rider share a timestamp.

Example

The rows must come back in the order shown.

AppEvent

event_idrider_idevent_atevent_type
13012024-06-01 09:00:00app_open
23012024-06-01 09:02:00search
33012024-06-01 09:32:00booking
43012024-06-01 18:00:00app_open
53012024-06-01 18:31:00fare_check
63022024-06-01 10:00:00app_open
73022024-06-01 10:05:00booking
83022024-06-01 10:06:00payment
93032024-06-01 11:00:00search
103032024-06-01 13:00:00search

Output

rider_idsessionsbooking_sessionsconversion_pct
30211100
3013133.33
303200
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
rider_idint
event_atdatetime
event_typeenum(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_idrider_idevent_atevent_type
13012024-06-01 09:00:00app_open
23012024-06-01 09:02:00search
33012024-06-01 09:32:00booking
43012024-06-01 18:00:00app_open
53012024-06-01 18:31:00fare_check
63022024-06-01 10:00:00app_open
73022024-06-01 10:05:00booking
83022024-06-01 10:06:00payment
93032024-06-01 11:00:00search
103032024-06-01 13:00:00search

Output

rider_idsessionsbooking_sessionsconversion_pct
30211100
3013133.33
303200

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_id

Another 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 →