SQL Event Modeling — Applied Attribution Analysis

QUESTION 6

Sessionization — Get the previous event time with LAG and assign session IDs with a conditional cumulative SUM over gaps longer than 30 minutes

LAGSUM OVERSessionizationWindow Functions

Background ▼

One of the most common preprocessing tasks in event modeling is sessionization. It groups consecutive events from the same user into sessions, using a fixed period of inactivity (30 minutes, for example) as the boundary. Get the preceding event time with the window function LAG, then cumulatively sum a 0/1 gap flag to assign session IDs.

-- Get the value one row earlier within the same PARTITION
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

-- Assign session IDs with a cumulative SUM (cumulative boundary flags = session number)
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time)

The three-stage sessionization pattern: (1) get the previous time with LAG → (2) turn the gap test into a 0/1 flag → (3) convert it to a session ID with a cumulative SUM. This three-CTE pattern is the fundamental workhorse of practical event analysis, and it is the starting point for session-length, page-transition, and bounce-rate analysis.

Problem ▼

From the user_events table, start a new session when the gap from the same user's previous event is longer than 30 minutes, then aggregate the session count, total event count, and average events per session for each user. Return user_id, session_count, total_events, avg_events_per_session (one decimal place), ordered by user_id ascending.

Table used

► user_events (9 rows)

user_id	event_time
1	2024-01-10 10:00
1	2024-01-10 10:05
1	2024-01-10 10:20
1	2024-01-10 12:00
1	2024-01-10 12:10
2	2024-01-10 09:00
2	2024-01-10 09:15
2	2024-01-10 11:30
3	2024-01-11 14:00

Expected Output ▼

user_id	session_count	total_events	avg_events_per_session
1	2	5	2.5
2	2	3	1.5
3	1	1	1.0

Model Answer ▼

WITH gapped AS (                                              -- ① Get the previous event time with LAG
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id
                          ORDER BY event_time) AS prev_time
  FROM   user_events
),
flagged AS (                                              -- ② Mark a gap longer than 30 minutes as a new session (1); otherwise continue (0)
  SELECT
    user_id, event_time, prev_time,
    CASE
      WHEN prev_time IS NULL THEN 1                          -- The first event starts a new session
      WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM   gapped
),
sessionized AS (                                          -- ③ Assign session IDs with a cumulative SUM
  SELECT
    user_id, event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id
                              ORDER BY event_time) AS session_id
  FROM   flagged
)
SELECT
  user_id,
  COUNT(DISTINCT session_id)                              AS session_count,
  COUNT(*)                                                  AS total_events,
  ROUND(COUNT(*)::numeric / COUNT(DISTINCT session_id), 1) AS avg_events_per_session
FROM   sessionized
GROUP BY user_id
ORDER BY user_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE gapped
  2. CTE flagged
  3. CTE sessionized
  4. Outer query
  */

Explanation (table transitions & key points) ▼

WITH gapped AS (
  SELECT user_id, event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM   user_events
),
flagged AS (
  SELECT user_id, event_time, prev_time,
    CASE WHEN prev_time IS NULL THEN 1
         WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS is_new_session
  FROM gapped
),
sessionized AS (
  SELECT user_id, event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
)
SELECT user_id,
  COUNT(DISTINCT session_id) AS session_count,
  COUNT(*) AS total_events,
  ROUND(COUNT(*)::numeric / COUNT(DISTINCT session_id),1) AS avg_events_per_session
FROM   sessionized
GROUP BY user_id
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM user_events (9 rows) ▼

FROM user_eventsRead 9 event-log rows for 3 users. Sessionization starts by evaluating gaps in chronological order within each user.

1 / 5

user_id	event_time
1	2024-01-10 10:00
1	2024-01-10 10:05
1	2024-01-10 10:20
1	2024-01-10 12:00
1	2024-01-10 12:10
2	2024-01-10 09:00
2	2024-01-10 09:15
2	2024-01-10 11:30
3	2024-01-11 14:00

9 rows read (3 users × chronological events)

LEARNING POINTS

LAG + cumulative SUM is the canonical sessionization pattern: Get the previous time with LAG → flag the gap with CASE → accumulate with SUM OVER. This three-stage pattern works with the same syntax in Postgres, BigQuery, and Snowflake. The same pattern also applies to detecting the last action before churn, counting consecutive events, and extracting screen-transition paths.

