Interview Question Bank · Conditional COUNT · HAVING · minimum volume
2. Restaurants with the most cancellations
EasyFood deliveryQuestion #230
Concept: Conditional COUNT · HAVING · minimum volumeshow
A share is a filtered count over the group's full count. COUNT(*) FILTER (WHERE ...) counts the matching rows while the others stay in the denominator, which a WHERE clause would remove. HAVING filters groups after they are counted, so that is where a minimum-volume rule goes.
Example (different data):
SELECT driver, COUNT(*) AS trips, ROUND(100.0 * COUNT(*) FILTER (WHERE rating <= 2) / COUNT(*), 1) AS low_rating_pct FROM trips GROUP BY driver HAVING COUNT(*) >= 10;
Your task
Brightbowl's partner team is calling restaurants that cancel too many orders, but a restaurant with only a handful of orders gives too little evidence.
For restaurants with at least 5 orders (any status) in fd_orders, return restaurant (name), orders, cancelled (orders with status 'cancelled') and cancel_pct (cancelled as a percentage of orders, ROUND(..., 1)). Sort by cancel_pct descending, then restaurant.
Starting Postgres in your browser…
Checking your access…