Interview Classics · Conditional ratio · JOIN filter · FILTER
9. No-show rate without test accounts
Concept: Conditional ratio · JOIN filter · FILTERshow
A rate is a filtered count over a total: COUNT(*) FILTER (WHERE outcome = 'x') / COUNT(*). When some rows must be ignored entirely (test accounts, banned users), join the table that flags them and filter in WHERE, so they leave the numerator and the denominator. Multiply by 100.0 to avoid integer division.
Example (different data):
SELECT s.carrier, ROUND(100.0 * COUNT(*) FILTER (WHERE s.late) / COUNT(*), 2) AS late_pct FROM shipments s JOIN merchants m ON m.id = s.merchant_id WHERE NOT m.is_sandbox GROUP BY s.carrier;
Your task
Classic interview question: a home-viewing platform ranks agents by how often buyers stand them up, but QA bots and load-test accounts book fake viewings that must not count.
Using only viewings booked by real buyers (viewing_buyers.is_test is false), return agent, viewings (all their viewings, any outcome), no_shows (outcome 'no_show') and no_show_rate (no_shows as a percentage of viewings, rounded to 2 decimals). Agents with no real viewings are left out. Sort by no_show_rate (highest first), then agent.
Starting Postgres in your browser…
Checking your access…