Business Context
Replit runs multiplayer coding sessions where multiple users collaborate in real time on the same Repl. At peak, the platform supports millions of daily active users and hundreds of thousands of concurrent sessions. Product and Trust & Safety teams need reliable analytics to understand who was active, when they were active, and what actions they performed (editing, running code, chat, file operations). These metrics drive decisions like collaboration UX improvements, abuse detection, and capacity planning.
You’re given a simplified event schema that tracks session membership and user actions. Events arrive from clients and servers and may be slightly out of order; however, event_ts is the canonical timestamp.
Task
Write a SQL query that produces a per-user activity summary per session for a given day.
Assume you are analyzing activity for 2025-01-15 (UTC).
Requirements
Return one row per (session_id, user_id) that had at least one event on 2025-01-15, with:
session_id, user_idfirst_event_ts: earliest event timestamp for that user in that session on that daylast_event_ts: latest event timestamp for that user in that session on that dayevent_count: total number of events for that user in that session on that dayactive_minutes: number of distinct minutes (UTC) in which the user produced at least one event in that session on that daytop_action_type: the most frequent action_type for that user in that session on that day; break ties by choosing the lexicographically smallest action_type
Order results by session_id, then event_count descending, then user_id.
Table Definitions
users
| column | type | description |
|---|
| user_id | INT | Unique user identifier |
| handle | VARCHAR(50) | Public username |
| created_at | TIMESTAMP | Account creation time |
repl_sessions
| column | type | description |
|---|
| session_id | VARCHAR(20) | Unique multiplayer session id |
| repl_id | VARCHAR(20) | Underlying Repl identifier |
| started_at | TIMESTAMP | Session start time |
| ended_at | TIMESTAMP | Session end time (nullable if ongoing) |
session_participants
| column | type | description |
|---|
| session_id | VARCHAR(20) | Session id |
| user_id | INT | User id |
| joined_at | TIMESTAMP | When user joined |
| left_at | TIMESTAMP | When user left (nullable) |
| role | VARCHAR(20) | e.g., owner, collaborator, viewer |
session_events
| column | type | description |
|---|
| event_id | BIGINT | Unique event id |
| session_id | VARCHAR(20) | Session id |
| user_id | INT | Actor user id |
| event_ts | TIMESTAMP | Event timestamp (UTC) |
| action_type | VARCHAR(30) | e.g., edit, run, chat, file_create |
| action_metadata | VARCHAR(255) | JSON-ish string with details |
Sample Data
users
| user_id | handle | created_at |
|---|
| 101 | aria | 2024-06-01 10:00:00 |
| 102 | ben | 2024-07-12 09:30:00 |
| 103 | chloe | 2024-08-20 14:15:00 |
repl_sessions
| session_id | repl_id | started_at | ended_at |
|---|
| s1 | r100 | 2025-01-15 10:00:00 | 2025-01-15 11:00:00 |
| s2 | r200 | 2025-01-15 23:50:00 | 2025-01-16 00:20:00 |
| s3 | r300 | 2025-01-14 23:40:00 | 2025-01-15 00:10:00 |
session_participants
| session_id | user_id | joined_at | left_at | role |
|---|
| s1 | 101 | 2025-01-15 10:00:10 | 2025-01-15 11:00:00 | owner |
| s1 | 102 | 2025-01-15 10:05:00 | 2025-01-15 10:55:00 | collaborator |
| s2 | 103 | 2025-01-15 23:50:10 | 2025-01-16 00:20:00 | owner |
session_events
| event_id | session_id | user_id | event_ts | action_type | action_metadata |
|---|
| 9001 | s1 | 101 | 2025-01-15 10:00:30 | edit | {"file":"main.py"} |
| 9002 | s1 | 101 | 2025-01-15 10:01:05 | edit | {"file":"main.py"} |
| 9003 | s1 | 101 | 2025-01-15 10:02:10 | run | {"exit_code":0} |
| 9004 | s1 | 102 | 2025-01-15 10:05:30 | chat | {"len":12} |
| 9005 | s1 | 102 | 2025-01-15 10:05:50 | chat | {"len":5} |
Expected Output (for sample data)
| session_id | user_id | first_event_ts | last_event_ts | event_count | active_minutes | top_action_type |
|---|
| s1 | 101 | 2025-01-15 10:00:30 | 2025-01-15 10:02:10 | 3 | 3 | edit |
| s1 | 102 | 2025-01-15 10:05:30 | 2025-01-15 10:05:50 | 2 | 1 | chat |
