Your question is Repl Session Activity Timeline Analytics. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
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).
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_typeOrder results by session_id, then event_count descending, then user_id.
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| handle | VARCHAR(50) | Public username/handle |
| created_at | TIMESTAMP | Account creation timestamp |
| Column | Type | Description |
|---|---|---|
| session_idPK | 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; NULL if still active |
| Column | Type | Description |
|---|---|---|
| session_idPK | VARCHAR(20) | Session id |
| user_idPK | INT | User id |
| joined_at | TIMESTAMP | When the user joined the session |
| left_at | TIMESTAMP | When the user left the session; NULL if still present |
| role | VARCHAR(20) | Participant role (owner/collaborator/viewer) |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Unique event id |
| session_id | VARCHAR(20) | Session id where the event occurred |
| user_id | INT | User who performed the action |
| event_ts | TIMESTAMP | Event timestamp in UTC |
| action_type | VARCHAR(30) | Action category (edit/run/chat/etc.) |
| action_metadata | VARCHAR(255) | Additional metadata (often JSON) |