Share of Users Who Saved a Home, by State

EasySQL

Reported in interviews at Zillow

Problem statement

A real-estate listings app lets users save homes to a favorites list. Saving is a strong intent signal, so the growth team wants to see, for every state, what percentage of users have saved at least one home.

Tables (SQLite dialect):

  • users(user_id INT, state TEXT): one row per user; user_id is unique and state is never NULL.
  • saved_homes(user_id INT, home_id INT, saved_date TEXT): one row per save event. A user can save many homes, and can save the same home more than once (after removing it).

For every state with at least one user, return state, total_users, users_with_save, pct_saved: the number of users in the state, how many of them have at least one row in saved_homes, and pct_saved = 100 * users_with_save / total_users (rounding to 2 or more decimals is fine). States where nobody saved a home must appear with 0. Saves by user ids not in users are ignored. Order by state.

Example

users: (1, WA), (2, WA), (3, WA), (4, WA), (5, CA), (6, CA), (7, TX)

saved_homes:

user_id | home_id | saved_date
1       | 9001    | 2026-02-01
1       | 9002    | 2026-02-03
1       | 9001    | 2026-02-10
3       | 9100    | 2026-02-04
5       | 9200    | 2026-02-05
6       | 9201    | 2026-02-06
12      | 9300    | 2026-02-07

Output:

state | total_users | users_with_save | pct_saved
CA    | 2           | 2               | 100.0
TX    | 1           | 0               | 0.0
WA    | 4           | 2               | 50.0

User 1 saved three times but counts once, TX has no savers and still appears, and user 12 is not in users.

Show hint

What should one row represent before you compute anything by state, given that a single user can generate many save events?

Relevant roles: Data Scientist, Forward Deployed Engineer and Applied Scientist.

Related free problems