## 1112. Highest Grade for Each Student

Question:

Solution:

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

## 1113. Reported Posts

Question:

Solution:

``````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``````

Question:

Solution:

``````select distinct
from(
select
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``````

## 1127. User Purchase Platform

Question:

Solution:

``````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``````