customer_snapshots holds one row per customer per day with their loyalty tier. Compress it into SCD type 2 versions: a new version starts whenever the tier differs from the previous day's tier (a tier can come back later, e.g. Gold -> Platinum -> Gold is three versions). Return customer_id, tier, valid_from, valid_to (the day before the next version starts; NULL for the latest version) and is_current (1 for the latest version, else 0). Order by customer_id, valid_from.
| Column | Type |
|---|---|
| snapshot_date | DATE |
| customer_id | TEXT |
| tier | TEXT |
| snapshot_date | customer_id | tier |
|---|---|---|
| 2026-06-01 | C1 | Silver |
| 2026-06-02 | C1 | Silver |
| 2026-06-03 | C1 | Gold |
| 2026-06-04 | C1 | Gold |
| 2026-06-01 | C2 | Bronze |
| 2026-06-02 | C2 | Bronze |
| 2026-06-03 | C2 | Bronze |
| 2026-06-04 | C2 | Silver |
| customer_id | tier | valid_from | valid_to | is_current |
|---|---|---|---|---|
| C1 | Silver | 2026-06-01 | 2026-06-02 | 0 |
| C1 | Gold | 2026-06-03 | NULL | 1 |
| C2 | Bronze | 2026-06-01 | 2026-06-03 | 0 |
| C2 | Silver | 2026-06-04 | NULL | 1 |
Solve it in the live editor on this page — run on sample data, then submit against hidden tests.
‹ Effective Roles Through Role Inheritance · Forward-Fill Missing FX Rates ›
{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.
Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
{{ cur.statement }}
No submissions yet. Press Evaluate to submit your query.
{{ solutionSql }}
{{ out.error }}
You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.