Summary  
This chapter covers generating a continuous date series and using joins to detect missing intervals (gaps) and group contiguous periods of activity (islands) in time series data.

General domain of usage  
Retail sales analytics

Time series analysis is essential in analytical reporting, particularly when you need to identify patterns of activity and inactivity over a sequence of dates. One of the most practical challenges in time series data is detecting **gaps and islands**. These concepts help you find periods where events are missing (**gaps**) or group together continuous stretches of activity (**islands**). 



- **Gaps** are stretches of time where no records exist for an expected event (such as no sales on certain days);
- **Islands** are consecutive periods where events are present without interruption (such as a streak of daily sales or employee absences).

**Significance:**  
Detecting gaps and islands helps you answer business-critical questions:  
- Are there days with no sales activity that require investigation?;
- How long do continuous employee absences last, and are there patterns?;
- Did marketing campaigns run without interruption, or are there periods of inactivity?.

Understanding these periods is crucial for operational monitoring, compliance, and performance improvement.

Definition

-- Find all dates in January 2024 where no sales were recorded in any store
WITH calendar AS (
  SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, interval '1 day') AS sale_date
)
SELECT c.sale_date
FROM calendar c
LEFT JOIN sales s ON c.sale_date = s.sale_date
WHERE s.sale_id IS NULL
ORDER BY c.sale_date;

The retail operations team often needs to pinpoint days when no sales were recorded in any store. This helps them investigate potential issues with staffing, inventory, or demand. By comparing a generated calendar to the actual sales records, you can efficiently detect these **gaps** and take action.

-- Detect gaps in daily sales data across all stores for Q1 2024
WITH calendar AS (
  SELECT generate_series('2024-01-01'::date, '2024-03-31'::date, interval '1 day') AS sale_date
)
SELECT c.sale_date
FROM calendar c
LEFT JOIN sales s ON c.sale_date = s.sale_date
WHERE s.sale_id IS NULL
ORDER BY c.sale_date;

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def setUp(self):
        # No DB state to reset, as VALUES clause is used in the query
        pass

    def test_row_count(self):
        """Check the number of returned absence periods is correct."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        _dynamic_test(
            self,
            len(rows) == 4,
            "Returned correct number of absence periods (4).",
            f"Expected 4 periods, got {len(rows)}."
        )

    def test_grouping_and_dates(self):
        """Check correct grouping and date ranges for each employee."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        # Convert to pandas Timestamp for date columns
        df['period_start'] = pd.to_datetime(df['period_start'])
        df['period_end'] = pd.to_datetime(df['period_end'])
        # Check for correct periods
        expected = [
            {'employee_id': 1, 'period_start': pd.Timestamp('2024-01-10'), 'period_end': pd.Timestamp('2024-01-11'), 'days_absent': 2},
            {'employee_id': 1, 'period_start': pd.Timestamp('2024-01-13'), 'period_end': pd.Timestamp('2024-01-13'), 'days_absent': 1},
            {'employee_id': 2, 'period_start': pd.Timestamp('2024-01-15'), 'period_end': pd.Timestamp('2024-01-17'), 'days_absent': 3},
            {'employee_id': 3, 'period_start': pd.Timestamp('2024-01-20'), 'period_end': pd.Timestamp('2024-01-20'), 'days_absent': 1},
        ]
        # Check each expected period is present
        missing = []
        for exp in expected:
            match = (
                (df['employee_id'] == exp['employee_id']) &
                (df['period_start'] == exp['period_start']) &
                (df['period_end'] == exp['period_end']) &
                (df['days_absent'] == exp['days_absent'])
            )
            if not match.any():
                missing.append(exp)
        _dynamic_test(
            self,
            len(missing) == 0,
            "All expected absence periods are present.",
            f"Missing absence periods: {missing}"
        )

    def test_separate_nonconsecutive(self):
        """Non-consecutive absences for same employee must be separate periods."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        # Convert to pandas Timestamp for date columns
        df['period_start'] = pd.to_datetime(df['period_start'])
        df['period_end'] = pd.to_datetime(df['period_end'])
        # employee 1 has two periods: 2024-01-10 to 2024-01-11 and 2024-01-13
        periods = df[df['employee_id'] == 1][['period_start', 'period_end']].sort_values(by='period_start').values.tolist()
        expected = [[pd.Timestamp('2024-01-10'), pd.Timestamp('2024-01-11')], [pd.Timestamp('2024-01-13'), pd.Timestamp('2024-01-13')]]
        condition = all([list(periods[i]) == expected[i] for i in range(len(expected))])
        _dynamic_test(
            self,
            condition,
            "Non-consecutive absences for employee 1 are correctly split into separate periods.",
            f"Periods found: {periods}, expected: {expected}"
        )

    def test_consecutive_grouped(self):
        """Consecutive absences for same employee must be grouped into a single period."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        columns = [desc[0] for desc in self.cursor.description]
        df = pd.DataFrame(rows, columns=columns)
        # Convert to pandas Timestamp for date columns
        df['period_start'] = pd.to_datetime(df['period_start'])
        df['period_end'] = pd.to_datetime(df['period_end'])
        # employee 2: 2024-01-15, 2024-01-16, 2024-01-17 should be grouped
        period = df[(df['employee_id'] == 2) & (df['period_start'] == pd.Timestamp('2024-01-15')) & (df['period_end'] == pd.Timestamp('2024-01-17'))]
        _dynamic_test(
            self,
            not period.empty and int(period['days_absent'].iloc[0]) == 3,
            "Consecutive absences for employee 2 are grouped as one period of 3 days.",
            f"Found: {period.to_dict('records')}"
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    import re
    text = text.lower()
    text = re.sub(r"\s{2,}", " ", text)
    text = re.sub(r"\s*([,:?])\s*", r"\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

Master advanced SQL techniques for building analytical reports. Learn to solve real-world business problems using multi-level aggregations, window functions, cohort and funnel analysis, KPI calculations, and more. Each chapter features theory, a realistic business use case, and a hands-on coding task.

Explore advanced SQL concepts and apply them to real-world analytical reporting scenarios across multiple business domains.

Time Series Analysis: Gaps and Islands

Lösung