User Activity for the Past 30 Days II LeetCode Solution

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

User Activity for the Past 30 Days II LeetCode Solution

Problem Statement

Column NameType
user_idint
session_idint
activity_datedate
activity_typeenum
Table: 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_idsession_id activity_dateactivity_type
112019-07-20open_session
112019-07-20scroll_down
112019-07-20end_session
242019-07-20open_session
242019-07-21send_message
242019-07-21end_session
322019-07-21open_session
322019-07-21send_message
322019-07-21end_session
432019-06-25open_session
432019-06-25end_session
Activity table:

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;
Scroll to Top