How to calculate approval rate in SQL
Contents:
What approval rate is and why payments teams obsess over it
Imagine the head of payments at Stripe pings you on a Friday afternoon. "Our authorization rate dropped 1.4 points yesterday — can you slice it by issuer and decline reason before standup on Monday?" On the volume Stripe or Airbnb processes, 1.4 points is tens of millions of dollars in lost GMV, and it usually points to something concrete — a 3D Secure rule change, an issuer pulling back, a BIN routed to the wrong acquirer, a new fraud model that decided every prepaid card is suspicious.
Approval rate is the headline metric for any team that touches checkout, billing, or money movement. A marketplace like DoorDash or Uber lives or dies on order-level approval at peak hours. A SaaS company like Notion or Linear cares about subscription renewal approval, because a declined renewal turns into involuntary churn weeks later. When the rate moves, leadership wants SQL today, not after a sprint of dashboard work.
The reason this metric ends up in nearly every payments interview at Stripe, Airbnb, and DoorDash is that the obvious calculation is wrong in several ways. Counting at the attempt level overstates the problem because the customer retried. Treating fraud blocks as declines mixes two different causes. Lumping soft and hard declines hides the fact that one is recoverable. This post walks through the SQL — the base rate, issuer and BIN cuts, decline reason analysis, retry math, and the fraud handling that interviewers love to dig into.
The formula and the data model
At its core the math is one line. Approval rate is the share of payment attempts that were authorized by the issuing bank.
approval_rate = approved_attempts / total_attemptsA healthy benchmark for a US e-commerce merchant on a major processor sits between 0.85 and 0.92 for card-not-present. Above 0.95 is excellent territory, typically low-risk recurring billing. Below 0.75 is a flashing red light — fraud rules over-aggressive, 3D Secure misconfigured, or the merchant slipped into a higher-risk MCC and issuers are pulling back.
The data model used here is a single fact table of payment attempts — what most payments warehouses converge on after flattening processor webhooks.
payment_attempts (
attempt_id,
order_id,
user_id,
issuer, -- visa, mastercard, amex, discover
bin, -- first six digits of the card
customer_country,
merchant_country,
currency,
amount,
status, -- approved, declined, error
decline_reason, -- nullable, populated when status = declined
is_3ds, -- boolean, true if 3D Secure was challenged
is_fraud_block, -- boolean, true if internal fraud engine blocked
attempted_at
)order_id lets you collapse retries on the same purchase intent. bin is the six-digit prefix that identifies the issuing bank — Chase debit cards behave very differently from Capital One credit cards, and slicing by BIN is how you find out. decline_reason is the normalized processor response, mapped to a small enumeration like insufficient_funds, do_not_honor, card_expired, 3ds_failed, fraud_suspected, and issuer_unavailable.
Five SQL queries you will actually ship
1. Approval rate over a single window
The base query for the executive number. Use a 30-day window so weekday and weekend mix is balanced.
SELECT
COUNT(*) AS attempts,
COUNT(*) FILTER (WHERE status = 'approved') AS approved,
COUNT(*) FILTER (WHERE status = 'declined') AS declined,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0),
2
) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND status <> 'error';The NULLIF guards against dividing by zero on an empty window. The status <> 'error' filter excludes processor or network timeouts that never reached the issuer — counting those as declines blames the bank for an internal problem.
2. Daily trend
The shape over time is more useful than the headline. A flat line at 0.88 is fine. A 4-point overnight drop is an incident.
SELECT
DATE_TRUNC('day', attempted_at) AS day,
COUNT(*) AS attempts,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0),
2
) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '90 days'
AND status <> 'error'
GROUP BY 1
ORDER BY 1;3. By issuer
The issuer cut is where most investigations begin. If Visa is fine and Mastercard dropped 6 points, the fix is at the network or BIN-routing layer, not the fraud engine.
SELECT
issuer,
COUNT(*) AS attempts,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0),
2
) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND status <> 'error'
GROUP BY issuer
HAVING COUNT(*) >= 1000
ORDER BY approval_rate_pct DESC;The HAVING COUNT(*) >= 1000 filter cuts noise — an issuer with twenty attempts and one decline shows 95 percent, but that is a statistical accident.
4. Decline reason mix
Once you know the rate dropped, this query tells you why — share of declines by reason.
SELECT
decline_reason,
COUNT(*) AS declines,
ROUND(
100.0 * COUNT(*) / SUM(COUNT(*)) OVER (),
2
) AS share_of_declines_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND status = 'declined'
GROUP BY decline_reason
ORDER BY declines DESC;If insufficient_funds is the largest bucket the user is the problem, not you. If do_not_honor jumps the issuer is the problem. If 3ds_failed jumps the friction layer is the problem. Each reason maps to a different team.
5. BIN-level drilldown
When an issuer cut shows movement, BIN — the first six digits of the card — is the next cut. A bank issues several BIN ranges with different policies; Capital One's premium travel BIN and its secured-card BIN behave nothing alike.
SELECT
bin,
COUNT(*) AS attempts,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0),
2
) AS approval_rate_pct
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND issuer = 'visa'
AND status <> 'error'
GROUP BY bin
HAVING COUNT(*) >= 500
ORDER BY approval_rate_pct ASC
LIMIT 20;Sorted ascending so the worst-performing BINs surface first. The bottom 20 list is what you bring to the routing meeting.
Retry logic and the per-order view
The most common interview follow-up is "what is the difference between attempt-level and order-level approval rate, and which one does leadership actually want?" The honest answer is both, but order-level is the more accurate revenue indicator.
WITH order_outcome AS (
SELECT
order_id,
COUNT(*) AS total_attempts,
BOOL_OR(status = 'approved') AS ever_approved,
MIN(attempted_at) AS first_attempt_at
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND status <> 'error'
GROUP BY order_id
)
SELECT
COUNT(*) AS orders,
COUNT(*) FILTER (WHERE ever_approved) AS orders_approved,
ROUND(
100.0 * COUNT(*) FILTER (WHERE ever_approved) / NULLIF(COUNT(*), 0),
2
) AS order_approval_rate_pct,
ROUND(AVG(total_attempts), 2) AS avg_attempts_per_order
FROM order_outcome;A merchant with 84 percent attempt-level approval can have 91 percent order-level approval if retries are wired correctly — the first authorization fails on a velocity rule, the second attempt minutes later succeeds. That seven-point gap is the revenue saved by smart retries.
The follow-up cut breaks down approval rate by attempt number, which tells you whether retries are even helping.
WITH numbered AS (
SELECT
order_id,
attempted_at,
status,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY attempted_at
) AS attempt_num
FROM payment_attempts
WHERE attempted_at >= CURRENT_DATE - INTERVAL '30 days'
AND status <> 'error'
)
SELECT
attempt_num,
COUNT(*) AS attempts,
ROUND(
100.0 * COUNT(*) FILTER (WHERE status = 'approved') / NULLIF(COUNT(*), 0),
2
) AS approval_pct
FROM numbered
GROUP BY attempt_num
ORDER BY attempt_num;Typically attempt 1 approves at the headline rate, attempt 2 catches another 25 to 40 percent of failures from attempt 1, and attempt 3 has diminishing returns — often signalling that retry waterfalls are stuck on a card that will never approve.
Common pitfalls
The first pitfall is conflating processor errors with issuer declines. When a payment times out at the network layer or the processor returns a 503, the transaction never reached the bank, and treating it as a decline blames the wrong team. The fix is a third status — error — excluded from the headline denominator but tracked separately as a reliability metric. A processor error rate above half a percent is its own incident.
The second pitfall is mixing soft and hard declines into one number. A soft decline like insufficient_funds is temporary and recoverable through retry. A hard decline like card_expired or do_not_honor is final, and retrying burns fees and triggers velocity rules at the issuer. The fix is a mapping table of decline_reason to is_retryable and reporting soft and hard rates separately. This also surfaces uncapped retries, because hard-decline retries pile up in the data and skew everything.
The third pitfall is treating internal fraud blocks as if they were issuer declines. When your fraud engine rejects a transaction before it ever touches the network, that is a merchant choice and should be measured separately. Mix the two and you cannot tell whether a dip came from the bank tightening up or your own model getting more conservative. The fix is the is_fraud_block flag — count internal blocks as their own status and report a "post-fraud approval rate" that excludes them from the denominator, so the metric measures only what the issuer did.
The fourth pitfall is cross-border noise. A US customer paying with a US card on a US merchant approves at one rate. The same card on a merchant in another country approves at a meaningfully lower rate because cross-border traffic triggers extra scrutiny at the issuer. Reporting one global rate over a traffic mix that shifts daily creates phantom dips. The fix is to segment by the pair customer_country, merchant_country and only roll up after the segments are stable.
Optimization tips
The fact table grows fast — a midsize merchant ships hundreds of millions of rows per year — so the first optimization is partitioning by attempted_at at day granularity. Dashboard queries filter on attempted_at, so partition pruning eliminates most scanned data. Pair that with a clustering or sort key on issuer, bin on Snowflake or BigQuery.
The second optimization is a daily roll-up table maintained by an incremental job. Most stakeholders look at the headline and the per-issuer cut, never the per-attempt detail, so materializing (day, issuer, decline_reason, attempts, approved, declined) collapses billions of rows into tens of thousands. The full fact table stays available for the order-level retry query, which needs row-level data because BOOL_OR cannot be pre-aggregated.
The third optimization is a small dimension table for the decline reason. Map raw processor codes once — decline_code -> normalized_reason, is_retryable, is_issuer_side — and join. This keeps queries readable and lets product managers reason about the metric without learning the entire ISO 8583 response code spec.
Related reading
- SQL window functions interview questions
- How to calculate add to cart rate in SQL
- How to calculate AOV in SQL
- Account takeover detection in SQL
- How to detect anomalies in SQL
If you want to drill payments and fraud SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.
FAQ
What approval rate should I aim for?
For a US e-commerce merchant on a major processor, anywhere from 0.85 to 0.92 on card-not-present is normal, and above 0.95 is excellent. Recurring billing on stored cards tends to land higher because the issuer trusts the relationship. If you are below 0.75 the cause is almost always an over-aggressive fraud rule, a 3D Secure misconfiguration, a recently changed MCC, or routing through an acquirer without strong relationships with the issuers you see most often.
Soft decline versus hard decline — what is the practical difference?
A soft decline is temporary and the same card may approve on a retry minutes later. Typical soft codes include insufficient_funds, issuer_unavailable, and velocity_limit_exceeded. A hard decline is final for that card on that merchant — card_expired, lost_card, stolen_card, do_not_honor — and retrying just costs you network fees and risks the issuer flagging your merchant ID for abuse. Always classify the decline before retrying, and never retry hard declines.
How does 3D Secure interact with approval rate?
3DS shifts liability for fraud chargebacks from merchant to issuer. The tradeoff is friction — users see a challenge screen and a meaningful share abandon. Approval rate as measured on payment attempts often goes up with 3DS because abandoned challenges never become an attempt, while checkout-completion goes down. The honest metric is the joint rate of checkout_started -> authorized.
Should I count internal fraud blocks as declines?
No — keep them in a separate status. An internal fraud block is a merchant policy decision and lumping it with issuer declines hides whether the rate movement came from the bank or from your own model. Report two numbers — the raw rate, and the "post-fraud" rate where the denominator excludes internal blocks. The gap between them is the cost of your current fraud policy in conversion terms.
How do I improve approval rate?
The cheap wins are smart retry waterfalls that respect the soft-versus-hard split, account updater for stored cards on subscriptions, network tokens replacing raw PANs, fine-tuning 3D Secure to only fire on risky transactions, and multi-acquirer routing so traffic to a given BIN goes through the acquirer with the best relationship with that issuer. Each typically buys one to three points; stacked they move a merchant from the high seventies into the high eighties.