+---------------+---------+
|Column Name |Type|+---------------+---------+
| user_id | int || session_id | int || activity_date | date || activity_type | enum |+---------------+---------+
This table may have duplicate rows.
The activity_type columnis an ENUM (category) oftype ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that eachsession belongs to exactly one user.
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.
Input:
Activity table:+---------+------------+---------------+---------------+| 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 ||3|5|2019-07-21| open_session ||3|5|2019-07-21| scroll_down ||3|5|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.
WITH myquery AS (
SELECT user_id,
session_id
FROM Activity
WHERE activity_date BETWEEN'2019-06-28'AND'2019-07-27'GROUPBY user_id, session_id
)
SELECT IFNULL(ROUND(COUNT(*) /COUNT(DISTINCT user_id), 2), 0.00) average_sessions_per_user
FROM myquery;