TECH **SQL for Data Analysis: Time-Based Gaps and Islands Problem**

SQL for Data Analysis: Time-Based Gaps and Islands Problem

Hyper-Practical, Zero-Fluff Study Guide

1. What This Is & Why It Matters

What It Is

The Gaps and Islands problem is a classic SQL pattern where you need to: - Identify gaps (missing time periods in a sequence, e.g., "When was a user inactive?").
- Group contiguous events (islands, e.g., "How many consecutive days did a user log in?").

It’s time-based when your data has a timestamp (e.g., logins, sensor readings, stock prices, server uptime).

Why It Matters in Production

• User behavior analysis: "How many days in a row did a customer use our app before churning?"
• System monitoring: "When did our API have downtime gaps longer than 5 minutes?"
• Financial reporting: "Which days had no trades for a given stock?"
• Inventory tracking: "When did we run out of stock for more than 24 hours?"

Ignoring this problem?
- You’ll miscount active users (e.g., treating a 1-day gap as a "streak").
- Your dashboards will show false trends (e.g., "90% uptime" when it was really 50% with long outages).
- Your anomaly detection will miss critical gaps (e.g., a sensor failing silently for hours).

Real-world scenario:
You’re analyzing a SaaS product’s daily active users (DAU). A user logs in on Monday, skips Tuesday, and logs in again on Wednesday. Is this one 3-day streak or two separate 1-day streaks? The answer changes your retention metrics.

2. Core Concepts & Components

3. Step-by-Step Hands-On: Finding User Login Streaks

Prerequisites

• A SQL database (PostgreSQL, MySQL, BigQuery, Snowflake, etc.).
• A table with user login timestamps (example schema below).
• Basic familiarity with SELECT, JOIN, and window functions.

Example Table: user_logins

CREATE TABLE user_logins (
    user_id INT,
    login_time TIMESTAMP
);

-- Sample data (UTC)
INSERT INTO user_logins VALUES
(1, '2023-01-01 09:00:00'),
(1, '2023-01-02 08:30:00'),
(1, '2023-01-03 09:15:00'), -- 3-day streak
(1, '2023-01-05 10:00:00'), -- 1-day gap (Jan 4 missing)
(1, '2023-01-06 08:00:00'), -- 2-day streak
(2, '2023-01-01 12:00:00'),
(2, '2023-01-03 11:00:00'); -- 2-day gap (Jan 2 missing)

Goal

Find all login streaks (islands) for each user, with: - Start and end dates of each streak.
- Length of the streak (in days).

Step-by-Step Solution

Step 1: Assign a row number to each login

WITH numbered_logins AS (
    SELECT
        user_id,
        login_time,
        DATE(login_time) AS login_date, -- Extract date (ignore time)
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS row_num
    FROM user_logins
)
SELECT * FROM numbered_logins;

Output:
| user_id | login_time | login_date | row_num | |---------|---------------------|-------------|---------| | 1 | 2023-01-01 09:00:00 | 2023-01-01 | 1 | | 1 | 2023-01-02 08:30:00 | 2023-01-02 | 2 | | 1 | 2023-01-03 09:15:00 | 2023-01-03 | 3 | | 1 | 2023-01-05 10:00:00 | 2023-01-05 | 4 | | 1 | 2023-01-06 08:00:00 | 2023-01-06 | 5 | | 2 | 2023-01-01 12:00:00 | 2023-01-01 | 1 | | 2 | 2023-01-03 11:00:00 | 2023-01-03 | 2 |

Step 2: Calculate the "group ID" for each streak

WITH numbered_logins AS (
    SELECT
        user_id,
        login_time,
        DATE(login_time) AS login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS row_num
    FROM user_logins
),
grouped_logins AS (
    SELECT
        user_id,
        login_date,
        row_num,
        DATEADD(day, -row_num, login_date) AS group_id -- Magic trick!
    FROM numbered_logins
)
SELECT * FROM grouped_logins;

Output:
| user_id | login_date | row_num | group_id | |---------|-------------|---------|-------------| | 1 | 2023-01-01 | 1 | 2022-12-31 | | 1 | 2023-01-02 | 2 | 2022-12-31 | -- Same group_id = same streak | 1 | 2023-01-03 | 3 | 2022-12-31 | | 1 | 2023-01-05 | 4 | 2023-01-01 | -- New group_id = new streak | 1 | 2023-01-06 | 5 | 2023-01-01 | | 2 | 2023-01-01 | 1 | 2022-12-31 | | 2 | 2023-01-03 | 2 | 2023-01-01 |

Why this works:
- DATEADD(day, -row_num, login_date) subtracts the row number from the date.
- If dates are consecutive, the result is the same (same group_id).
- If there’s a gap, the result changes (new group_id).

Step 3: Aggregate to find streaks

