select * ,rank()over(partition by city order by on_time_rate desc,avg_minutes_7d) rank_in_city from ( select parcel.courier_id ,courier_name ,city ,count(1) orders_7d ,sum(case when promised_minutes >= timestampdiff(minute,shipped_ts,delivered_ts) then 1 else 0 end) on_time_7d ,ifnull(ROUND(sum(c...

select * from ( select post.author_id ,author_name ,count(1) posts_30d ,sum(like_cnt) likes_30d ,ifnull(ROUND(sum(like_cnt) / count(1), 2),0) avg_likes_30d from author join post on post.author_id = author.author_id where date_format(publish_ts,'%Y-%m-%d') between date_sub((select date_format(publish...

select customers.customer_id ,customer_name ,count(distinct orders.order_id) feb_2024_order_count ,ifnull(round(sum(qty*price),2),0) feb_2024_total_amount ,ifnull(round(ifnull(round(sum(qty*price),2),0)/count(distinct orders.order_id),2),0) feb_2024_avg_order_amount ,min(order_date) feb_2024_first_o...

select book_id ,book_title ,sum(case when date_format(borrow_date ,'%Y-%m') = '2023-02' then 1 else 0 end) feb_2023_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-02' then 1 else 0 end) feb_2024_borrows ,sum(case when date_format(borrow_date ,'%Y-%m') = '2024-01' then 1 else 0 end)...