The roles of PARTITION BY and ORDER BY: PARTITION BY user_id evaluates the window function independently per user, ensuring that user1's LAG never incorporates user2's data. ORDER BY event_time puts each user's rows in chronological order; without it, LAG's value is unpredictable. For window functions, getting PARTITION and ORDER right is the same as getting the result right.

Time arithmetic with INTERVAL: In PostgreSQL, timestamp - timestamp = interval, which can be compared naturally with an INTERVAL literal. BigQuery requires a form such as TIMESTAMP_DIFF(t1, t2, MINUTE) > 30; the dialects differ, but the concept of testing a gap is universal.

ANTI-PATTERNS

Omitting PARTITION BY: If you write LAG(event_time) OVER (ORDER BY event_time), every row belongs to one partition, so user2's first row may use user1's final row as its prev_time. Sessionization across users is meaningless; PARTITION BY user_id is required.

Using DENSE_RANK instead of a cumulative SUM: Some implementations try to create session IDs with DENSE_RANK() OVER(PARTITION BY user_id, is_new_session ORDER BY event_time), but then all rows with is_new_session=0 collapse into the same group. A cumulative SUM is monotonic, so the cumulative flag can safely serve as the ID.

Practical column: The analytical world opened by sessionization

Once sessions are available, countless session-level metrics become possible: session length (last event time − first event time), page views within a session, the proportion of bounce sessions (one-event sessions), and the number of sessions reaching conversion. User-level CV rate and session-level CV rate have very different meanings and imply very different improvement strategies, so aggregating along both axes is fundamental to product analysis. This query also reproduces Google Analytics' session definition (30 minutes of inactivity by default) in SQL.

QUESTION 7

Attribution — Extract the first-touch channel and the touch immediately before purchase with FIRST_VALUE + LAG

FIRST_VALUELAGAttributionMarketing Analysis

Background ▼

Attribution is at the heart of marketing analysis. It assigns a conversion (a purchase) to the channels that contributed to it, using models such as first touch, last touch, and multi-touch. The basic technique is to attach an always-available first value and the preceding value to each user's behavioral history with window functions.

-- FIRST_VALUE: propagate the first row's value within a PARTITION to every row
FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY event_time)

-- LAG: get the previous row's value (used for the touch immediately before purchase)
LAG(channel) OVER (PARTITION BY user_id ORDER BY event_time)

Watch FIRST_VALUE's default frame: In PostgreSQL, specifying ORDER BY for a window function gives the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. FIRST_VALUE returns the first value in the frame, so with ORDER BY it propagates the partition's first value to every row, which is exactly what first-touch extraction needs.

Problem ▼

From the user_events table, extract for each purchase event the user's first-touch channel (first_touch) and the channel immediately before purchase (last_touch). Return user_id, first_touch, last_touch, purchase_time, ordered by user_id ascending. Exclude users with no purchase.

Table used

► user_events (9 rows)

user_id	event_time	event_type	channel
1	2024-01-10 10:00	view	google_ads
1	2024-01-10 11:00	view	organic
1	2024-01-10 12:00	click	facebook_ads
1	2024-01-10 13:00	purchase	direct
2	2024-01-11 09:00	view	organic
2	2024-01-11 10:00	purchase	email
3	2024-01-11 11:00	view	google_ads
3	2024-01-11 12:00	click	google_ads
4	2024-01-12 09:00	view	twitter_ads

Expected Output ▼

user_id	first_touch	last_touch	purchase_time
1	google_ads	facebook_ads	2024-01-10 13:00
2	organic	organic	2024-01-11 10:00

Model Answer ▼

