Professional vs Non-Professional Hosts | Dataford Interview Questions

Turing wants to compare listing prices from professional and non-professional hosts on its accommodation marketplace. A host is professional when they have listings in more than 5 distinct values of neighbourhood_cleansed.

Write a PostgreSQL query that compares the average listing price for both host classifications.

Requirements

Count distinct non-null neighbourhood_cleansed values for each host.
Classify hosts with more than 5 locations as professional; classify all other hosts with listings as non_professional.
Return the average listing price for each classification and the difference between the professional and non-professional averages. Round monetary results to two decimal places.

Schema

hosts

Column	Type	Description
host_idPK	INT	Unique host identifier
host_name	VARCHAR(100)	Host display name

listings

Column	Type	Description
listing_idPK	INT	Unique listing identifier
host_id	INT	Host who owns the listing
neighbourhood_cleansed	VARCHAR(100)	Standardized neighborhood name
price	NUMERIC(10,2)	Nightly listing price

Your question is Professional vs Non-Professional Hosts. Start with the requirements and the two tables in the Question tab.

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

Problem