This Leetcode problem User Activity for the Past 30 Days II LeetCode Solution is done in SQL.
List of all LeetCode Solution
User Activity for the Past 30 Days II LeetCode Solution
Table of Contents
Problem Statement
| Column Name | Type |
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
ActivityThis table may have duplicate rows.
The activity_type column is an ENUM (category) 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 result format is in the following example.
Example 1:
Input:
| 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 |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
Output:
| average_sessions_per_user |
| 1.33 |
Explanation: 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.
User Activity for the Past 30 Days II LeetCode Solution MySQL
select
round(
ifnull(
sum(sessions) / count(user_id),
0
),
2
) as average_sessions_per_user
from
(
select
distinct user_id,
count(distinct session_id) as sessions
from
Activity
where
activity_date between '2019-06-28'
and '2019-07-27'
group by
user_id
having
count(*) >= 1
) as u;Code language: SQL (Structured Query Language) (sql)