Weekly completed deliveries per partner, idle ones included

A city operations head wants a weekly productivity report for every delivery partner, including partners who did not complete a single delivery that week.

From delivery_partners and deliveries, return partner_name and completed_deliveries: the number of deliveries with status COMPLETED made between 2026-10-12 and 2026-10-18 (inclusive). Partners with none must appear with 0. Order by completed_deliveries descending, then partner_name.

1. Step 1: Use `LEFT JOIN` from `delivery_partners` so every partner stays in the result.
2. Step 2: Put the status and date conditions in the `ON` clause. If they go in `WHERE`, rows with no matching delivery are removed and the partner disappears, which turns the outer join into an inner join.
3. Step 3: `COUNT(d.delivery_id)` counts only matched rows, so idle partners correctly get 0; `COUNT(*)` would return 1 for them.

SELECT dp.partner_name,
       COUNT(d.delivery_id) AS completed_deliveries
FROM delivery_partners dp
LEFT JOIN deliveries d
       ON d.partner_id   = dp.partner_id
      AND d.status       = 'COMPLETED'
      AND d.delivered_on BETWEEN DATE '2026-10-12' AND DATE '2026-10-18'
GROUP BY dp.partner_id, dp.partner_name
ORDER BY completed_deliveries DESC, dp.partner_name;