Day: October 2, 2019

select 
student_id, 
course_id, 
grade
from (
    select 
    student_id, 
    course_id, 
    grade,
    row_number() over(partition by student_id order by grade desc, course_id) as r
    from Enrollments
) a
where r = 1

select 
extra as report_reason, 
count(distinct post_id) as report_count
from Actions
where action = 'report'
and action_date = date_sub('2019-07-05', interval 1 day)
group by 1

select distinct 
b.business_id 
from(
    select 
    e.business_id, 
    count(distinct e.event_type) as number_of_event_type
    from Events e
    left join (
        select 
        event_type, 
        avg(occurences) as avg_occurences
        from Events
        group by 1
    ) a 
        on e.event_type = a.event_type
    where e.occurences > a.avg_occurences
    group by 1
    having count(distinct e.event_type) > 1
) b
order by 1

select 
a.spend_date, 
a.platform, 
ifnull(total_amount,0) as total_amount, 
ifnull(total_users,0) as total_users
from(
    /* create a view with spend date across desktop, mobile and combined platforms */
    select distinct 
    s.spend_date, 
    p.platform 
    from Spending s
        cross join (
            select 'desktop' as platform
            union
            select 'mobile' as platform
            union
            select 'both' as platform
        ) p
    ) a
    left join (
        select 
        spend_date, 
        platform, 
        sum(total_amount) as total_amount, 
        count(distinct user_id) as total_users
        from(
            select 
            spend_date, 
            user_id, 
            case 
                when sum(mobile) > 0 and sum(desktop) > 0 then 'both' 
                when sum(desktop) > 0 and sum(mobile) = 0 then 'desktop'
                when sum(mobile) > 0 and sum(desktop) = 0 then 'mobile'
            end as platform,
            case 
                when sum(mobile) > 0 and sum(desktop) > 0 then sum(mobile) + sum(desktop)
                when sum(desktop) > 0 and sum(mobile) = 0 then sum(desktop)
                when sum(mobile) > 0 and sum(desktop) = 0 then sum(mobile)
            end as total_amount
            from(
                select 
                spend_date, 
                user_id, 
                amount as mobile, 
                0 as desktop
                from Spending
                where platform = 'mobile'
                union
                select 
                spend_date, 
                user_id, 
                0 as desktop, 
                amount as desktop
                from Spending
                where platform = 'desktop'
            ) s
        group by 1,2
        )a
        group by 1,2
    ) b 
        on (a.spend_date = b.spend_date and a.platform = b.platform)
order by 1,2