Shopping Sessions Abandoned After Add to Cart — SQL Problem
The app logs every view, add_to_cart and checkout a user makes.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #116
Problem statement
The app logs every view, add_to_cart and checkout a user makes. Events of one user belong to the same session until a gap of more than 30 minutes (1,800 seconds) between consecutive events — a gap of exactly 30 minutes keeps the session going. Order a user's events by event_time, then event_id.
Return the sessions that contain at least one add_to_cart but no checkout, with the columns user_id, session_start (first event time), session_end (last event time) and events (events in the session), ordered by user_id, then session_start.
Tables
Table: app_events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_time | datetime |
| event_type | enum(view, add_to_cart, checkout) |
Primary key: event_id.
One row per tracked event from the shopping app.
Examples
Example 1
app_events
| event_id | user_id | event_time | event_type |
|---|---|---|---|
| 1 | 7 | 2024-11-01 10:00:00 | view |
| 2 | 7 | 2024-11-01 10:05:00 | add_to_cart |
| 3 | 7 | 2024-11-01 10:35:00 | view |
| 4 | 7 | 2024-11-01 11:20:00 | view |
| 5 | 7 | 2024-11-01 11:22:00 | add_to_cart |
| 6 | 7 | 2024-11-01 11:30:00 | checkout |
| 7 | 8 | 2024-11-01 09:00:00 | add_to_cart |
| 8 | 8 | 2024-11-01 09:30:01 | add_to_cart |
| 9 | 8 | 2024-11-01 09:31:00 | view |
| 10 | 9 | 2024-11-01 20:00:00 | view |
Output
| user_id | session_start | session_end | events |
|---|---|---|---|
| 7 | 2024-11-01 10:00:00 | 2024-11-01 10:35:00 | 3 |
| 8 | 2024-11-01 09:00:00 | 2024-11-01 09:00:00 | 1 |
| 8 | 2024-11-01 09:30:01 | 2024-11-01 09:31:00 | 2 |
How to solve Shopping Sessions Abandoned After Add to Cart
This is sessionisation, a gaps-and-islands problem on time. Step one: for every event, look at the user's previous event with LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id). The event starts a new session if there is no previous event or the gap exceeds 1,800 seconds. Measure in seconds: TIMESTAMPDIFF(MINUTE, …) truncates, so 30 minutes 1 second would read as 30 and wrongly continue the session — user 8 in the example has exactly that gap.
Step two: a running SUM of the 0/1 start flags, with an explicit ROWS frame over a unique order, numbers the sessions 1, 2, 3, … per user. Step three: group by user and session number; MIN and MAX give the session's bounds, and two conditional sums in HAVING keep sessions with an add-to-cart and no checkout. User 7's first session (10:00–10:35, the 30-minute gap counts as one session) is abandoned; the second ends in a checkout.
The alternative finds session starts directly — events with no earlier event of the user within 30 minutes — and assigns each event the latest start at or before it. It avoids window functions but is quadratic per user; the window version is one sort.
Reference solution (MySQL)
WITH gaps AS (
SELECT user_id, event_id, event_time, event_type,
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS prev_time
FROM app_events
), flagged AS (
SELECT user_id, event_id, event_time, event_type,
CASE WHEN prev_time IS NULL OR TIMESTAMPDIFF(SECOND, prev_time, event_time) > 1800 THEN 1 ELSE 0 END AS new_session
FROM gaps
), sessions AS (
SELECT user_id, event_time, event_type,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY event_time, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM flagged
)
SELECT user_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end, COUNT(*) AS events
FROM sessions
GROUP BY user_id, session_no
HAVING SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN event_type = 'checkout' THEN 1 ELSE 0 END) = 0
ORDER BY user_id, session_startAnother way
WITH starts AS (
SELECT e.user_id, e.event_time AS start_time
FROM app_events e
WHERE NOT EXISTS (
SELECT 1 FROM app_events p
WHERE p.user_id = e.user_id
AND (p.event_time < e.event_time OR (p.event_time = e.event_time AND p.event_id < e.event_id))
AND p.event_time >= DATE_SUB(e.event_time, INTERVAL 1800 SECOND)
)
), tagged AS (
SELECT e.user_id, e.event_time, e.event_type,
(SELECT MAX(s.start_time) FROM starts s WHERE s.user_id = e.user_id AND s.start_time <= e.event_time) AS session_start
FROM app_events e
)
SELECT user_id, session_start, MAX(event_time) AS session_end, COUNT(*) AS events
FROM tagged
GROUP BY user_id, session_start
HAVING MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) = 1 AND MAX(CASE WHEN event_type = 'checkout' THEN 1 ELSE 0 END) = 0
ORDER BY user_id, session_start← Wishlisted Products That Are Now Cheaper · Monthly Buyer Cohort Retention →