Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1142.html
1142. User Activity for the Past 30 Days II
Level
Easy
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.
## Solution
To obtain the average sessions per user, count the number of different sessions and the number of different users, and divide them to obtain the average.
Use `ifnull` to prevent dividing by 0 and use `round` to round the result to required number of decimal places.
```sql
# Write your MySQL query statement below
select ifnull(round(count(distinct session_id) / count(distinct user_id), 2), 0) as average_sessions_per_user
from Activity
where datediff('2019-07-27', activity_date) < 30;