SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization

SQL Window Functions•Permalink

Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Deduplication patterns

Deduplicate and keep the latest row

↗#

WITH ranked AS (
  SELECT t.*, 
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM users t
)
SELECT *
FROM ranked
WHERE rn = 1;

# Choose one row per business key.

Delete duplicates after ranking

↗#

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

# Keep the newest record and remove the rest.

## Gaps and islands

Flag a new session when the gap is too large

↗#

WITH flagged AS (
  SELECT user_id,
         event_time,
         CASE
           WHEN event_time - LAG(event_time) OVER (
             PARTITION BY user_id
             ORDER BY event_time
           ) > INTERVAL '30 minutes'
           OR LAG(event_time) OVER (
             PARTITION BY user_id
             ORDER BY event_time
           ) IS NULL
           THEN 1 ELSE 0
         END AS is_new_session
  FROM user_events
)
SELECT *
FROM flagged;

# Start a new session after 30 minutes of inactivity.

Assign session IDs with a running sum

↗#

WITH flagged AS (
  SELECT user_id,
         event_time,
         CASE
           WHEN event_time - LAG(event_time) OVER (
             PARTITION BY user_id
             ORDER BY event_time
           ) > INTERVAL '30 minutes'
           OR LAG(event_time) OVER (
             PARTITION BY user_id
             ORDER BY event_time
           ) IS NULL
           THEN 1 ELSE 0
         END AS is_new_session
  FROM user_events
)
SELECT user_id,
       event_time,
       SUM(is_new_session) OVER (
         PARTITION BY user_id
         ORDER BY event_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM flagged;

# Convert session boundary flags into stable groups.

Group consecutive dates into streaks

↗#

WITH numbered AS (
  SELECT user_id,
         activity_date,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY activity_date
         ) AS rn
  FROM daily_activity
), grouped AS (
  SELECT user_id,
         activity_date,
         activity_date - rn * INTERVAL '1 day' AS grp_key
  FROM numbered
)
SELECT user_id,
       MIN(activity_date) AS streak_start,
       MAX(activity_date) AS streak_end,
       COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, grp_key;

# Classic islands pattern using row-number arithmetic.

More in SQL Window Functions

SQL Window Functions: Performance, EXPLAIN, and Dialect Notes

How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.

SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis

Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.

SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics

Positional window functions for first, last, and nth values inside ordered partitions.

SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons

Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.

SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics

Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.

SQL Window Functions: Ranking, Top N per Group, and Percent Rank

Ranking functions such as ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST.

Recommended next

No recommendations yet.

Browse all sheets →