Best Expected Revenue Listing | Dataford Interview Questions

Your question is Best Expected Revenue Listing. Start with the requirements and the two 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

Turing wants to identify the listing with the highest expected revenue from the previous 12 months. Assume 60% of guests leave reviews, and every guest stays exactly the listing's minimum number of nights.

Write a PostgreSQL query using listings and reviews to return the single best listing.

Requirements

Consider reviews from the rolling 12-month period before CURRENT_DATE.
Include only listings where minimum_nights <= 7 and nightly_price is available.
Estimate guests as review_count / 0.60.
Calculate expected revenue as estimated guests multiplied by minimum_nights and nightly_price.
Use a window function to rank listings and return the highest-revenue listing, breaking ties by listing_id.

Schema

listings

Column	Type	Description
listing_idPK	INTEGER	Unique listing identifier
listing_name	VARCHAR(120)	Public listing name
city	VARCHAR(80)	City where the listing is located
nightly_price	NUMERIC(10,2)	Price charged per night
minimum_nights	INTEGER	Minimum number of nights per stay

reviews

Column	Type	Description
review_idPK	INTEGER	Unique review identifier
listing_id	INTEGER	Listing associated with the review
reviewer_name	VARCHAR(100)	Name of the reviewing guest
review_date	DATE	Date the review was submitted