Real Estate Models Familiarity | Dataford Interview Questions

Your question is Real Estate Models Familiarity. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

What real estate models are you familiar with?

Using the supplied tables, write a SQL query to identify models with at least one completed project and summarize the analyst's experience with each model.

Output

One row per familiar model with model_name, category_name, completed_projects, avg_deal_value_millions, and familiarity_level.
Exclude models without completed projects. Classify one, two, or three or more completed projects as Basic, Intermediate, or Advanced.
Order by completed projects descending, then model name ascending.

Schema

real_estate_models

Column	Type	Description
model_idPK	INT	Unique identifier for a real estate model
model_name	VARCHAR(100)	Name of the real estate model
model_type	VARCHAR(50)	Broad type of model
category_id	INT	Optional reference to a model category

model_categories

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(60)	Category label

analyst_model_experience

Column	Type	Description
experience_idPK	INT	Unique experience record identifier
model_id	INT	Referenced real estate model
project_status	VARCHAR(20)	Status of the project using the model
project_date	DATE	Date associated with the project
deal_value_millions	DECIMAL(10,2)	Deal value in millions of dollars
valuation_error_pct	DECIMAL(5,2)	Optional valuation error percentage