# Deal Flow > Sellers want buyers. Buyers want deals. Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L4 ## Problem We run a housing marketplace where sellers list properties and buyers either view listings or submit leads. We need conversion rate from view to lead broken down by location and property type. Design the data model. ## Worked solution and explanation ### Why this problem exists in real interviews This probes whether a candidate separates facts by **grain and volume** instead of overloading a single event log. Views arrive at orders of magnitude more than leads, and conversion analytics need both sides joinable on a conformed listing dimension. > **Trick to Solving** > > Whenever a prompt says "conversion rate from X to Y," the trick is to notice you have two facts at two grains, not one. Before designing, ask: what is the cardinality ratio between the two, and do they share a common dimension? > > 1. Build separate fact tables for views and leads > 2. Share dim_listings and dim_geography across both facts > 3. Compute conversion at query time via a JOIN on listing_id > 4. Keep geography as its own hierarchy dimension --- ### Break down the requirements #### Step 1: Separate facts by grain `fact_views` is one row per impression; `fact_leads` is one row per contact request. Combining them forces a `fact_type` column and breaks additivity. #### Step 2: Model listings as a dimension `dim_listings` carries property type and a FK to geography, with price, bedrooms, and square footage as slowly changing attributes shared by both facts. #### Step 3: Model geography with a hierarchy `dim_geography` stores neighborhood, city, state in one row per neighborhood. Roll-ups become GROUP BY without joins. #### Step 4: Connect users as a conformed dimension `dim_users` is referenced by both facts so that cross-fact user behavior (did this viewer also submit a lead) is a single join away. --- ### The solution Below is one defensible model. The anchor is that views and leads are two distinct facts sharing the same dimensions, which is the purest form of a conformed star schema. > **Why this works** > > Two fact tables at different grains let the warehouse honor the 100:1 or 1000:1 view-to-lead ratio without bloating a combined events log with nulls. Conformed dimensions mean conversion, cohort, and geo roll-ups are all one JOIN away. > **Interviewers watch for** > > A strong candidate names the conformed-dimension pattern out loud and justifies why views and leads are not the same fact. They also raise whether to partition `fact_views` by day given the volume ratio. > **Common pitfall** > > An overloaded `events` table with `event_type` = 'view' or 'lead'. It feels flexible but destroys query performance: every view query pays the cost of every lead column, and the cardinality imbalance wrecks partition pruning. --- ### The analysis pattern **View-to-lead conversion by neighborhood** ```sql SELECT g.neighborhood, COUNT(DISTINCT v.view_id) AS views, COUNT(DISTINCT l.lead_id) AS leads, COUNT(DISTINCT l.lead_id) * 1.0 / NULLIF(COUNT(DISTINCT v.view_id), 0) AS conversion_rate FROM dim_listings d JOIN dim_geography g ON g.geography_id = d.geography_id LEFT JOIN fact_views v ON v.listing_id = d.listing_id LEFT JOIN fact_leads l ON l.listing_id = d.listing_id WHERE d.listed_at >= NOW() - INTERVAL '30 days' GROUP BY g.neighborhood ORDER BY conversion_rate DESC ``` --- ### Trade-offs and alternatives **Separate fact tables** fact_views and fact_leads split. * Partition pruning works cleanly * Schema columns reflect what each event actually has * Conversion requires a two-way JOIN **Unified event log** One events table with event_type discriminator. * Single ingest path * Sparse columns and null-heavy rows * Partition pruning blurred by the high-low volume mix --- ## Common follow-up questions - How would you model listings that change price mid-campaign? _(Tests whether the candidate reaches for a Type 2 SCD on dim_listings or a price history table.)_ - How do you attribute a lead to the view that drove it when a user visits five times? _(Tests last-touch vs first-touch attribution and whether a session key spans both facts.)_ - What changes when fact_views grows to 100M per day? _(Tests partitioning, clustering, and whether a rolled-up daily aggregate is introduced.)_ - How do you support GDPR deletion of a user who submitted leads a year ago? _(Tests whether user_id is a hashable pseudonym and whether lookups are tombstone-driven.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/deal_flow) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.