select
person1,
person2,
sum(calls) as call_count,
sum(duration) as total_duration
from (
select
from_id as person1,
to_id as person2,
1 as calls,
duration
from Calls
where from_id < to_id
union all
select
to_id as person1,
from_id as person2,
1 as calls,
duration
from Calls
where to_id < from_id
) c
group by 1,2
select
a.machine_id,
round(sum(a.duration)/count(distinct a.process_id),3) as processing_time
from(
select
s.machine_id,
s.process_id,
(e.end_time- s.start_time) as duration
from(
select
machine_id,
process_id,
timestamp as start_time
from Activity
where activity_type = 'start'
) s
left join (
select
machine_id,
process_id,
timestamp as end_time
from Activity
where activity_type = 'end'
) e
on (s.machine_id = e.machine_id and s.process_id = e.process_id )
) a
group by 1
order by 1
select p.name,
ifnull(sum(i.rest),0) as rest,
ifnull(sum(i.paid),0) as paid,
ifnull(sum(i.canceled),0) as canceled,
ifnull(sum(i.refunded),0) as refunded
from Product p
inner join Invoice i
on p.product_id = i.product_id
group by 1
order by 1
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
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
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
select a.id as ids
from (
select 1 as id union
select 2 as id union
select 3 as id union
select 4 as id union
select 5 as id union
select 6 as id union
select 7 as id union
select 8 as id union
select 9 as id union
select 10 as id union
select 11 as id union
select 12 as id union
select 13 as id union
select 14 as id union
select 15 as id union
select 16 as id union
select 17 as id union
select 18 as id union
select 19 as id union
select 20 as id union
select 21 as id union
select 22 as id union
select 23 as id union
select 24 as id union
select 25 as id union
select 26 as id union
select 27 as id union
select 28 as id union
select 29 as id union
select 30 as id union
select 31 as id union
select 32 as id union
select 33 as id union
select 34 as id union
select 35 as id union
select 36 as id union
select 37 as id union
select 38 as id union
select 39 as id union
select 40 as id union
select 41 as id union
select 42 as id union
select 43 as id union
select 44 as id union
select 45 as id union
select 46 as id union
select 47 as id union
select 48 as id union
select 49 as id union
select 50 as id union
select 51 as id union
select 52 as id union
select 53 as id union
select 54 as id union
select 55 as id union
select 56 as id union
select 57 as id union
select 58 as id union
select 59 as id union
select 60 as id union
select 61 as id union
select 62 as id union
select 63 as id union
select 64 as id union
select 65 as id union
select 66 as id union
select 67 as id union
select 68 as id union
select 69 as id union
select 70 as id union
select 71 as id union
select 72 as id union
select 73 as id union
select 74 as id union
select 75 as id union
select 76 as id union
select 77 as id union
select 78 as id union
select 79 as id union
select 80 as id union
select 81 as id union
select 82 as id union
select 83 as id union
select 84 as id union
select 85 as id union
select 86 as id union
select 87 as id union
select 88 as id union
select 89 as id union
select 90 as id union
select 91 as id union
select 92 as id union
select 93 as id union
select 94 as id union
select 95 as id union
select 96 as id union
select 97 as id union
select 98 as id union
select 99 as id union
select 100 as id
) a
left join Customers c
on a.id = c.customer_id
where (
c.customer_id is null
and a.id <= (select max(customer_id) from Customers)
)
order by 1
1623. All Valid Triplets That Can Represent a Country
select
a.student_name as member_A,
b.student_name as member_B,
c.student_name as member_C
from SchoolA a
cross join SchoolB b
cross join SchoolC c
where
a.student_name <> b.student_name
and b.student_name <> c.student_name
and a.student_name <> c.student_name
and a.student_id <> b.student_id
and b.student_id <> c.student_id
and a.student_id <> c.student_id
select
r.contest_id,
round((count(distinct r.user_id) / u.users) *100,2) as percentage
from Register r
join (
select
count(distinct user_id) as users
from Users
) u
on 1=1
group by 1
order by 2 desc, 1
select
v.customer_id,
count(distinct v.visit_id) as count_no_trans
from visits v
left join transactions s
on v.visit_id = s.visit_id
where s.amount is null
group by 1
select
name,
balance
from(
select
t.account,
u.name,
sum(amount) as balance
from transactions t
left join users u
on t.account = u.account
group by 1,2
having sum(amount) > 10000
) a
1596. The Most Frequently Ordered Products for Each Customer
select
b.customer_id,
b.product_id,
p.product_name
from(
select
customer_id,
product_id,
order_count,
rank() over(partition by customer_id order by order_count desc) as r
from(
select
o.customer_id,
o.product_id,
count(distinct o.order_id) as order_count
from Orders o
group by 1,2
having count(distinct o.order_id) >= 1 /* at least 1 order */
) a
) b
left join (
select
product_id,
product_name
from Products
group by 1,2
) p
on b.product_id = p.product_id
where b.r = 1
select
seller_name
from Seller s
/* sellers who haven't made any sales in 2020 */
where seller_id in (
select
seller_id
from Orders
group by 1
having max(year(sale_date)) <> 2020
and min(year(sale_date)) <> 2020
)
/* sellers who haven't sold nothing */
or seller_id not in (
select distinct
seller_id
from Orders
)
order by 1