Drill 3 of 4 in Marketplace questions
0

Interview Question Bank · LEFT JOIN · COUNT FILTER · NOT IN and NULL

3. Neighborhoods with no active listings

EasyMarketplaceQuestion #264

Concept: LEFT JOIN · COUNT FILTER · NOT IN and NULLshow

To find parents with no matching children of a certain kind, start from the parent table and LEFT JOIN the children, then keep the groups where a filtered count is zero. Count a column from the child table, not *, because a parent with no children still produces one row of NULLs. Beware NOT IN (subquery): if the subquery returns a single NULL, NOT IN is never true and you get no rows.

Example (different data):

SELECT s.name, COUNT(c.id) AS classes
FROM schools s
LEFT JOIN classes c ON c.school_id = s.id
GROUP BY s.id, s.name
HAVING COUNT(c.id) FILTER (WHERE c.term = 'fall') = 0;

Your task

Burrowstay, a short-stay rental marketplace, wants to see where supply has dried up: neighborhoods where no listing is currently active. Paused and delisted listings don't count as supply.

Return city, neighborhood (its name) and listings (how many listings it has in any status, 0 if none) for every neighborhood with no active listing. Sort by city, then neighborhood.

Starting Postgres in your browser…

Checking your access…