WITH attributed AS (                                  -- Attach the first and previous channels to every event
  SELECT
    user_id,
    event_time,
    event_type,
    channel,
    FIRST_VALUE(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS first_touch,
    LAG(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS last_touch
  FROM   user_events
)
SELECT
  user_id,
  first_touch,
  last_touch,
  event_time AS purchase_time
FROM   attributed
WHERE  event_type = 'purchase'                              -- Keep purchase rows only
ORDER BY user_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE attributed
  2. Outer query
  */

Explanation (table transitions & key points) ▼

WITH attributed AS (
  SELECT user_id, event_time, event_type, channel,
    FIRST_VALUE(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS first_touch,
    LAG(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS last_touch
  FROM   user_events
)
SELECT user_id, first_touch, last_touch,
  event_time AS purchase_time
FROM   attributed
WHERE  event_type = 'purchase'
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM user_events (9 rows) ▼

FROM user_eventsRead 9 event-log rows (4 users × view/click/purchase × several channels). user3 and user4 have not reached purchase, but the window functions are evaluated over every row.

1 / 6

user_id	event_time	event_type	channel
1	10:00	view	google_ads
1	11:00	view	organic
1	12:00	click	facebook_ads
1	13:00	purchase	direct
2	09:00	view	organic
2	10:00	purchase	email
3	11:00	view	google_ads
3	12:00	click	google_ads
4	09:00	view	twitter_ads

9 rows read (4 users × multiple channels)

LEARNING POINTS

FIRST_VALUE and LAG have different jobs: FIRST_VALUE propagates the first value in the partition (which remains constant) to every row. LAG returns the previous row's value (a relative reference). The first touch maps naturally to the former, and the last touch immediately before purchase to the latter. Compute both in one CTE, then filter to purchase rows for a one-pass solution.

Why keep the window functions and WHERE in separate CTE stages: SQL's logical order is FROM → WHERE → GROUP BY → SELECT, and window functions are evaluated at the SELECT stage. If you filter to purchase rows first, LAG can no longer see view/click rows and instead sees the next purchase; separating the window calculation from WHERE is the essential pattern.

Is LAG(channel) always sufficient for last touch: Some cases require the last touch within the same session. For long-lived lead attribution, whether a first touch 30 days ago or the first touch of the latest session should count as “first” is a business definition. Narrowing the FIRST_VALUE frame to RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW can express “first touch in the last 7 days.”

ANTI-PATTERNS

Filter with WHERE before calculating the window function: If you put WHERE event_type='purchase' inside the CTE, the view and click rows disappear. LAG then has no preceding touch and returns NULL for the first purchase, or treats the previous purchase as the preceding row for a later purchase. Remember that window functions are evaluated after WHERE.

Assuming FIRST_VALUE is safe without a frame order: Omitting ORDER BY as in FIRST_VALUE(channel) OVER (PARTITION BY user_id) leaves the frame order undefined and makes the “first row” arbitrary. Always include ORDER BY with FIRST_VALUE. LAST_VALUE is even more subtle: because its default frame ends at CURRENT ROW, explicitly use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when needed.

Practical column: Extending to multi-touch attribution

First- and previous-touch attribution with FIRST_VALUE/LAG is called a single-touch model. It is simple, but its weakness is that intermediate touches receive no credit. In practice, more advanced multi-touch models (linear, time-decay, U-shaped, and others) are used. For example, a linear model that assigns equal credit to every touch can be implemented by finding the total number of touches N in a CTE and calculating contribution = 1/N. Combining COUNT(*) OVER(PARTITION BY user_id) with window functions lets SQL alone distribute credit across every touch leading to a purchase. Combined with Q6 sessionization, this can become a practical pipeline for session-level U-shaped attribution.

QUESTION 8

Time Between Funnel Steps — Calculate average conversion time with LAG + EXTRACT(EPOCH)

LAGEXTRACT EPOCHFunnel AnalysisElapsed Time

Background ▼

The basic set covered funnel reach and order validation, but in practice, time between steps is also an important measure of conversion quality. If the average view → click time is too long, users may lack decision-making information; if click → purchase takes too long, the checkout flow may have friction.

-- Get the time difference from the previous event in seconds
EXTRACT(EPOCH FROM (event_time - prev_event_time))

-- Divide by 60 for minutes, then ROUND to one decimal place
ROUND(AVG(...) / 60, 1)

EXTRACT(EPOCH FROM interval) converts an interval to seconds: timestamp - timestamp returns an interval, and extracting EPOCH gives a numeric value in seconds. This serves the same role as MySQL's TIMESTAMPDIFF(SECOND, t1, t2) and BigQuery's TIMESTAMP_DIFF(t2, t1, SECOND). Convert seconds with /60 → minutes or /3600 → hours.

Problem ▼

From the user_events table, calculate the elapsed time for each consecutive funnel event (view → click and click → purchase), then output the count and average minutes for each transition type. Return transition, event_count, avg_minutes (one decimal place), in view → click and click → purchase order.

Table used

► user_events (8 rows)

user_id	event_type	event_time
1	view	2024-01-10 10:00
1	click	2024-01-10 10:10
1	purchase	2024-01-10 11:00
2	view	2024-01-11 09:00
2	click	2024-01-11 09:30
3	view	2024-01-11 14:00
3	click	2024-01-11 14:05
3	purchase	2024-01-11 14:35

Expected Output ▼

transition	event_count	avg_minutes
view -> click	3	15.0
click -> purchase	2	40.0

Model Answer ▼

WITH ordered_events AS (                              -- Get the previous event type and time with window functions
  SELECT
    user_id,
    event_type,
    event_time,
    LAG(event_type) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS prev_event_type,
    LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS prev_event_time
  FROM   user_events
)
SELECT
  prev_event_type || ' -> ' || event_type AS transition,
  COUNT(*)                                                   AS event_count,
  ROUND(
    AVG(EXTRACT(EPOCH FROM (event_time - prev_event_time))) / 60
  , 1)                                                       AS avg_minutes
FROM   ordered_events
WHERE  (prev_event_type = 'view'     AND event_type = 'click')
    OR (prev_event_type = 'click'    AND event_type = 'purchase')
GROUP BY prev_event_type, event_type
ORDER BY CASE prev_event_type WHEN 'view' THEN 1 ELSE 2 END;

/*
  Execution order (logical SQL evaluation order):
  1. CTE ordered_events
  2. Outer query
  */

Explanation (table transitions & key points) ▼

WITH ordered_events AS (
  SELECT user_id, event_type, event_time,
    LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
  FROM   user_events
)
SELECT prev_event_type || ' -> ' || event_type AS transition,
  COUNT(*) AS event_count,
  ROUND(AVG(EXTRACT(EPOCH FROM (event_time - prev_event_time))) / 60, 1) AS avg_minutes
FROM   ordered_events
WHERE (prev_event_type = 'view' AND event_type = 'click')
   OR (prev_event_type = 'click' AND event_type = 'purchase')
GROUP BY prev_event_type, event_type
ORDER BY CASE prev_event_type WHEN 'view' THEN 1 ELSE 2 END;

LEGEND ▼

Rows read / loaded

① FROM user_events (8 rows) ▼

FROM user_eventsRead funnel events for 3 users. The goal is to calculate how many minutes each user takes to move between steps.

1 / 5

user_id	event_type	event_time
1	view	10:00
1	click	10:10
1	purchase	11:00
2	view	09:00
2	click	09:30
3	view	14:00
3	click	14:05
3	purchase	14:35

8 rows read (3 users × funnel events)

LEARNING POINTS

Convert to seconds with EXTRACT(EPOCH FROM interval): PostgreSQL's timestamp - timestamp returns an interval, but numeric conversion is needed for straightforward use with AVG and ROUND. EXTRACT(EPOCH FROM ...) converts the interval to seconds as DOUBLE PRECISION, making it easy to use with AVG, SUM, and other aggregates.

Know when to inspect the distribution, not only the average: Averages are sensitive to outliers and can be pulled by extremely long elapsed times, such as a user returning days later. In practice, use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...) for the median and PERCENTILE_CONT(0.95) for the 95th percentile. A large gap between the mean and median is a sign to investigate a skewed distribution.

Generate transition strings and control their order: prev_event_type || ' -> ' || event_type creates a readable transition label. Alphabetical order puts “click -> purchase” before “view -> click”, so inserting a CASE in ORDER BY is a practical way to restore funnel order.

ANTI-PATTERNS

Pass an interval directly to AVG: PostgreSQL can return an interval from AVG(t2 - t1), but combining it with ROUND or converting to another unit (minutes or hours) becomes awkward. Converting to seconds with EXTRACT(EPOCH FROM ...) keeps the logic consistent across RDBMSs.

Include transitions across sessions: If a user viewed something 30 days ago and clicked today, LAG quite correctly returns a 30-day gap, but that is not a meaningful step time. In practice, apply an upper bound such as only transitions in the same session or within 24 hours. Combining this with Q6 sessionization and adding WHERE session_id = prev_session_id is the standard approach.

Practical column: Elapsed-time metrics guide UX improvement

A long view → click time may mean that users are carefully considering a product, or that they cannot find a decisive reason to act and are hesitating. Compare distributions by segment, especially between users who eventually purchase and users who drop out. A pattern such as “purchaser median = 2 minutes, dropout median = 15 minutes” directly suggests the insight that hesitant users churn. A long click → purchase time may also point to checkout UI or shipping-option problems and help prioritize improvements. Combined with Q6 sessionization and Q7 attribution, this supports multidimensional analysis such as elapsed-time distributions by first-touch channel and funnel step.

QUESTION 9

Recursive CTE — Generate a date dimension and zero-fill a DAU time series with LEFT JOIN

WITH RECURSIVELEFT JOINTime-Series AnalysisRecursive CTE

Background ▼

The biggest trap when visualizing a DAU (Daily Active Users) time series is that days with no events disappear from the result. A straightforward GROUP BY date produces no row for a day with zero activity, so the graph is not continuous and can give a misleading impression. Generate a date dimension with a recursive CTE to prevent this.

WITH RECURSIVE date_series(d) AS (
  SELECT MIN(event_time::date) FROM user_events    -- Anchor
  UNION ALL
  SELECT d + 1                                  -- Recursive step: generate a row one day after each accumulated row
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)
)

A recursive CTE is a three-part set: anchor, recursive member, and termination condition: (1) the anchor is the first row, (2) the recursive member generates rows added to the accumulated result on each iteration, and (3) the termination condition in WHERE stops recursion. The recursive member can reference the accumulated result (itself) and continues generating the next row until the condition becomes false. Here, recursion stops after reaching the date of the last event.

Problem ▼

From the user_events table, output every date from the first event date through the last event date and calculate the DAU (number of active users) for each date. Output DAU=0 for dates with no events. Return event_date, dau, ordered by event_date ascending.

Table used

► user_events (5 rows)

user_id	event_time
1	2024-01-10
2	2024-01-10
1	2024-01-12
3	2024-01-12
1	2024-01-14

Expected Output ▼

event_date	dau
2024-01-10	2
2024-01-11	0
2024-01-12	2
2024-01-13	0
2024-01-14	1

Model Answer ▼

WITH RECURSIVE date_series(d) AS (                  -- ① Generate a continuous date series with a recursive CTE
  SELECT MIN(event_time::date)                              -- Anchor: the first date
  FROM   user_events
  UNION ALL
  SELECT d + 1                                              -- Recursive member: add one day to the previous accumulated result
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)  -- Termination condition: through the last date
)
SELECT
  ds.d                                  AS event_date,
  COUNT(DISTINCT e.user_id)             AS dau          -- COUNT(DISTINCT) ignores NULL → becomes 0
FROM       date_series ds
LEFT JOIN  user_events e
  ON ds.d = e.event_time::date                            -- Join on matching dates
GROUP BY   ds.d
ORDER BY   ds.d;

/*
  Execution order (logical SQL evaluation order):
  1. WITH RECURSIVE date_series  → recursively generate dates one day at a time
  2. LEFT JOIN user_events        → join events to each date (NULL when absent)
  3. GROUP BY ds.d                → count DISTINCT user_id for each date
  */

Explanation (table transitions & key points) ▼

WITH RECURSIVE date_series(d) AS (
  SELECT MIN(event_time::date)
  FROM   user_events
  UNION ALL
  SELECT d + 1
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)
)
SELECT ds.d AS event_date,
  COUNT(DISTINCT e.user_id) AS dau
