Objective

Write a SQL query to find contacts who had marketing touches for 3 or more consecutive weeks and had at least one trial_request touch. Output their email addresses in ascending order.

Table Schema:

• marketing_touches

Task Requirements:

• A "week" is determined by truncating the date to the start of its ISO week (Monday)
• Multiple touches within the same week count as a single week
• The trial_request touch does not need to fall within the consecutive-week streak, it just needs to exist for that contact
• Both conditions must be met: 3+ consecutive weeks of touches AND at least one trial_request
• Order results by email in ascending order
  
  

Output columns: email

WITH weekly_touches AS (
    SELECT DISTINCT
        contact_id,
        DATE_TRUNC('week', touch_date)::DATE AS week_start
    FROM marketing_touches
),
streaks AS (
    SELECT
        contact_id,
        week_start - (ROW_NUMBER() OVER (
            PARTITION BY contact_id ORDER BY week_start
        ) * 7)::INT AS grp
    FROM weekly_touches
)
SELECT DISTINCT m.email
FROM marketing_touches m
WHERE m.contact_id IN (
    SELECT contact_id
    FROM streaks
    GROUP BY contact_id, grp
    HAVING COUNT(*) >= 3
)
AND m.contact_id IN (
    SELECT DISTINCT contact_id
    FROM marketing_touches
    WHERE touch_type = 'trial_request'
)
ORDER BY m.email;

Explanation

Step 1: Collapse touches into distinct weeks per contact

WITH weekly_touches AS (
    SELECT DISTINCT
        contact_id,
        DATE_TRUNC('week', touch_date)::DATE AS week_start
    FROM marketing_touches
)

DATE_TRUNC('week', touch_date) truncates each date to the Monday of its week. DISTINCT ensures each contact-week pair appears only once, regardless of how many touches occurred that week.

Step 2: Detect consecutive weeks using the subtraction trick

streaks AS (
    SELECT
        contact_id,
        week_start - (ROW_NUMBER() OVER (
            PARTITION BY contact_id ORDER BY week_start
        ) * 7)::INT AS grp
    FROM weekly_touches
)

For each contact, ROW_NUMBER() assigns sequential numbers ordered by week_start. Multiplying by 7 and subtracting from the week start date produces a constant value for consecutive weeks. For example, weeks starting Jan 1, Jan 8, Jan 15 with row numbers 1, 2, 3 produce: Jan 1 - 7 = Dec 25, Jan 8 - 14 = Dec 25, Jan 15 - 21 = Dec 25. A gap in weeks breaks the constant, starting a new group.

Step 3: Filter contacts meeting both conditions

WHERE m.contact_id IN (
    SELECT contact_id
    FROM streaks
    GROUP BY contact_id, grp
    HAVING COUNT(*) >= 3
)
AND m.contact_id IN (
    SELECT DISTINCT contact_id
    FROM marketing_touches
    WHERE touch_type = 'trial_request'
)

The first IN subquery groups by contact_id and grp, then keeps contacts with at least one streak of 3+ weeks. The second IN subquery independently checks for contacts who had a trial_request touch at any point. Both conditions must be true. SELECT DISTINCT m.email and ORDER BY m.email produce a clean, sorted output.