User Activity For The Past 30 Days II Problem
Description
LeetCode Problem 1142.
Table: Activity
1
2
3
4
5
6
7
8
9
10
11
12
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.
Write an SQL query to find the average number of sessions per user for a period of 30 days ending 2019-07-27 inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period.
The query result format is in the following example:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 3 | 5 | 2019-07-21 | open_session |
| 3 | 5 | 2019-07-21 | scroll_down |
| 3 | 5 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
Result table:
+---------------------------+
| average_sessions_per_user |
+---------------------------+
| 1.33 |
+---------------------------+
User 1 and 2 each had 1 session in the past 30 days while user 3 had 2 sessions so the average is (1 + 1 + 2) / 3 = 1.33.
MySQL Solution
1
2
3
select round(ifnull(count(distinct session_id)/count(distinct user_id), 0),2) as average_sessions_per_user
from Activity
where datediff('2019-07-27', activity_date) <= 29
LeetCode Database - Easy
LeetCode 1050
LeetCode 1068
LeetCode 1069
LeetCode 1075
LeetCode 1076
LeetCode 1082
LeetCode 1083
LeetCode 1084
LeetCode 1113
LeetCode 1141
LeetCode 1142
LeetCode 1148
LeetCode 1173
LeetCode 1179
LeetCode 1211
LeetCode 1241
LeetCode 1251
LeetCode 1280
LeetCode 1294
LeetCode 1303
LeetCode 1322
LeetCode 1327
LeetCode 1350
LeetCode 1378
More LeetCode Database
MySQL Tutorials