Last updated on August 3rd, 2024 at 11:02 pm
This Leetcode problem User Activity for the Past 30 Days II LeetCode Solution is done in SQL.
List of all LeetCode Solution
Level of Question
Easy
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 |
Activity
This 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.
1. 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;