masteranalytics Uncategorized

Leetcode SQL (#1132, #1141 – #1142)

1132. Reported Posts II

Question:

https://leetcode.com/problems/reported-posts-ii/

Solution:

select 
round(avg(rate)*100,2) as average_daily_percent
from(
    select 
    b.action_date, 
    count(distinct removed_post_id)/count(distinct post_id) as rate
    from(
        select 
        a.action_date, 
        a.post_id, 
        r.post_id as removed_post_id
        from(
            select a.*
            from Actions a
            where a.action= 'report' 
            and a.extra = 'spam'
        ) a
        left join Removals r 
            on a.post_id= r.post_id
    ) b
    group by 1
 ) c

1141. User Activity for the Past 30 Days I

Question:

https://leetcode.com/problems/user-activity-for-the-past-30-days-i/

Solution:

select 
activity_date as day, 
count(distinct user_id) as active_users
from Activity
where activity_date <= '2019-07-27' 
/* activity date >= '2019-06-27' */
and activity_date >= date_sub('2019-07-27', interval 29 day)
group by 1
order by 1

1142. User Activity for the Past 30 Days II

Question:

https://leetcode.com/problems/user-activity-for-the-past-30-days-ii/

Solution:

select 
round(ifnull(sum(sessions)/count(distinct user_id),0),2) as average_sessions_per_user
from(
    select 
    user_id, 
    count(distinct session_id) as sessions
    from activity
    where activity_date <='2019-07-27' 
    and activity_date >= date_sub('2019-07-27', interval 29 day)
    group by 1
) a