Objective

Write a SQL query to count the number of unique callers who made multiple calls within a 7-day interval of each other. A caller who made more than two qualifying calls should only be counted once.

Table Schema:

• caller_history

Task Requirements:

• A caller qualifies if they have at least two calls where the gap between consecutive calls is 7 days or fewer
• Two calls on the same day (gap of 0) count as within 7 days
• Each qualifying caller should be counted exactly once regardless of how many close calls they made
• Output a single count of unique qualifying callers
  
  

Output columns: unique_callers

WITH call_pairs AS (
    SELECT
        caller_id,
        call_date,
        LEAD(call_date) OVER (
            PARTITION BY caller_id ORDER BY call_date
        ) AS next_call_date
    FROM caller_history
)
SELECT COUNT(DISTINCT caller_id) AS unique_callers
FROM call_pairs
WHERE next_call_date - call_date <= 7;

Explanation

Step 1: Find each caller's next call date

WITH call_pairs AS (
    SELECT
        caller_id,
        call_date,
        LEAD(call_date) OVER (
            PARTITION BY caller_id ORDER BY call_date
        ) AS next_call_date
    FROM caller_history
)

The LEAD() window function looks ahead one row within each caller_id partition, ordered by call_date. This pairs each call with the caller's chronologically next call. For a caller's last call, LEAD() returns NULL.

Step 2: Filter for calls within 7 days

WHERE next_call_date - call_date <= 7

Subtracting two DATE values in PostgreSQL gives the number of days between them. The filter keeps only rows where the gap to the next call is 7 days or fewer. Rows where next_call_date is NULL (last call per caller) are automatically excluded since any comparison with NULL returns NULL.

Step 3: Count unique callers

SELECT COUNT(DISTINCT caller_id) AS unique_callers

COUNT(DISTINCT caller_id) ensures each caller is counted exactly once, even if they have multiple pairs of calls within 7 days. This satisfies the requirement that callers with more than two qualifying calls are only counted once.