Interview Question Bank · COUNT FILTER · HAVING
2. Win rate for regular players
EasyGamingQuestion #146
Concept: COUNT FILTER · HAVINGshow
A rate needs two counts from the same group: all the rows (the denominator) and the ones that pass a condition. COUNT(*) FILTER (WHERE ...) gives you the second without throwing away the first, which a WHERE clause would. To ignore groups that are too small to trust, filter after grouping with HAVING, since WHERE can't see counts.
Example (different data):
SELECT agent, COUNT(*) AS calls, ROUND(100.0 * COUNT(*) FILTER (WHERE outcome = 'resolved') / COUNT(*), 1) AS resolved_pct FROM support_calls GROUP BY agent HAVING COUNT(*) >= 20;
Your task
Skyrift Arena's matchmaking team wants a win-rate table for its 1v1 mode, but only for players with enough matches for the number to mean something.
For players with at least 5 matches in gm_match_players, return username, matches, wins and win_rate_pct (wins as a % of matches, rounded to 1 decimal). A draw counts as a match but not a win. Sort by win_rate_pct descending, then username.
Starting Postgres in your browser…
Checking your access…