with t1 as ( select shift_id,rider_id from (select *,rank()over(partition by shift_id,rider_id order by invited_at) rak from shift_invite_responses sir ) a where response_status = 'accepted' and responded_at is not null and timestampdiff(second,invited_at,responded_at)/3600<=2 and rak=1 group by ...

with t1 as (select distinct ue.user_id from user_events ue join users u using(user_id) where (registration_date >= '2025-01-01' and registration_date<'2025-07-01') and plan_type ='Pro' and (event_timestamp >= '2025-01-01' and event_timestamp<'2025-04-01') and event_type='login'), t2 as (...

select t.player_id,username,level current_level,if(level>=30,'高阶玩家','新秀玩家') player_category,round(sum(cost),2) total_april_spending from transactions t join players p using(player_id) where date_format(creation_date,'%Y%m')='202503' and date_format(purchase_time,'%Y%m')='202504' group by 1,2,3 or...

with t1 as (select fu.team_id,team_name,count(*) april_usage_count,if(count(*)>50,'深度采纳团队','普通采纳团队') adoption_category from feature_usage fu join teams t using(team_id) where date_format(usage_timestamp,'%Y%m')='202504' and feature_name='Advanced_Analytics' and plan_level='Enterprise' group by 1,...

with a as (select cc.user_id,round(avg(rating),2) average_rating,if(round(avg(rating),2)>=4.0,'优质反馈学员','普通反馈学员') feedback_type from course_completions cc join content_reviews cr using(user_id,course_id) where cc.course_id='DS-102' and date_format(completion_date,'%Y%m')='202503' group by 1), b as...

with a as (select category,avg(t_usage) avg_usage from (select device_id,sum(usage_kwh) t_usage from energy_logs where date_format(log_timestamp,'%Y%m')='202501' group by device_id) b join smart_devices sd using(device_id) group by category) select device_name,location_code,total_usage,efficiency_le...

with t1 as (select store_id,product_id,inventory_quantity,sales_amount from sales_inventory si where inventory_quantity<10 and sales_amount>5000) select t1.store_id,store_name,product_category,inventory_quantity,sales_amount from t1 join stores using(store_id) join products using(product_id) o...

select * from (select p.courier_id,courier_name,city,count(*) orders_7d,sum(if(timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes,1,0)) on_time_7d,ifnull(round(sum(if(timestampdiff(minute,shipped_ts,delivered_ts)<=promised_minutes,1,0))/count(*),2),0) on_time_rate,ROUND(AVG(TIMEST...

with a as (select ss.store_id,ss.sku_id,sum(qty) last7d_qty,round(sum(qty)/7,2) avg_daily_qty from store_stock_ ss left join sales_daily_ sd using(store_id,sku_id) where sale_date between date_sub((select max(snapshot_date) from store_stock_ ss group by store_id,sku_id limit 1) ,interval 7 day)and (...

with a as (select dept,count(*) feb_2024_visits,count(distinct v.patient_id) feb_2024_unique_patients from (select*from visits where date_format(visit_date,'%Y%m')='202402') v group by 1), b as (select dept,round(sum(if(df<=30,1,0))*100/count(*),2) feb_2024_revisit_rate from(select *,lag(visit_da...

select c.customer_id,customer_name,ifnull(count(distinct order_id) ,0)feb_2024_order_count,ifnull(round(sum(qty*price) ,2) ,0.00)feb_2024_total_amount,ifnull(round(sum(qty*price)/count(distinct order_id),2),0.00) feb_2024_avg_order_amount,min(order_date) feb_2024_first_order_date,max(order_date) feb...

with a as (select o.zone_id,zone_name, sum(if(date_format(delivered_time,'%Y%m')='202302',1,0)) peak_2023_02_delivered, sum(if(date_format(delivered_time,'%Y%m')='202402',1,0)) peak_2024_02_delivered, sum(if(date_format(delivered_time,'%Y%m')='202401',1,0)) peak_2024_01_delivered, sum(if(date_format...

select bk.book_id, book_title, ifnull(sum( if(date_format(borrow_date, '%Y%m') = '202302', 1, 0) ),0) feb_2023_borrows, ifnull(sum( if(date_format(borrow_date, '%Y%m') = '202402', 1, 0) ),0) feb_2024_borrows, ifnull(sum( if(date_format(borrow_date, '%Y%m') = '202401', 1, 0) ),0) jan_2024_borrows, if...

select s.product_id,product_name,competitor_name,sum(quarter_1_sales_amount+quarter_2_sales_amount+quarter_3_sales_amount+quarter_4_sales_amount) total_sales_amount_of_product,round(sum(quarter_1_sales_amount+quarter_2_sales_amount+quarter_3_sales_amount+quarter_4_sales_amount)-avg(total_competitor_...