Consecutive Tax Years
ASKED IN INTERVIEWIntuit
Problem Statement
Find users who have filed taxes for 3 or more consecutive years.
Rules:
- Output columns: user_id.
- Definition: A sequence where Year N, Year N-1, and Year N-2 all exist for the same user.
- Sort by user_id ASC.
Tests your understanding of
Window Functions, LEAD, LAG and Filtering
Input Tables
tax_filings
| filing_id(INTEGER) | user_id(INTEGER) | tax_year(INTEGER) |
|---|---|---|
| 1 | 1 | 2021 |
| 2 | 1 | 2022 |
| 3 | 1 | 2023 |
| 4 | 2 | 2021 |
| 5 | 2 | 2023 |
user_profiles
| user_id(INTEGER) | state(VARCHAR) |
|---|---|
| 1 | CA |
filing_types
| filing_id(INTEGER) | form_name(VARCHAR) |
|---|---|
| 1 | 1040 |
Expected Output
| user_id(INTEGER) |
|---|
| 1 |
Tags
HardASKED IN INTERVIEWWindow FunctionsLEADLAGFiltering
30-40 min
39%