FROM       date_series ds
LEFT JOIN  user_events e
  ON ds.d = e.event_time::date
GROUP BY ds.d
ORDER BY ds.d;

LEGEND ▼

① Anchor: SELECT MIN(event_time::date) → add one row to the accumulated result ▼

The initial date_series row is createdMIN(event_time::date) returns the first date (2024-01-10), which is inserted as the anchor at the start of the accumulated result. This is where recursion begins.

1 / 6

Iteration	Accumulated result (date_series)	Row added this time
Anchor	2024-01-10	2024-01-10 ← initial row

Accumulated result: 1 row (after anchor)

LEARNING POINTS

The three elements of a recursive CTE: (1) anchor: generate the first row (SELECT MIN(...)), (2) recursive member: generate the next row from the row added in the previous step (SELECT d+1 FROM date_series), and (3) termination condition: continue iterating until WHERE is false. A recursive CTE cannot work if any of the three is missing, so always account for all of them.

Date dimension × LEFT JOIN is the standard zero-filling pattern: Put a dimension table containing consecutive dates on the left and LEFT JOIN the fact table (events) on the right. Days with zero events remain as rows instead of disappearing. COUNT(DISTINCT) ignores NULL and naturally outputs 0. This is a core data-shaping step before sending data to BI tools such as Tableau or Looker.

