select machine_id,
round(2*avg(if(activity_type = 'start',-1,1)*timestamp),3) as processing_time
from Activity
group by machine_id;

select t.student_id, student_name, t.subject_name, count(e.subject_name) as attended_exams
from (select * from Students cross join Subjects ) t
left join Examinations e
on e.student_id = t.student_id and  e.subject_name = t.subject_name
group by t.student_id, student_name, t.subject_name
order by t.student_id, t.subject_name;

select e1.name
from Employee e1
join Employee e2
on e1.id = e2.managerId
group by e1.id
having count(*) >= 5;

select p.product_id,
round(ifnull(sum(p.price * u.units) / sum(u.units), 0), 2) as average_price
from Prices p
left join UnitsSold u 
on p.product_id = u.product_id and purchase_date between start_date and end_date
group by p.product_id;

select contest_id, round(count(user_id)/(select count(*) from Users)* 100, 2) as percentage
from Register r
group by contest_id
order by percentage desc, contest_id;

select date_format(trans_date,'%Y-%m') as month, country, 
count(id) as trans_count, 
sum(if(state = 'approved', 1, 0)) as approved_count, 
sum(amount) as trans_total_amount,
sum(if(state = 'approved', 1, 0) * amount) as approved_total_amount
from Transactions
group by month, country;

select 
round(avg(if(order_date = customer_pref_delivery_date, 1, 0)) * 100, 2)
as immediate_percentage
from Delivery
where (customer_id, order_date) in 
(select customer_id, min(order_date) from Delivery group by customer_id); #找出首次订单

select 
round(avg(event_date is not null), 2) as fraction
from 
    (select player_id, min(event_date) as first_login
    from Activity
    group by player_id) fl #首次登录
left join Activity a
on fl.player_id = a.player_id and datediff(a.event_date, fl.first_login) = 1;

select
    (select num
    from MyNumbers
    group by num
    having count(num) = 1
    order by num desc
    limit 1)
as num;

select customer_id
from customer
group by customer_id
having count(distinct(product_key)) = 
(select count(product_key) from product);