Total Time Spent per Day
ASKED IN INTERVIEWSlack
Problem Statement
Slack tracks user activity in sessions. You are given a table of activity logs where each entry represents a single session duration in minutes.
Write an SQL query to calculate the total time spent by each user on each day.
Rules:
- A user can have multiple activity logs on the same day.
- Output columns: user_id, day, total_minutes.
- Results must be sorted by user_id in ascending order, then by day in ascending order.
Tests your understanding of
Grouping, Aggregation and Date Conversion
Input Tables
ActivityLogs
| log_id(INTEGER) | user_id(INTEGER) | activity_timestamp(TIMESTAMP) | duration_minutes(INTEGER) |
|---|---|---|---|
| 1 | 1 | 2026-02-09 09:00:00 | 30 |
| 2 | 1 | 2026-02-09 14:00:00 | 45 |
| 3 | 2 | 2026-02-09 10:00:00 | 120 |
| 4 | 1 | 2026-02-08 11:00:00 | 20 |
Users
| user_id(INTEGER) | user_name(VARCHAR) | workspace_id(INTEGER) |
|---|---|---|
| 1 | Alice | 101 |
| 2 | Bob | 101 |
Workspaces
| workspace_id(INTEGER) | workspace_name(VARCHAR) |
|---|---|
| 101 | Engineering_Dept |
Expected Output
| user_id(INTEGER) | day(DATE) | total_minutes(INTEGER) |
|---|---|---|
| 1 | 2026-02-08 | 20 |
| 1 | 2026-02-09 | 75 |
| 2 | 2026-02-09 | 120 |
Tags
EasyASKED IN INTERVIEWGroupingAggregationDate Conversion
10-15 min
76%