1142. User Activity for the Past 30 Days II
https://leetcode.com/problems/user-activity-for-the-past-30-days-ii
Description
Table: Activity
+---------------+---------+
| 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:
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.
ac
Last updated
Was this helpful?