Sessionization with LAG and cumulative SUM

A new session starts when there's a long gap since the last visit. LAG finds each gap; a running SUM counts the long ones to number the sessions.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Grouping visits into sessions

WITH gaps AS (
  SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap
  FROM visits
)
SELECT customer_id, visit_date,
       SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY visit_date) + 1 AS session_no
FROM gaps

Result:

customer_id	visit_date	session_no
C101	2024-01-05	1
C101	2024-01-20	1
C101	2024-03-15	2
C101	2024-03-30	2
C102	2024-02-01	1
C102	2024-02-10	1

Step by step

LAG looks one row back, within each customer.

SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap
FROM visits;

More than 30 days apart starts a new session: flag it 1.

WITH gaps AS (
  SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap
  FROM visits
)
SELECT customer_id, visit_date, CASE WHEN gap > 30 THEN 1 ELSE 0 END AS new_session
FROM gaps;

A running SUM of the flags, plus 1, numbers each customer's sessions.

WITH gaps AS (
  SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap
  FROM visits
)
SELECT customer_id, visit_date,
       SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY visit_date) + 1 AS session_no
FROM gaps;

Practice

Try each one in your head first, then open the answer.

1. Show customer_id, visit_date and the days since that customer's previous visit, as gap.

SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap FROM visits

2. Show customer_id, visit_date and new_session: 1 when the gap since the previous visit is more than 30 days, otherwise 0.

WITH gaps AS (SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap FROM visits) SELECT customer_id, visit_date, CASE WHEN gap > 30 THEN 1 ELSE 0 END AS new_session FROM gaps

3. Number each customer's sessions as session_no (a new session after more than 30 days). Show customer_id, visit_date and session_no.

WITH gaps AS (SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap FROM visits) SELECT customer_id, visit_date, SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY visit_date) + 1 AS session_no FROM gaps

4. Show each customer_id and how many sessions they had, as sessions (a new session after more than 30 days).

WITH gaps AS (SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap FROM visits) SELECT customer_id, SUM(CASE WHEN gap > 30 THEN 1 ELSE 0 END) + 1 AS sessions FROM gaps GROUP BY customer_id

5. Number each customer's sessions as session_no, starting a new session after more than 14 days. Show customer_id, visit_date and session_no.

WITH gaps AS (SELECT customer_id, visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY customer_id ORDER BY visit_date) AS gap FROM visits) SELECT customer_id, visit_date, SUM(CASE WHEN gap > 14 THEN 1 ELSE 0 END) OVER (PARTITION BY customer_id ORDER BY visit_date) + 1 AS session_no FROM gaps

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed