The Address That Changed

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Problem

The customers table keeps each customer's full address history, one row per address they have lived at tagged with that address's country, so every row after a customer's first one is a move. For the relocation team's review, list every address row of the customers who have moved at least once, each carrying that customer's total number of moves, from the lowest customer id up to the highest.

Addresses change. History must not be erased.

Tables

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

Expected output

Worked solution and explanation

This is a grain problem dressed up as an address history. The answer lives at one grain, one row per address on file, but the number on each row lives at a coarser one: the whole customer. Anyone can count rows per customer_id. What separates candidates is the reflex of adding first_name and country to that same GROUP BY so the descriptive columns come along for free. Every mover here lived in each country once, so the count per (customer_id, country) is always 1, COUNT(*) - 1 is 0 on every row, and the moved-at-least-once filter returns nothing at all.

Two grains, one row

Step 1: Count at the customer grain

Compute COUNT(*) - 1 AS move_count over customers grouped by customer_id alone. Nothing else goes in that GROUP BY: once country joins it, you are counting addresses, not customers.

Step 2: Stamp the total back onto every address

Join the per-customer counts back to customers on customer_id. The aggregate side has one row per customer, so each address row finds exactly one match: nothing is dropped or duplicated, every version just gains its customer's total.

Step 3: Drop the customers who never moved

WHERE moves.move_count > 0 removes the 184 single-address customers and keeps both rows for each of the 8 movers, 16 rows in all. Filtering on the joined count is what keeps the address grain intact.

Step 4: Sort by customer_id

The review reads straight down the id list. Within one customer the two addresses can come in either order: nothing in the table says which came first, so nothing is promised there.

SELECT history.customer_id, history.first_name, history.country, moves.move_count
FROM customers history
JOIN (
    SELECT customer_id, COUNT(*) - 1 AS move_count
    FROM customers
    GROUP BY customer_id
) moves ON history.customer_id = moves.customer_id
WHERE moves.move_count > 0
ORDER BY history.customer_id

One flat aggregate

GROUP BY customer_id, first_name, country turns every address into its own group, so COUNT(*) - 1 is 0 on all 200 rows and move_count > 0 keeps none of them.

Count, then join back

The count is taken over customer_id alone and then attached to each address, so Maria at 40445 shows 1 on both her 'China' and 'Sweden' rows.

Common follow-up questions

• If the table gained a `valid_from` date, how would you return only each mover's latest address? (Moves from a grain-preserving count to picking one row per customer.)
• How would you flag customers who moved back into a country they had already lived in? (Tests counting repeated values within a customer.)
• A duplicated load wrote the same address twice for some customers. What happens to `move_count`, and how do you guard it? (Tests whether the candidate sees rows versus unique addresses.)