PostgreSQL has the simpler generate_series: PostgreSQL can create the dates in one line with SELECT generate_series(min_date, max_date, '1 day'::interval)::date. Recursive CTEs shine for their intended uses: creating date series in databases without generate_series, such as BigQuery or MySQL, and performing real graph traversal (finding ancestors and descendants). Ideally, know both standard SQL recursive CTEs and dialect-specific generate_series.

ANTI-PATTERNS

Forget the termination condition (infinite loop): Without a WHERE clause in the recursive member, d keeps increasing by 1 forever. PostgreSQL stops with an error such as recursion depth exceeds 100, but test locally before running the query against a production database.

Use UNION instead of UNION ALL: UNION removes duplicates, so comparison cost can grow to O(N²) as the accumulated result grows. Recursive CTEs should use UNION ALL because rows generated in each iteration are assumed to be unique. If INNER JOIN or another CTE introduces duplicates, use DISTINCT inside the recursive SELECT or revisit the logic.

GROUP BY without a date dimension makes zero-event days disappear: Aggregating only with SELECT event_time::date, COUNT(DISTINCT user_id) FROM user_events GROUP BY event_time::date omits 1/11 and 1/13. The time-series line then breaks unnaturally and the average-DAU denominator can be wrong. Always guarantee time-series continuity with a date dimension × LEFT JOIN.