WITH numbered_logins AS (
    SELECT
        user_id,
        login_time,
        DATE(login_time) AS login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS row_num
    FROM user_logins
),
grouped_logins AS (
    SELECT
        user_id,
        login_date,
        row_num,
        DATEADD(day, -row_num, login_date) AS group_id
    FROM numbered_logins
)
SELECT
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*) AS streak_length_days
FROM grouped_logins
GROUP BY user_id, group_id
ORDER BY user_id, streak_start;

Final Output:
| user_id | streak_start | streak_end | streak_length_days | |---------|--------------|--------------|--------------------| | 1 | 2023-01-01 | 2023-01-03 | 3 | | 1 | 2023-01-05 | 2023-01-06 | 2 | | 2 | 2023-01-01 | 2023-01-01 | 1 | | 2 | 2023-01-03 | 2023-01-03 | 1 |

Verification:
- User 1 has a 3-day streak (Jan 1–3) and a 2-day streak (Jan 5–6).
- User 2 has two 1-day streaks (Jan 1 and Jan 3).

4. ? Production-Ready Best Practices

Performance

• Index your timestamps: CREATE INDEX idx_user_logins_time ON user_logins(user_id, login_time).
• Use window functions (not self-joins) for large datasets.
• Partition by user/device to avoid mixing data.

Data Quality

• Handle duplicates: SELECT DISTINCT user_id, DATE(login_time) FROM user_logins.
• Timezone awareness: Store all timestamps in UTC and convert for reporting.
• Edge cases: What if a user logs in multiple times in a day? Decide if you count it as 1 day or multiple.

Maintainability

• Name CTEs clearly: WITH user_daily_logins AS (...) is better than WITH cte1 AS (...).
• Add comments: Explain the DATEADD(day, -row_num, login_date) trick.
• Modularize queries: Break complex logic into CTEs for debugging.

Observability

• Log query performance: If it runs slow, check for missing indexes.
• Alert on long gaps: "No logins for 24 hours" could mean an outage.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Find the longest streak of consecutive days where X happened."
2. Trick: They’ll give you data with duplicates or timezones to trip you up.

3. Solution: Use ROW_NUMBER() + DATEADD() trick.

4. "Identify all gaps longer than N days in a time series."

5. Trick: They might ask for gaps between events (e.g., "no logins for 3+ days").

6. Solution: Use LAG() to compare current and previous dates.

7. "Count the number of islands in a dataset."

8. Trick: They’ll include edge cases (e.g., single-day islands).
9. Solution: Group by group_id and count distinct groups.

Key Trap Distinctions

Example Exam Question

"Given a table of user logins, write a query to find all users who had a streak of 7+ consecutive days of activity in January 2023."

Answer:

WITH numbered_logins AS (
    SELECT
        user_id,
        DATE(login_time) AS login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time) AS row_num
    FROM user_logins
    WHERE login_time >= '2023-01-01' AND login_time < '2023-02-01'
),
grouped_logins AS (
    SELECT
        user_id,
        login_date,
        DATEADD(day, -row_num, login_date) AS group_id
    FROM numbered_logins
),
streaks AS (
    SELECT
        user_id,
        group_id,
        COUNT(*) AS streak_length
    FROM grouped_logins
    GROUP BY user_id, group_id
)
SELECT DISTINCT user_id
FROM streaks
WHERE streak_length >= 7;

7. ? Hands-On Challenge

Problem:
You have a table server_uptime with timestamps of when a server was online. Write a query to find all downtime gaps longer than 1 hour.

Sample Data:

CREATE TABLE server_uptime (
    event_time TIMESTAMP
);

INSERT INTO server_uptime VALUES
('2023-01-01 00:00:00'),
('2023-01-01 00:05:00'),
('2023-01-01 01:30:00'), -- 1.5-hour gap
('2023-01-01 01:35:00'),
('2023-01-01 02:00:00');

Solution:

WITH gaps AS (
    SELECT
        event_time,
        LEAD(event_time) OVER (ORDER BY event_time) AS next_event_time,
        TIMESTAMPDIFF(MINUTE, event_time, LEAD(event_time) OVER (ORDER BY event_time)) AS gap_minutes
    FROM server_uptime
)
SELECT
    event_time AS gap_start,
    next_event_time AS gap_end,
    gap_minutes
FROM gaps
WHERE gap_minutes > 60; -- Gaps longer than 1 hour

Why It Works:
- LEAD() gets the next event time.
- TIMESTAMPDIFF() calculates the gap in minutes.
- Filter for gaps > 60 minutes.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Window Functions Docs – Deep dive into LAG(), LEAD(), and ROW_NUMBER().
2. SQL Gaps and Islands Explained (Mode Analytics) – Interactive tutorial with real datasets.
3. LeetCode SQL Problems – Practice gaps/islands problems (e.g., "Consecutive Numbers").
4. "SQL for Mere Mortals" (Book) – Chapter 14 covers time-series analysis.