1142 User Activity for the Past 30 Days II

Description

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.

Problem Statement

Write a solution 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:

• Activity table:

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.

Solution

This problem requires to find activities for the last 30 days. This can be done using WHERE activity_date > DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date < '2019-07-27' clause. You can find average session count by finding distinct sessions and dividing them by distinct count of users.

1SELECT COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id)
2    FROM Activity
3    WHERE activity_date > DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date < '2019-07-27';

Next, you need to round the average sessions to 2-decimal places. Here, you can use ROUND() function. The next caveat is that if ther eare no sessions in the last 30 days, you might get result as NULL. So, you need to check for NULL and if there are no sessions then you can replace them with 0 using IFNULL() function.

1SELECT 
2    IFNULL(
3        ROUND(
4            COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id),
5            2
6        ),
7        0) AS average_sessions_per_user
8    FROM Activity
9    WHERE activity_date > DATE_SUB('2019-07-27', INTERVAL 30 DAY) AND activity_date < '2019-07-27';