Practical column: The real value of recursive CTEs is hierarchical and graph traversal

Date-dimension generation is an introductory use of recursive CTEs; their real strength is traversing hierarchies. Examples include walking an org chart from employee to manager to CEO, expanding ancestor and descendant categories in a product tree, and following social-network relationships through N degrees of friends-of-friends. Exploration that follows linked rows as it expands is naturally expressed with recursive CTEs. Add the CYCLE clause for infinite-loop detection and the SEARCH clause for traversal-order control, and substantial graph analysis can remain in SQL. The mental model learned here—anchor + recursion + termination condition—transfers directly to more complex recursive problems.

QUESTION 10

Consecutive Login Days — Find the longest streak by grouping on the date − ROW_NUMBER difference with the Gaps and Islands pattern

ROW_NUMBERGaps & IslandsStreak AnalysisRetention

Background ▼

Detecting consecutive blocks (streaks), such as consecutive login or purchase days, is solved with the classic Gaps and Islands pattern. Assign ROW_NUMBER() to each row and calculate the curious difference date − row number: the value stays the same within a consecutive block and changes when a gap appears.

-- The magic key that identifies an island: date - row_number(day)
login_date - ROW_NUMBER() OVER (
  PARTITION BY user_id ORDER BY login_date
) * INTERVAL '1 day' AS streak_group

Why date − row_number reveals islands: In consecutive dates, both the date and row_number increase by one, so their difference does not change. When a gap appears, only the date jumps while row_number does not, so the difference increases. Rows with the same difference belong to the same island, or consecutive block.

Problem ▼

From the user_logins table, calculate each user's longest consecutive login streak (longest_streak). Return user_id, longest_streak, ordered by user_id ascending.

Table used

► user_logins (10 rows)

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-03
1	2024-01-05
1	2024-01-06
2	2024-01-10
3	2024-01-15
3	2024-01-16
3	2024-01-17
3	2024-01-18

Expected Output ▼

user_id	longest_streak
1	3
2	1
3	4

