LEFT JOIN NULL Handling | Dataford Interview Questions

How do you handle NULL values when performing a LEFT JOIN, and what are the implications on your final aggregate metrics?

Using the provided Zillow listings and buyer inquiries tables, write a query that preserves every listing and summarizes its inquiry activity. Distinguish between listings with no inquiries and inquiries whose offer amount is NULL.

Output

One row per listing with listing_id, market, inquiry_count, offer_count, total_offer_amount, and average_offer_amount.
Return zero-valued metrics for listings without usable offers.
Order by market ascending, then listing_id ascending.

Schema

listings

Column	Type	Description
listing_idPK	INT	Unique Zillow listing identifier
market	VARCHAR(100)	Listing market
status	VARCHAR(30)	Current listing status

buyer_inquiries

Column	Type	Description
inquiry_idPK	INT	Unique buyer inquiry identifier
listing_id	INT	Referenced listing identifier
inquiry_date	DATE	Date the inquiry was submitted
offer_amount	NUMERIC(12,2)	Optional offer amount submitted with the inquiry

Your question is LEFT JOIN NULL Handling. 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