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;

All Problems

All Solutions