App Sessions That End in a Hotel Booking — SQL Problem
The product team measures how often a visit to the hotel app converts.
- Difficulty: Hard
- Topics: Window Functions, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #546
Problem statement
The product team measures how often a visit to the hotel app converts. Each user's events are split into sessions: a new session starts with a user's first event and with any event that comes more than 30 minutes after that user's previous event (a gap of exactly 30 minutes stays in the same session).
For every user with at least one event, return user_id, sessions, booking_sessions (sessions containing at least one book event) and conversion_pct = 100 × booking_sessions / sessions, rounded to 2 decimals. Return the rows in any order.
Tables
Table: AppEvent
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_time | datetime |
| event_type | enum(search, view_hotel, start_checkout, book) |
Primary key: event_id.
One row per tracked event. Two events of the same user never share an event_time.
Examples
Example 1
AppEvent
| event_id | user_id | event_time | event_type |
|---|---|---|---|
| 1 | 7 | 2024-09-14 10:00:00 | search |
| 2 | 7 | 2024-09-14 10:12:00 | view_hotel |
| 3 | 7 | 2024-09-14 10:42:00 | book |
| 4 | 7 | 2024-09-14 11:20:00 | search |
| 5 | 7 | 2024-09-15 09:05:00 | search |
| 6 | 7 | 2024-09-15 09:20:00 | book |
| 7 | 9 | 2024-09-14 22:50:00 | search |
| 8 | 9 | 2024-09-14 23:15:00 | start_checkout |
| 9 | 9 | 2024-09-14 23:46:00 | book |
Output
| user_id | sessions | booking_sessions | conversion_pct |
|---|---|---|---|
| 7 | 3 | 2 | 66.67 |
| 9 | 2 | 1 | 50 |
How to solve App Sessions That End in a Hotel Booking
Sessionisation turns a stream of events into visits. Within each user, order events by time and look at the previous one with LAG(event_time). An event starts a new session if there is no previous event or if the gap, TIMESTAMPDIFF(SECOND, previous, current), is more than 1800 seconds. The gap of exactly 30 minutes fails the strict > and stays in the session, as the statement requires; measuring in seconds avoids any question of how minutes are truncated.
A running SUM of those 0/1 start marks — with an explicit ROWS frame over the user's unique event times — gives every event its session number. Grouping by (user_id, session_no) collapses each session to one row, and MAX(CASE WHEN event_type = 'book' THEN 1 ELSE 0 END) flags whether it contained a booking; a session with two bookings still counts once. A final group per user counts sessions and booking sessions and rounds the ratio.
The alternative avoids windows: a session start is an event with no earlier event of the same user in the preceding 30 minutes (NOT EXISTS), each event belongs to the latest start at or before it, and COUNT(DISTINCT …) over the start times counts sessions. It is quadratic per user; the window plan is one sort.
Reference solution (MySQL)
WITH gaps AS (
SELECT user_id, event_time, event_type,
CASE
WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL THEN 1
WHEN TIMESTAMPDIFF(SECOND, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time), event_time) > 1800 THEN 1
ELSE 0
END AS new_session
FROM AppEvent
), numbered AS (
SELECT user_id, event_type,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM gaps
), per_session AS (
SELECT user_id, session_no, MAX(CASE WHEN event_type = 'book' THEN 1 ELSE 0 END) AS booked
FROM numbered
GROUP BY user_id, session_no
)
SELECT user_id, COUNT(*) AS sessions, SUM(booked) AS booking_sessions,
ROUND(100 * SUM(booked) / COUNT(*), 2) AS conversion_pct
FROM per_session
GROUP BY user_idAnother way
WITH starts AS (
SELECT e.user_id, e.event_time FROM AppEvent e
WHERE NOT EXISTS (SELECT 1 FROM AppEvent p WHERE p.user_id = e.user_id AND p.event_time < e.event_time
AND p.event_time >= DATE_SUB(e.event_time, INTERVAL 30 MINUTE))
), tagged AS (
SELECT e.user_id, e.event_type,
(SELECT MAX(s.event_time) FROM starts s WHERE s.user_id = e.user_id AND s.event_time <= e.event_time) AS session_start
FROM AppEvent e
)
SELECT user_id, COUNT(DISTINCT session_start) AS sessions,
COUNT(DISTINCT CASE WHEN event_type = 'book' THEN session_start END) AS booking_sessions,
ROUND(100 * COUNT(DISTINCT CASE WHEN event_type = 'book' THEN session_start END) / COUNT(DISTINCT session_start), 2) AS conversion_pct
FROM tagged
GROUP BY user_id← Nightly Rooms Sold Calendar for the Holiday Season · Date Each Frequent Flyer Qualified for Gold →