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…