Game: Level Difficulty
Electronic Arts
Problem Statement
Game designers at Epic Games need to ensure a smooth difficulty curve. A level that is suddenly much harder than the previous one can cause players to quit (churn).
Write a query to identify levels where the Fail Rate is at least 10 times higher than the Fail Rate of the immediately preceding level for that specific game.
Rules:
- Fail Rate is defined as: (attempts_failed / attempts_total).
- Levels are sequential (1, 2, 3...).
- Level 1 cannot have a previous level; ignore it for spike detection.
- A spike is defined as: Current Fail Rate >= 10 * Previous Level Fail Rate.
- If the previous level had a Fail Rate of 0, any current level with a Fail Rate > 0 should be considered a spike.
- Output columns: game_id, level_number, current_fail_rate, previous_fail_rate.
- Result must be sorted by game_id ascending, then level_number ascending.
Tests your understanding of
Window Functions, LAG and Arithmetic Operations
Input Tables
level_stats
| game_id(INTEGER) | level_number(INTEGER) | attempts_total(INTEGER) | attempts_failed(INTEGER) |
|---|---|---|---|
| 1 | 1 | 1000 | 50 |
| 1 | 2 | 900 | 45 |
| 1 | 3 | 800 | 500 |
| 1 | 4 | 300 | 20 |
| 2 | 1 | 500 | 5 |
| 2 | 2 | 450 | 90 |
| 2 | 3 | 300 | 10 |
| 3 | 1 | 100 | 0 |
| 3 | 2 | 100 | 15 |
Expected Output
| game_id(INTEGER) | level_number(INTEGER) | current_fail_rate(DECIMAL) | previous_fail_rate(DECIMAL) |
|---|---|---|---|
| 1 | 3 | 0.625 | 0.05 |
| 2 | 2 | 0.2 | 0.01 |
| 3 | 2 | 0.15 | 0 |
Tags
HardWindow FunctionsLAGArithmetic Operations
25-35 min
38%