Practice Interview Questions
| Question | Status |
|---|---|
Write a query to identify the user with the highest average session duration, but only include users who have used more than 3 features in the last 30 days. Assuming that today's date is 2025-04-01.
| session_id | user_id | login_time | logout_time |
|---|---|---|---|
| 6 | 1 | 2025-03-03 08:00:00 | 2025-03-03 08:21:00 |
| 2 | 5 | 2025-03-05 08:00:00 | 2025-03-05 09:34:00 |
| 4 |
| 2 |
| 2025-03-05 11:00:00 |
| 2025-03-05 12:22:00 |
| 2 | 7 | 2025-03-06 11:00:00 | 2025-03-06 13:00:00 |
| 5 | 1 | 2025-03-06 17:00:00 | 2025-03-06 18:04:00 |
| usage_id | user_id | feature_name | usage_count | last_used |
|---|---|---|---|---|
| 1 | 1 | analytics | 15 | 2025-04-14 16:08:32 |
| 2 | 2 | team_chat | 23 | 2025-03-09 19:09:01 |
| 3 | 1 | notifications | 45 | 2025-03-08 08:14:03 |
| 4 | 1 | task_management | 10 | 2025-03-09 19:02:47 |
| 5 | 1 | file_sharing | 20 | 2025-04-13 19:07:05 |
The subquery will calculate the average session duration per user from sessions, while the main query filters users who have accessed more than 3 features in the last 30 days using feature_usage.
Write a query to identify the user with the highest average session duration, but only include users who have used more than 3 features in the last 30 days. Assuming that today's date is 2025-04-01.
| session_id | user_id | login_time | logout_time |
|---|---|---|---|
| 6 | 1 | 2025-03-03 08:00:00 | 2025-03-03 08:21:00 |
| 2 | 5 | 2025-03-05 08:00:00 | 2025-03-05 09:34:00 |
| 4 | 2 | 2025-03-05 11:00:00 | 2025-03-05 12:22:00 |
| 2 | 7 | 2025-03-06 11:00:00 | 2025-03-06 13:00:00 |
| 5 | 1 | 2025-03-06 17:00:00 | 2025-03-06 18:04:00 |
| usage_id | user_id | feature_name | usage_count | last_used |
|---|---|---|---|---|
| 1 | 1 | analytics | 15 | 2025-04-14 16:08:32 |
| 2 | 2 | team_chat | 23 | 2025-03-09 19:09:01 |
| 3 | 1 | notifications | 45 | 2025-03-08 08:14:03 |
| 4 |
The subquery will calculate the average session duration per user from sessions, while the main query filters users who have accessed more than 3 features in the last 30 days using feature_usage.
| 1 |
| task_management |
| 10 |
| 2025-03-09 19:02:47 |
| 5 | 1 | file_sharing | 20 | 2025-04-13 19:07:05 |