[1142] User Activity for the Past 30 Days II

Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity_type column is an ENUM 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 query result format is in the following example.

 

Example 1:

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.

 

문제 조건 

- 7월 27일을 포함하여 그 전 30일의 데이터 중, 사용자 당 평균 세션 수를 소수점 이하 2자리로 반올림하여 출력하시오

- 유저에 대해 계산 하려는 세션은 해당 기간에 하나 이상 활동이 있는 세션. 

 

정답 쿼리

- 2019년을 포함하는 그 전 30일 데이터 중, user_id 당 unique한 session_id 값을 구하는 가상 테이블을 만듦

- 그 다음 전체 유저 수(user_id)에 유저 당 session_id 값을 나눠준다.

- 소숫점 둘째 자리 까지 표시하는 round함수를 썼는데, 만약 null값이 있을 경우를 포함하여 2째자리 까지 표시해야 하기 때문에 if null, 0.00을 추가 했음.  

# date_sub 
with temp as (
    select user_id
            ,count(distinct session_id) as s_id
     from activity
     where 
        (activity_date between date_sub('2019-07-27', interval 29 day) and '2019-07-27')
     and activity_type is not null 
     group by user_id) 
    
select ifnull(round(sum(s_id)/count(user_id),2),0.00) as average_sessions_per_user --ifnull 뭐야?? 
from temp

# datediff 
with temp as (
    select user_id
            ,count(distinct session_id) as s_id
     from activity
     where 
        (datediff('2019-07-27',activity_date)<30 and activity_date <= '2019-07-27')
     and activity_type is not null 
     group by user_id) 
    
select ifnull(round(sum(s_id)/count(user_id),2),0.00) as average_sessions_per_user 
from temp

나는 멍텅구리.. discussion 1위 쿼리

select ifnull(round(count(distinct session_id)/count(distinct user_id),2),0.00) as "average_sessions_per_user"
from activity 
WHERE activity_date >= '2019-06-28' and activity_date <= '2019-07-27';

 

 

ifnull(x,0.00000) 이렇게 하면 소숫점 자리를 지정해주는게 되는건가? 

with temp as (
    select user_id
            ,count(distinct session_id) as s_id
            ,count(user_id) as cnt_id
     from activity
     where 
        (activity_date between date_sub('2019-07-27', interval 29 day) and '2019-07-27')
     and activity_type is not null 
     group by user_id) 
    

select ifnull(round(sum(s_id)/cnt_id,2),0.00000) as average_sessions_per_user 
from temp

해당 쿼리를 돌리니까 

# {"headers": ["average_sessions_per_user"], "values": [[1.33000]]}  이렇게 나옴

 

반대로 ifnull(x,0.00) 하니까 두 자리 수 까지 나옴. 왜지? 

 

아, 바로 밖에 ifnull이 있어서 round까지 다 한 값에 0.00000이 들어가서 소숫점을 나타내는 형식에 변화가 있는 것인가? 

round(ifnull(round(sum...),0.0000),2) 하니까 제대로 나온다!