Drill 2 of 12 in Food delivery questions
0

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…