App Sessions That End in a Hotel Booking

Hard
546.

App Sessions That End in a Hotel Booking

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

ColumnTypeValues
event_idint
user_idint
event_timedatetime
event_typeenumsearch, 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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AppEvent

event_iduser_idevent_timeevent_type
172024-09-14 10:00:00search
272024-09-14 10:12:00view_hotel
372024-09-14 10:42:00book
472024-09-14 11:20:00search
572024-09-15 09:05:00search
672024-09-15 09:20:00book
792024-09-14 22:50:00search
892024-09-14 23:15:00start_checkout
992024-09-14 23:46:00book

Output

user_idsessionsbooking_sessionsconversion_pct
73266.67
92150
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.

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

ColumnType
event_idint
user_idint
event_timedatetime
event_typeenum(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_iduser_idevent_timeevent_type
172024-09-14 10:00:00search
272024-09-14 10:12:00view_hotel
372024-09-14 10:42:00book
472024-09-14 11:20:00search
572024-09-15 09:05:00search
672024-09-15 09:20:00book
792024-09-14 22:50:00search
892024-09-14 23:15:00start_checkout
992024-09-14 23:46:00book

Output

user_idsessionsbooking_sessionsconversion_pct
73266.67
92150

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_id

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