with t as( select i.video_id, avg(timestampdiff(second,start_time,end_time)>=i.duration) full_play_rate, sum(if_like) like_cnt, count(comment_id) comment_cnt, sum(if_retweet) retweet_cnt, datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) nonplayed_days from tb_vide...

with t1 as( select i.tag, date_format(p.start_time,'%Y-%m-%d') as dt, sum(p.if_like) as like_cnt, sum(p.if_retweet) as retweet_cnt from tb_user_video_log p left join tb_video_info i using(video_id) where date_format(p.start_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03' group by i.tag,date_f...

with t1 as( select i.author, date_format(p.start_time,'%Y-%m') as month, case when p.if_follow=1 then 1 when p.if_follow=2 then -1 else 0 end as fans_num from tb_video_info i left join tb_user_video_log p using(video_id) where left(p.start_time,4)='2021' order by author,month), t2 as( select author,...

with t1 as( select i.tag, p.video_id, date_format(p.start_time,'%Y-%m-%d') pdate, p.if_retweet from tb_user_video_log p left join tb_video_info i using(video_id) where p.if_follow<>0 or p.if_like<>0 or p.if_retweet<>0 or p.comment_id is not null), t2 as( select tag, video_id, pdate...

with t as( select i.tag, case when timestampdiff(second,p.start_time,p.end_time)*100/i.duration>=100 then 100 else timestampdiff(second,p.start_time,p.end_time)*100/i.duration end as progress from tb_video_info i inner join tb_user_video_log p using(video_id)) select tag, concat(round(avg(progres...

select video_id, round(sum(ifcomp)/count(distinct uid),3) avg_comp_play_rate from( select v.video_id, v.uid, if(timestampdiff(second,v.start_time,v.end_time)>=i.duration,1,0) ifcomp from tb_user_video_log v left join tb_video_info i using(video_id) where left(v.start_time,4)='2021' ) t group by v...

select c.cid, count(uid) as pv, sum(timestampdiff(minute,start_time,end_time)) as time_len from play_record_tb p right join course_info_tb c using(cid) where timestampdiff(day,release_date,date(start_time))<=7 group by c.cid having avg(score)>=3 order by pv desc,time_len desc limit 3

select category, orders_cnt, buyers_cnt, items_qty, revenue, ifnull(round(revenue/orders_cnt,2),0) avg_order_value, rank() over(order by revenue desc,orders_cnt desc,category) rank_by_revenue from( select category, count(distinct o.order_id) orders_cnt, count(distinct o.buyer_id) buyers_cnt, sum(i.q...

with t as ( select author_id, count(distinct title) posts_30d, sum(like_cnt) likes_30d from ( select author_id, title, like_cnt, dense_rank() over ( partition by author_id order by left(publish_ts, 7) desc ) as ts_rank from post ) tmp where ts_rank between 1 and 30 group by tmp.author_id ) select a....

with t1 as( select max(delivered_ts) as latest_dt from parcel), t2 as( select p.courier_id, c.courier_name, c.city, count(p.parcel_id) as orders_7d, sum(if(timestampdiff(minute,p.shipped_ts,p.delivered_ts)<=p.promised_minutes,1,0)) as on_time_7d, round(avg(timestampdiff(minute,p.shipped_ts,p.deli...

with tmp as( select i.store_id, i.store_name, i.city, t.sku_id, coalesce(round(sum(s.qty),2),0) as last7d_qty, coalesce(round(sum(s.qty)/7,2),0) as avg_daily_qty, row_number() over(partition by i.store_id order by coalesce(round(sum(s.qty),2),0) desc,t.sku_id) as rank_in_store from store_info_ i lef...

with t1 as( select dept, coalesce(count(visit_id),0) as feb_2024_visits, coalesce(count(distinct patient_id),0) as feb_2024_unique_patients from visits where left(visit_date,7)='2024-02' group by dept), t2 as( select b.dept, b.visit_id from visits a join visits b on a.dept=b.dept and a.patient_id=b....

select c.customer_id, c.customer_name, coalesce(count(distinct o.order_id),0) as feb_2024_order_count, coalesce(sum(i.qty*i.price),0) as feb_2024_total_amount, coalesce(round(sum(i.qty*i.price)/count(distinct o.order_id),2),0) as feb_2024_avg_order_amount, coalesce(min(o.order_date),null) as feb_202...

with t1 as( select o.zone_id, z.zone_name, sum(if(left(o.order_time,7)='2023-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) peak_2023_02_delivered, sum(if(left(o.order_time,7)='2024-02' and hour(o.order_time) in(11,12,13,18,19,20) and o.status='delivered',1,0)) peak_...