Model Answer ▼

WITH numbered AS (                                       -- ① Assign ROW_NUMBER per user
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    )                                                       AS rn
  FROM   user_logins
),
grouped AS (                                          -- ② Island key (streak_group) = date - rn days
  SELECT
    user_id,
    login_date,
    login_date - rn * INTERVAL '1 day'                       AS streak_group
  FROM   numbered
),
streaks AS (                                          -- ③ Aggregate the length of each island
  SELECT
    user_id,
    streak_group,
    COUNT(*)                                                AS streak_length
  FROM   grouped
  GROUP BY user_id, streak_group
)
SELECT
  user_id,
  MAX(streak_length)                                       AS longest_streak
FROM   streaks
GROUP BY user_id
ORDER BY user_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE numbered
  2. CTE grouped
  3. CTE streaks
  4. Outer query
  */

Explanation (table transitions & key points) ▼

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM   user_logins
),
grouped AS (
  SELECT user_id, login_date,
    login_date - rn * INTERVAL '1 day' AS streak_group
  FROM numbered
),
streaks AS (
  SELECT user_id, streak_group,
    COUNT(*) AS streak_length
  FROM grouped
  GROUP BY user_id, streak_group
)
SELECT user_id,
  MAX(streak_length) AS longest_streak
FROM   streaks
GROUP BY user_id
ORDER BY user_id;

LEGEND ▼

Rows read / loaded

① FROM user_logins (10 rows) ▼

FROM user_loginsRead the login history of 3 users. user1 mixes consecutive and non-consecutive days, making it a typical longest-streak case.

1 / 5

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-03
1	2024-01-05
1	2024-01-06
2	2024-01-10
3	2024-01-15
3	2024-01-16
3	2024-01-17
3	2024-01-18

10 rows read (3 users × login history)

LEARNING POINTS

“Date − ROW_NUMBER” is the Holy Grail of Gaps and Islands: Both values increase by one on consecutive dates, so the difference remains constant; when there is a gap, only the date jumps and the value changes. This idea applies to every consecutive-detection problem, including consecutive purchases, wins, and attendance, and is one of the most reusable window-function patterns.

Why RANK() and DENSE_RANK() do not work here: If a user logs in more than once on the same day, ORDER BY is no longer unique. RANK creates gaps, while DENSE_RANK gives the same value to multiple rows, causing the date − rn difference to vary within an island. ROW_NUMBER supplies a forced sequence with no ties, so it is the right choice for Gaps and Islands. If there are multiple logins on one day, first deduplicate with SELECT DISTINCT user_id, login_date.

Separate intent with three CTEs: Split the query into numbered (sequence) → grouped (island key) → streaks (island length) → final aggregation (maximum). Each stage becomes easier to understand, debug, tune, and extend. To find the currently active streak rather than the longest one, change the final WHERE to something like streak_group = (today - streak_length days).

ANTI-PATTERNS

An alternative that uses LAG and a cumulative group number: A solution such as SUM(CASE WHEN login_date - LAG(login_date) > 1 THEN 1 ELSE 0 END) OVER(...) needs more CTE stages and becomes verbose. “Date − row_number” creates the island key in one line and is more readable. Know both approaches and choose appropriately.

Ignore multiple logins on the same day: This question assumes one row per day in user_logins, but a real event table (user_events) may contain multiple events on one day. Assigning ROW_NUMBER directly gives multiple rn values on the same date, so date − rn is no longer constant within the island. Always deduplicate first with SELECT DISTINCT user_id, event_time::date AS login_date FROM user_events before applying Gaps and Islands.

Practical column: What streak analysis says about habit formation

Metrics such as “7 consecutive logins” and “30 consecutive days of use” matter in social, gaming, and learning apps because a streak is a strong proxy for users who have successfully formed a habit. Duolingo's Streak badge and Snapchat's Snapstreak are known as powerful mechanisms that bring users back to an app. With this SQL, correlation analyses such as “users with a longest streak of at least 7 days have three times the one-year retention” become possible and can support the design of product initiatives. Combined with Q6 sessionization, Q7 attribution, and Q9 date dimensions, the five questions can be extended into a multidimensional pipeline such as maximum weekly streak distributions by first-touch channel.