Last updated on October 10th, 2024 at 01:52 pm

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

Problem Statement

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:

Output:

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;