with t1 as( select zone_id, zone_name, courier_id, courier_name, round(timestampdiff(second,order_time,delivered_time)/60,2) as deliver_time, date_format(delivered_time,'%Y%m') as mdate from Orders left join Zones using(zone_id) left join Couriers using(courier_id) where ( hour(order_time) between 1...

# 各个图书在特定时间的借阅量，和指定时间对比，同时给出各分区占比 with t1 as( select book_id, book_title, count(if(date_format(borrow_date,'%Y%m')='202302',1,null)) as feb_2023_borrows, count(if(date_format(borrow_date,'%Y%m')='202402',1,null)) as feb_2024_borrows, count(if(date_format(borrow_date,'%Y%m')='202401',1,null)) as jan_...

# 查询每个城市的情况 with t1 as( select city, sum(loan_amount) as total_loan_amount, sum(loan_amount)/count(distinct customer_id) as average_loan_amount, count(distinct customer_id)as total_customers from loan_applications left join customers using(customer_id) left join loan_application_types using(applicat...

with t1 as( select customers.name as customer_name, round(sum(packages.price),2) as total_travel_cost, count(packages.id) as order_count, round(avg(packages.price),2) as avg_order_price from bookings left join packages on bookings.package_id=packages.id left join customers on bookings.customer_id=cu...

with t1 as( select product_line, region, channel_id, channel_name, sum(sale_amount) as total_sale_amount, count(product_id) as total_sale_quantity from sales_data left join oppo_products using(product_id) left join sales_channels using(channel_id) group by product_line, region, channel_id, channel_n...

# 不同地区 不同渠道下 全年销售总额，以及和主要竞品2023年全年销售总金额差值 with t1 as( select product_id, sum( quarter_1_sales_amount +quarter_2_sales_amount +quarter_3_sales_amount +quarter_4_sales_amount ) as total_sales_amount_of_product from sales_info group by product_id ) , t2 as( select product_id, product_name, competitor_n...

# 每种天气类型下，配送速度大于20，投诉率低于50%的每单平均配送时间，总配送次数 with t1 as( select staff_id, weather_type, sum(delivery_time) as total_delivery_time, sum(is_complaint) as complaint_times, count(delivery_time) as delivery_count from delivery_records left join delivery_staff using(staff_id) left join weather_conditions us...

with t1 as( select product_id, product_name, sum(sales_amount) as total_sales_amount, sum(sales_quantity) as total_sales_quantity from sales_records left join products using(product_id) where sales_date between '2024-01-01' and '2024-12-31' group by product_id, product_name ) , t2 as( select product...

# 每个供应商质量高于80成本低于50 with t1 as( select supplier_id, component_id, quality_score, cost from supply_quality_cost where quality_score>80 and cost<50 ) , t2 as( select supplier_id, supplier_name, component_name, quality_score, cost from t1 left join components using(component_id) left join supplie...

# 库存数量小于10，销售额超过5000 with t1 as( select store_id, product_id, inventory_quantity, sales_amount from sales_inventory where inventory_quantity<10 and sales_amount>5000 ) , t2 as( select store_id, store_name, product_category, inventory_quantity, sales_amount from t1 left join products using(prod...

# 每个人在2024年7月的总收入 按照id排列 with t1 as( select courier_id, sum(delivery_fee) as dcount from deliveries_info where delivery_date between '2024-07-01' and '2024-07-31' group by courier_id ) , t2 as( select courier_id, courier_name, base_salary+dcount as total_income from couriers_info left join t1 using(...

# 查询每个部门中绩效得分高于部门平均绩效得分的员工信息 with t1 as( select employee_id, department_name, performance_score, avg(performance_score)over(partition by department_name) as davg from employee_projects left join department_info using(employee_id) ) , t2 as( select employee_id, department_name, performance_score from...

# 按照运输方式分组，看不同运输方式在不同城市的平均运输时长、费用 with t1 as( select destination_city, transport_name, timestampdiff(day,order_date,delivery_date) as ddays, total_cost from order_info left join cost_data using(order_id) left join transport_detail using(transport_id) ) , t2 as( select destination_city, transport_nam...

# 各个品牌在2023年的总销售额、总销售数量、平均客户满意度评分 with t1 as( select brand_id, sum(sales_amount) as total_sales_amount, sum(sales_quantity) as total_sales_quantity, round(avg(satisfaction_score),2) as avg_satisfaction_score from customer_feedback left join sales_data using(sales_id) where sales_month between '2023-...