Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.
Window functions make cleanup and latest-row selection easy.
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;This pattern is safer and clearer than many self-join alternatives.
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
);Exact delete syntax can vary by database, but the ranking step is the core idea.
Detect streaks, contiguous runs, and session boundaries.
Start a new session after 30 minutes of inactivity.
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;This is the first step in sessionization. Interval syntax may differ by dialect.
Convert session boundary flags into stable groups.
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;A cumulative sum transforms boundary rows into session group numbers.
Classic islands pattern using row-number arithmetic.
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;A timeless trick for finding consecutive runs. Date arithmetic varies slightly by dialect.