Objective

Write a SQL query to find users who have filed taxes using any TurboTax product for 3 or more consecutive years. Output the qualifying user IDs in ascending order.

Table Schema:

• filed_taxes

Task Requirements:

• A user may file multiple returns in the same year; each year should only count once
• The product used does not matter, any TurboTax product counts
• Consecutive means no gap years (e.g., 2019, 2020, 2021 is consecutive; 2019, 2021, 2022 is not)
• A user with multiple separate 3+ year streaks should only appear once in the output
• Order results by user_id in ascending order
  
  

Output columns: user_id

WITH yearly_filings AS (
    SELECT DISTINCT
        user_id,
        EXTRACT(YEAR FROM filing_date)::INT AS filing_year
    FROM filed_taxes
),
streaks AS (
    SELECT
        user_id,
        filing_year - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY filing_year
        )::INT AS grp
    FROM yearly_filings
)
SELECT DISTINCT user_id
FROM streaks
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

Explanation

Step 1: Extract distinct filing years per user

WITH yearly_filings AS (
    SELECT DISTINCT
        user_id,
        EXTRACT(YEAR FROM filing_date)::INT AS filing_year
    FROM filed_taxes
)

The first CTE pulls the year from each filing_date and applies DISTINCT to ensure each user-year pair appears only once. This handles users who filed multiple returns in the same year.

Step 2: Create streak groups using the subtraction trick

streaks AS (
    SELECT
        user_id,
        filing_year - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY filing_year
        )::INT AS grp
    FROM yearly_filings
)

For each user, ROW_NUMBER() assigns sequential numbers (1, 2, 3, ...) ordered by year. Subtracting this from the actual year produces a constant value for consecutive years. For example, years 2019, 2020, 2021 with row numbers 1, 2, 3 all produce 2018 as the group value. A gap breaks the constant, creating a new group.

Step 3: Filter for streaks of 3 or more years

SELECT DISTINCT user_id
FROM streaks
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

Grouping by user_id and grp isolates each consecutive streak. HAVING COUNT(*) >= 3 keeps only streaks spanning 3 or more years. DISTINCT ensures a user with multiple qualifying streaks appears only once in the output.