Ad-Click Sessions per Shopper — SQL Window Functions Problem
The click-fraud team groups each shopper's ad clicks into sessions: a click starts a new session when it is the shopper's first click or comes more than 30…
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #293
Problem statement
The click-fraud team groups each shopper's ad clicks into sessions: a click starts a new session when it is the shopper's first click or comes more than 30 minutes (1,800 seconds) after that shopper's previous click; otherwise it continues the current session. A gap of exactly 30 minutes continues it.
Return user_id, sessions (the number of sessions) and longest_session_clicks (the most clicks in any one session) for every shopper with at least one click. Order by user_id.
Tables
Table: AdClick
| Column | Type |
|---|---|
| click_id | int |
| user_id | int |
| campaign_id | int |
| clicked_at | datetime |
Primary key: click_id.
One row per tracked ad click. Two clicks of one shopper can carry the same timestamp.
Examples
Example 1
AdClick
| click_id | user_id | campaign_id | clicked_at |
|---|---|---|---|
| 1 | 41 | 3 | 2024-11-11 10:00:00 |
| 2 | 41 | 3 | 2024-11-11 10:12:00 |
| 3 | 41 | 5 | 2024-11-11 10:42:00 |
| 4 | 41 | 5 | 2024-11-11 11:12:01 |
| 5 | 42 | 3 | 2024-11-11 09:00:00 |
| 6 | 42 | 3 | 2024-11-11 09:00:00 |
| 7 | 42 | 4 | 2024-11-11 13:30:00 |
| 8 | 43 | 5 | 2024-11-11 23:50:00 |
| 9 | 43 | 5 | 2024-11-12 00:15:00 |
Output
| user_id | sessions | longest_session_clicks |
|---|---|---|
| 41 | 2 | 3 |
| 42 | 2 | 2 |
| 43 | 1 | 2 |
How to solve Ad-Click Sessions per Shopper
Sessionisation in three steps. First, compare each click with the shopper's previous one: LAG(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at, click_id). The click starts a session when there is no previous click or when TIMESTAMPDIFF(SECOND, prev, clicked_at) > 1800; exactly 1,800 seconds continues the session, and two clicks at the same second have a gap of 0. Ordering by click_id as well makes the sequence unique, which matters for the next step.
Second, a running SUM of the start flags (with an explicit ROWS frame over the unique order) gives every click its session number. Third, group by (shopper, session) for each session's size, then by shopper for the count of sessions and the largest size. Sessions cross midnight naturally because the gap is measured on the timestamp, not the date.
The alternative defines a session start without windows — no earlier click of the shopper within the last 1,800 seconds — and numbers each click by counting the starts at or before it. That is quadratic per shopper; the window plan is one sort.
Reference solution (MySQL)
WITH g AS (
SELECT user_id, click_id, clicked_at,
CASE WHEN LAG(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at, click_id) IS NULL
OR TIMESTAMPDIFF(SECOND, LAG(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at, click_id), clicked_at) > 1800
THEN 1 ELSE 0 END AS is_start
FROM AdClick
), s AS (
SELECT user_id, click_id,
SUM(is_start) OVER (PARTITION BY user_id ORDER BY clicked_at, click_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM g
), sizes AS (
SELECT user_id, session_no, COUNT(*) AS clicks FROM s GROUP BY user_id, session_no
)
SELECT user_id, COUNT(*) AS sessions, MAX(clicks) AS longest_session_clicks
FROM sizes
GROUP BY user_id
ORDER BY user_idAnother way
WITH starts AS (
SELECT a.user_id, a.click_id, a.clicked_at FROM AdClick a
WHERE NOT EXISTS (
SELECT 1 FROM AdClick b WHERE b.user_id = a.user_id
AND (b.clicked_at < a.clicked_at OR (b.clicked_at = a.clicked_at AND b.click_id < a.click_id))
AND TIMESTAMPDIFF(SECOND, b.clicked_at, a.clicked_at) <= 1800)
), tagged AS (
SELECT c.user_id, c.click_id,
(SELECT COUNT(*) FROM starts s WHERE s.user_id = c.user_id
AND (s.clicked_at < c.clicked_at OR (s.clicked_at = c.clicked_at AND s.click_id <= c.click_id))) AS session_no
FROM AdClick c
)
SELECT user_id, COUNT(DISTINCT session_no) AS sessions,
MAX(n) AS longest_session_clicks
FROM (SELECT user_id, session_no, COUNT(*) OVER (PARTITION BY user_id, session_no) AS n FROM tagged) t
GROUP BY user_id ORDER BY user_id← Streaks of Three or More Overspent Days · Click to Sign-up to Purchase Funnel per Campaign →