Deal Flow

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L4

Problem

A housing marketplace records every view of a property listing and every lead a buyer submits on one, each with the buyer and the time. Each listing has a property type and sits in a neighborhood that rolls up to a city and a state, and the model has to yield the view-to-lead conversion rate broken down by location at any of those levels and by property type.

Sellers want buyers. Buyers want deals.

Worked solution and explanation

A ratio that has to roll up

This is a non-additive metric dressed up as a funnel. Conversion is leads over views, and the report wants it at neighborhood, city and state, crossed with property type. Anyone can draw a views table and a leads table. What separates candidates is where the rate gets computed: the weak design stores it, or stores counts already summed by neighborhood, and the strong one keeps every view and lead as its own row hanging off a listing that knows its type and its place. Get it wrong and a city's rate becomes an average of neighborhood rates, so a sleepy block with 50 views outvotes a busy one with 50,000.

Building it

Step 1: Record each event at its own grain

fact_views is one row per view, every repeat open included; fact_leads is one row per lead submitted. Each carries the buyer (user_id) and the time (viewed_at, submitted_at), and each has columns the other lacks (device on a view, message on a lead).

Step 2: Anchor both events on the listing

Both facts point at dim_listings through listing_id, and the listing carries property_type plus a geography_id. Type and location are facts about the listing, so they live there once and both sides of the ratio reach them by the same hop.

Step 3: Let geography roll up in place

dim_geography is one row per neighborhood with city and state denormalized onto it. Rolling up from neighborhood to state is a coarser grouping on the same row, not another table to reach.

Step 4: Share the buyer across both facts

dim_users is referenced by both facts, so a cut by signup cohort or user type lands on the numerator and the denominator identically.

The reference design

`fact_views` and `fact_leads` both reference `dim_listings` and `dim_users`; location rolls up through `dim_geography`.
  • dim_listings: listing_id BIGINT PK, geography_id INT FK, property_type TEXT, price DECIMAL, bedrooms INT, sqft INT, listed_at DATE
  • dim_geography: geography_id INT PK, neighborhood TEXT, city TEXT, state TEXT
  • dim_users: user_id BIGINT PK, signup_date DATE, user_type TEXT
  • fact_views: view_id BIGINT PK, listing_id BIGINT FK, user_id BIGINT FK, viewed_at TIMESTAMP, device TEXT
  • fact_leads: lead_id BIGINT PK, listing_id BIGINT FK, user_id BIGINT FK, submitted_at TIMESTAMP, message TEXT
  • dim_listings.geography_id references dim_geography.geography_id
  • fact_views.listing_id references dim_listings.listing_id
  • fact_views.user_id references dim_users.user_id
  • fact_leads.listing_id references dim_listings.listing_id
  • fact_leads.user_id references dim_users.user_id
Separate facts
fact_views and fact_leads stand apart. Each carries only the columns its event has, each partitions by its own date, and conversion is two per-listing totals joined on listing_id.
One typed event log
One event log with an event_type column also works: conditional counts give the same rate, as long as each row still points at the listing. The cost is width, not correctness: device and message sit empty on most rows.

Common follow-up questions