## 1635. Hopper Company Queries I

Question:

Solution:

``````select
d.month_num as month,
ifnull(d.active_drivers,0) as active_drivers,
ifnull(a.accepted_rides,0) as accepted_rides
from(
select
m.month,
m.month_num,
count(distinct d.driver_id) as active_drivers
from(
select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
union
select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
union
select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
union
select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
union
select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
union
select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
union
select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
union
select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
union
select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
union
select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
union
select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
union
select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num
) m
left join (
select
driver_id,
DATE_FORMAT(join_date,'%Y-%m') as join_month
from Drivers
) d
on m.month >= d.join_month
group by 1,2
) d
left join (
select
DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
count(distinct r.ride_id) as accepted_rides
from Rides r
join AcceptedRides a
on r.ride_id = a.ride_id
group by 1
having count(distinct r.ride_id) > 0
) a
on d.month = a.ride_month
order by 1``````

## 1645. Hopper Company Queries II

Question:

Solution:

``````select
d.month_num as month,
case
when ifnull(d.active_drivers,0) = 0 then round(0,2)
when ifnull(d.active_drivers,0) >0 then round((ifnull(a.accepted_rides,0)/ifnull(d.active_drivers,0))*100,2)
end as working_percentage
from(
select
m.month,
m.month_num,
count(distinct d.driver_id) as active_drivers
from(
select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
union
select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
union
select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
union
select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
union
select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
union
select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
union
select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
union
select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
union
select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
union
select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
union
select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
union
select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num
) m
left join (
select
driver_id,
DATE_FORMAT(join_date,'%Y-%m') as join_month
from Drivers
) d
on m.month >= d.join_month
group by 1,2
) d
left join (
select
DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
count(distinct case when r.ride_id is not null then a.driver_id else null end) as accepted_rides
from Rides r
join AcceptedRides a
on r.ride_id = a.ride_id
group by 1
having count(distinct r.ride_id) >0
) a
on d.month = a.ride_month
order by 1``````

## 1651. Hopper Company Queries III

Question:

Solution:

``````select
month,
average_ride_distance,
average_ride_duration
from(
select
month,
round(avg(ride_distance) over(order by month asc rows between current row and 2 following),2) as average_ride_distance,
round(avg(ride_duration) over(order by month asc rows between current row and 2 following),2) as average_ride_duration
from(
select
b.month_num as month,
sum(ride_distance) as ride_distance,
sum(ride_duration) as ride_duration
from(
select
m.month,
m.month_num,
d.driver_id,
ifnull(r.ride_distance,0) as ride_distance,
ifnull(r.ride_duration,0) as ride_duration
from(
select DATE_FORMAT('2020-01-01','%Y-%m') as month, 1 as month_num
union
select DATE_FORMAT('2020-02-01','%Y-%m') as month, 2 as month_num
union
select DATE_FORMAT('2020-03-01','%Y-%m') as month, 3 as month_num
union
select DATE_FORMAT('2020-04-01','%Y-%m') as month, 4 as month_num
union
select DATE_FORMAT('2020-05-01','%Y-%m') as month, 5 as month_num
union
select DATE_FORMAT('2020-06-01','%Y-%m') as month, 6 as month_num
union
select DATE_FORMAT('2020-07-01','%Y-%m') as month, 7 as month_num
union
select DATE_FORMAT('2020-08-01','%Y-%m') as month, 8 as month_num
union
select DATE_FORMAT('2020-09-01','%Y-%m') as month, 9 as month_num
union
select DATE_FORMAT('2020-10-01','%Y-%m') as month, 10 as month_num
union
select DATE_FORMAT('2020-11-01','%Y-%m') as month, 11 as month_num
union
select DATE_FORMAT('2020-12-01','%Y-%m') as month, 12 as month_num
) m
left join (
select
driver_id,
DATE_FORMAT(join_date,'%Y-%m') as join_month
from Drivers
) d
on m.month >= d.join_month
left join (
select
a.driver_id,
DATE_FORMAT(r.requested_at,'%Y-%m') as ride_month,
sum(a.ride_distance) as ride_distance,
sum(a.ride_duration) as ride_duration
from AcceptedRides a
left join Rides r
on a.ride_id = r.ride_id
group by 1,2
) r
on (m.month = r.ride_month and d.driver_id = r.driver_id )
) b
group by 1
) c
) d
where month <= 10
order by 1``````