Calculate the payment success rate for each country. Success rate is the number of successful payments divided by total payment attempts. Round to 2 decimal places and only show countries with at least 5 payment attempts.
This metrics calculation problem reflects Stripe's core business of payment processing analytics. Understanding success rates by geography is critical for identifying fraud patterns, optimizing routing, and improving authorization rates. This question tests your ability to perform conditional aggregation, calculate percentages correctly, and apply filtering at the right stage of query execution.
Key concepts: CASE WHEN for conditional counting, GROUP BY for country-level aggregation, HAVING clause for post-aggregation filtering, ROUND() for precision, and division with proper type casting (1.0 for float division). Understanding the difference between WHERE (pre-aggregation) and HAVING (post-aggregation) is crucial.
Stripe's analytics teams use similar queries to: generate country-specific success rate dashboards for merchants, identify regions requiring payment method optimization, calculate SLA compliance metrics, detect anomalies in authorization patterns, feed machine learning models for fraud detection, and power automated alerts when success rates drop below thresholds.
When tackling this Stripe problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT
country,
ROUND(
SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 1.0
/ COUNT(*),
2
) AS success_rate
FROM payments
GROUP BY country
HAVING COUNT(*) >= 5
ORDER BY success_rate DESC;This approach uses a conditional aggregate — SUM(CASE WHEN …) — to count successes without a subquery. Multiplying by 1.0 forces floating-point division before ROUND(). HAVING filters groups after aggregation, ensuring only countries with at least 5 attempts appear. It is the most readable and widely compatible approach, working on MySQL, PostgreSQL, SQLite, and SQL Server. The single GROUP BY pass is efficient. The main limitation is that SUM/COUNT are evaluated twice (once for select, once for HAVING), though most optimizers deduplicate this.
SELECT
country,
ROUND(AVG(CASE WHEN status = 'success' THEN 1.0 ELSE 0.0 END), 2) AS success_rate
FROM payments
GROUP BY country
HAVING COUNT(*) >= 5
ORDER BY success_rate DESC;AVG over a 0/1 expression is mathematically identical to SUM(success)/COUNT(*) but is more concise. Using 1.0 and 0.0 avoids integer-division truncation in strict SQL dialects. This reads naturally as 'average success flag per country.' It performs comparably to Solution 1 — both are single-pass aggregations. Prefer this when code brevity matters in interviews. Some interviewers appreciate recognizing that a proportion equals an average of indicator variables, showing deeper statistical intuition.
Both solutions require a full table scan and a hash or sort aggregation grouped by country, giving O(n) scan complexity with O(k) memory for k distinct countries. An index on (country, status) allows the optimizer to use an index-only scan, reading no heap pages and reducing I/O dramatically at scale. The HAVING COUNT(*) >= 5 clause is applied post-aggregation and cannot be pushed down to the scan phase, so all rows are read regardless. At very large scale (billions of payment rows), partitioning the table by country and using partial aggregation per partition before merging improves throughput. ROUND() is cheap and applied only once per output row. ORDER BY success_rate DESC requires a sort over the result set — since this is typically small (number of distinct countries), it adds negligible cost.
A classic mistake is integer division: SUM(success_count) / COUNT(*) in databases like older MySQL or PostgreSQL returns 0 for any rate below 1.0. Always cast to float with * 1.0 or use AVG with decimal literals. Another pitfall is placing the minimum-attempts filter in WHERE instead of HAVING — WHERE runs before grouping, so COUNT(*) is unavailable there and the query errors or gives wrong results. Candidates sometimes count NULL statuses as failures when they should be excluded; clarify requirements. Ordering by country instead of success_rate DESC is a common oversight under pressure. Finally, forgetting ROUND() or using the wrong precision misrepresents the rate.
Add COUNT(*) AS total_attempts to the SELECT list. Since it is already computed for the HAVING clause, there is no additional cost. You might also expose SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS successful_count for full transparency, letting readers verify the rate themselves.
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Because COUNT(*) is an aggregate result, it only exists after GROUP BY runs. Putting COUNT(*) >= 5 in WHERE causes a syntax error or incorrect behavior. HAVING is the correct clause whenever your condition references an aggregate function.
NULL statuses are treated as failures by both solutions (CASE WHEN status = 'success' returns 0 for NULL). If NULLs represent unknown outcomes that should be excluded from the rate entirely, add WHERE status IS NOT NULL before grouping, or change COUNT(*) to COUNT(status) in the denominator to exclude NULLs from the total count.