Drill 1 of 8 in Fintech questions
0

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…