Track Your Most Valuable Gamers
Start Timer
0:00:00
You’re an analyst at Ubisoft Gaming. Leadership wants to track Weekly Active Paying Users (WAPU), players who record at least one game session and at least one in-app purchase in the same calendar week.
Write a SQL query that returns, for each week in the dataset:
week_start- the Monday of that calendar week
wapu- the number of distinct users who qualify as WAPU in that week
Note: Assume all timestamps are in UTC. Output should be ordered by week_start ascending.
Schema:
Input:
game_sessions table
| Column | Type |
|---|---|
| user_id | INTEGER |
| session_id | INTEGER |
| session_start | TIMESTAMP |
| country | VARCHAR |
purchases table
| Column | Type |
|---|---|
| purchase_id | INTEGER |
| user_id | INTEGER |
| purchase_time | TIMESTAMP |
| amount | DECIMAL(10,2) |
Output:
| Column | Type |
|---|---|
| week_start | TIMESTAMP |
| wapu | INTEGER |
Example:
Input:
game_sessions table
| user_id | session_id | session_start | country |
|---|---|---|---|
| 1 | 101 | 2023-10-02 | USA |
| 2 | 102 | 2023-10-03 | USA |
| 1 | 103 | 2023-10-04 | USA |
| 3 | 104 | 2023-10-05 | USA |
purchases table
| purchase_id | user_id | purchase_time | amount |
|---|---|---|---|
| 201 | 1 | 2023-10-02 | 5.99 |
| 202 | 2 | 2023-10-03 | 10.00 |
| 203 | 1 | 2023-10-04 | 2.99 |
| 204 | 3 | 2023-10-05 | 7.49 |
Output:
| Column | Type |
|---|---|
| week_start | 2023-10-02 |
| wapu | 3 |
Explanation:
All sessions and purchases occur during the same calendar week:
- Week start: Monday 2023-10-02
Users who qualify as WAPU (≥1 session AND ≥1 purchase in the same week):
- User 1 - sessions on Oct 2 & 4; purchases on Oct 2 & 4
- User 2 - session on Oct 3; purchase on Oct 3
- User 3 - session on Oct 5; purchase on Oct 5
Total WAPU = 3 users
.
.
.
.
.
.
.
.
.
Comments