Interview Question Bank · Conditional COUNT · HAVING · minimum volume
1. Card approval rate by merchant
EasyFintechQuestion #98
Concept: Conditional COUNT · HAVING · minimum volumeshow
A rate is a filtered count over the total count of the same group. COUNT(*) FILTER (WHERE ...) counts the matching rows without dropping the others, which a WHERE clause would. Small groups give noisy rates, so set a minimum volume with HAVING, which filters groups after they are counted.
Example (different data):
SELECT courier, COUNT(*) AS deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE on_time) / COUNT(*), 1) AS on_time_pct FROM deliveries GROUP BY courier HAVING COUNT(*) >= 20;
Your task
Northpine Payments' risk team is looking for merchants whose card authorizations get declined unusually often, but a merchant with a handful of attempts tells them nothing.
For merchants with at least 5 authorization attempts in fin_card_auths, return merchant, attempts (all attempts) and approval_rate (the percentage of attempts with status 'approved', ROUND(..., 1)). Sort by approval_rate descending, then merchant.
Starting Postgres in your browser…
Checking your access…