Ubisoft Interview Questions

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:

1. week_start- the Monday of that calendar week
   
2. 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

purchases table

Output:

Example:

Input:

game_sessions table

purchases table

Output:

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