Ad-Click Sessions per Shopper

Hard
293.

Ad-Click Sessions per Shopper

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

ColumnType
click_idint
user_idint
campaign_idint
clicked_atdatetime

Primary key: click_id.

One row per tracked ad click. Two clicks of one shopper can carry the same timestamp.

Example

The rows must come back in the order shown.

AdClick

click_iduser_idcampaign_idclicked_at
14132024-11-11 10:00:00
24132024-11-11 10:12:00
34152024-11-11 10:42:00
44152024-11-11 11:12:01
54232024-11-11 09:00:00
64232024-11-11 09:00:00
74242024-11-11 13:30:00
84352024-11-11 23:50:00
94352024-11-12 00:15:00

Output

user_idsessionslongest_session_clicks
4123
4222
4312
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.

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

ColumnType
click_idint
user_idint
campaign_idint
clicked_atdatetime

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_iduser_idcampaign_idclicked_at
14132024-11-11 10:00:00
24132024-11-11 10:12:00
34152024-11-11 10:42:00
44152024-11-11 11:12:01
54232024-11-11 09:00:00
64232024-11-11 09:00:00
74242024-11-11 13:30:00
84352024-11-11 23:50:00
94352024-11-12 00:15:00

Output

user_idsessionslongest_session_clicks
4123
4222
4312

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_id

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