Interview Question Bank · WHERE IN · COUNT FILTER · HAVING
3. Offer acceptance rate by recruiter
EasyHRQuestion #183
Concept: WHERE IN · COUNT FILTER · HAVINGshow
A rate needs the right denominator: only outcomes that have actually been decided. Filter rows first with WHERE, count the successes with COUNT(*) FILTER (WHERE ...), and divide with 100.0 so the math stays decimal. HAVING then drops groups too small to judge; it runs after WHERE, so it only counts the rows that survived.
Example (different data):
SELECT agent, COUNT(*) AS closed_tickets, ROUND(100.0 * COUNT(*) FILTER (WHERE solved_first_contact) / COUNT(*), 1) AS fcr_pct FROM support_tickets WHERE status = 'closed' GROUP BY agent HAVING COUNT(*) >= 10;
Your task
Quillmoor's Head of People is comparing recruiters on offer acceptance. Pending offers have no answer yet, and rescinded offers were withdrawn by Quillmoor, so neither says anything about how well a recruiter closes.
Counting only offers that were accepted or declined, return recruiter, decided_offers, accepted and acceptance_pct (accepted as a % of decided, rounded to 1 decimal) for recruiters with at least 5 decided offers. Sort by acceptance_pct descending, then recruiter.
Starting Postgres in your browser…
Checking your access…