Shopping Sessions Abandoned After Add to Cart

Hard
116.

Shopping Sessions Abandoned After Add to Cart

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

ColumnTypeValues
event_idint
user_idint
event_timedatetime
event_typeenumview, add_to_cart, checkout

Primary key: event_id.

One row per tracked event from the shopping app.

Example

The rows must come back in the order shown.

app_events

event_iduser_idevent_timeevent_type
172024-11-01 10:00:00view
272024-11-01 10:05:00add_to_cart
372024-11-01 10:35:00view
472024-11-01 11:20:00view
572024-11-01 11:22:00add_to_cart
672024-11-01 11:30:00checkout
782024-11-01 09:00:00add_to_cart
882024-11-01 09:30:01add_to_cart
982024-11-01 09:31:00view
1092024-11-01 20:00:00view

Output

user_idsession_startsession_endevents
72024-11-01 10:00:002024-11-01 10:35:003
82024-11-01 09:00:002024-11-01 09:00:001
82024-11-01 09:30:012024-11-01 09:31:002
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.

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

ColumnType
event_idint
user_idint
event_timedatetime
event_typeenum(view, add_to_cart, checkout)

Primary key: event_id.

One row per tracked event from the shopping app.

Examples

Example 1

app_events

event_iduser_idevent_timeevent_type
172024-11-01 10:00:00view
272024-11-01 10:05:00add_to_cart
372024-11-01 10:35:00view
472024-11-01 11:20:00view
572024-11-01 11:22:00add_to_cart
672024-11-01 11:30:00checkout
782024-11-01 09:00:00add_to_cart
882024-11-01 09:30:01add_to_cart
982024-11-01 09:31:00view
1092024-11-01 20:00:00view

Output

user_idsession_startsession_endevents
72024-11-01 10:00:002024-11-01 10:35:003
82024-11-01 09:00:002024-11-01 09:00:001
82024-11-01 09:30:012024-11-01 09:31:002

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_start

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