Current and Previous Address Tracking with SCD Type 2

SQL coding challenge · Difficulty: medium · Topic: SCD Type 2 · +100 XP

Problem

customer_addresses is a Slowly Changing Dimension Type 2 history table. Each row is one address a customer moved to on address_start_date.

There is no end_date column. An address is implicitly replaced by the next one, so:

• current address = the row with the latest address_start_date
• previous address = the row immediately before it, by address_start_date

For each customer, return their current address and the address they lived at before it.

Tables

Table: customer_addresses

| Column | Type |
| --- | --- |
| address_id | INT |
| customer_id | INT |
| address | VARCHAR |
| address_start_date | DATE |

Example Input

| address_id | customer_id | address | address_start_date |
| --- | --- | --- | --- |
| 1 | 201 | 123 Elm St | 2022-01-01 |
| 2 | 201 | 456 Oak Ave | 2023-06-01 |
| 3 | 201 | 789 Pine Rd | 2024-03-01 |
| 4 | 202 | 10 Main St | 2021-05-15 |
| 5 | 202 | 20 Lake Dr | 2024-01-10 |
| 6 | 203 | 5 River Ln | 2023-09-01 |

Expected Output

| customer_id | current_address | previous_address |
| --- | --- | --- |
| 201 | 789 Pine Rd | 456 Oak Ave |
| 202 | 20 Lake Dr | 10 Main St |
| 203 | 5 River Ln | NULL |

Explanation

Customer 201 has three addresses. The latest start date is 2024-03-01, so 789 Pine Rd is current and 456 Oak Ave is the one before it.

Customer 203 has only ever had one address, so previous_address is NULL — the customer must still appear in the output.

Notes

• Return: customer_id, current_address, previous_address
• Sort by customer_id ASC
• Function to use: LAG(address) OVER (PARTITION BY customer_id ORDER BY address_start_date)

What this SQL challenge teaches you

“Current and Previous Address Tracking with SCD Type 2” is a medium-level SQL challenge focused on SCD Type 2. Working through it gives you hands-on practice with SCD, window functions, subquery — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

• SCD
• window functions
• subquery

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

1. Your result should return: customer_id, current_address,.
2. Compare your output to the Expected Output — the columns, values and row order must match exactly.
3. LAG(address) OVER ( PARTITION BY customer_id ORDER BY start_date )

Where this comes up

Variations of this problem have been reported in interviews at Snowflake, Databricks, SAP. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

• Top 3 Products per Category
• Running Total Revenue
• Find Duplicate Emails
• Median Salary per Department
• Second Highest Salary
• Latest Order Per Customer

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers SCD Type 2.

Solve this challenge free on PySpark.in