Drill 2 of 12 in Gaming questions
0

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…