WITH t1 AS ( SELECT staff_id, TIMESTAMPDIFF(SECOND,first_clockin,last_clockin) / 3600 AS sj FROM attendent_tb ) SELECT b.department, CONCAT( ROUND( AVG(CASE WHEN t1.sj > 9.5 THEN 1 ELSE 0 END) * 100 ,1) ,'%') AS ratio FROM t1 INNER JOIN staff_tb AS b ON t1.staff_id = b.staff_id GROUP BY b.departm...

/*分两步，先计算订单明细相关金额数量等，然后把结果左连接给客户表，否则GROUP BY 会报错*/WITHt1 AS (SELECT    a.order_id,    a.customer_id,    a.order_date,    b.sku,    b.qty * b.price AS jeFROM orders AS aINNER JOIN order_items AS b ON a.order_id = b.order_id     AND a.order_date >= '2024-02-01'     AND a.order_date < '2024-03-01...

/* 这题使用GROUP BY + MAX同样可以达成，但是效率与可读性不如窗口函数，这是窗口函数的基础应用 */ WITH t1 AS ( -- 使用窗口函数对每位客户的订单以倒序排名 SELECT o.order_id, c.customer_id, c.customer_name, o.order_date, ROW_NUMBER() OVER(PARTITION BY c.customer_id ORDER BY o.order_date DESC) AS rk FROM orders AS o INNER JOIN customers AS c ON o.customer_id = ...

SELECT c.music_name FROM follow AS a INNER JOIN music_likes AS b ON a.follower_id = b.user_id INNER JOIN music AS c ON b.music_id = c.id WHERE a.user_id = 1 AND b.music_id NOT IN (SELECT music_id FROM music_likes WHERE user_id = 1) GROUP BY c.music_name,c.id ORDER BY c.id;

WITH t1 AS ( SELECT a.order_id, b.customer_name, a.order_date, ROW_NUMBER() OVER(PARTITION BY b.customer_name ORDER BY a.order_date DESC) AS rk FROM orders AS a INNER JOIN customers AS b ON a.customer_id = b.customer_id ORDER BY b.customer_name ) SELECT order_id, customer_name, order_date FROM t1 WH...

WITH t1 AS ( SELECT *, RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rk FROM employees ) SELECT department,employee_name,salary FROM t1 WHERE rk <= 2 ORDER BY department,salary DESC;

/* ①关联4个表，展示每一个贷款的相关信息 ②以城市和贷款类型分组求出每个城市最常申请的贷款类型名称 ③以城市分组求出其他指标 ④关联两个表求出结果 */ WITH t1 AS ( -- 关联4个表，展示每一个贷款的相关信息 SELECT kehu.customer_id, kehu.city, daikuan.loan_amount, leixing.loan_type_id, leixing.loan_type_name FROM customers AS kehu LEFT JOIN loan_applications AS daikuan ON kehu.customer_id = ...

WITH t1 AS ( -- 清洗数据，得出每个人都在哪些天登陆过 SELECT DISTINCT fdate,user_id FROM tb_dau WHERE fdate >= '2023-01-01' AND fdate < '2023-02-01' ), t2 AS ( -- 用窗口函数对登录日期进行排序，目的是为了接下来可以用排序结果进行每次连续登录的分组 SELECT fdate,user_id, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY fdate) AS rk FROM t1 ), t3 AS ( -- 对每次...

WITH t1 AS ( SELECT -- 清洗数据，获取2022年18~25岁用户每个月每首周杰伦歌曲的播放次数 MONTH(a.fdate) AS month, b.song_id, b.song_name, COUNT(*) AS play_pv FROM play_log AS a INNER JOIN song_info AS b ON a.song_id = b.song_id AND b.singer_name = '周杰伦' INNER JOIN user_info AS c ON a.user_id = c.user_id AND c.age >= 18 AND c....

WITH t1 AS ( -- 关联两表清洗数据，并计算出每个员工上半年的绩效总分 SELECT yg.employee_id, yg.employee_name, yg.department, xm.project_name AS project_group, jx.project_id, ROUND(COALESCE(SUM(jx.performance_score),0),0) AS first_half_2024_score FROM employees AS yg LEFT JOIN performance AS jx ON yg.employee_id = jx.employee_...

WITH t1 AS ( -- 清洗数据，关联两表，得出包含所需数据的底表 SELECT sp.product_id, sp.product_name, sp.category_id, SUM(ll.sales_amount) AS total_sales_amount, SUM(ll.cost_amount) AS total_cost_amount FROM product_category AS sp LEFT JOIN sales_and_profit AS ll ON sp.product_id = ll.product_id GROUP BY sp.product_id,sp.pr...

/* ①商品ID：product_id ②商品名称：product_name ③2024年第二季度的销售总额：q2_2024_sales_total ④在所属类别的销售排名：category_rank ⑤所属供应商：supplier_name */ WITH t1 AS ( -- 链接三表，清洗数据，并计算每个商品的销售额 SELECT sp.product_id, sp.product_name, gy.supplier_name, COALESCE(SUM(dd.total_amount),0) AS xse, sp.category FROM product_info AS sp LEF...

/* 输出： product_name: 商品的名称。 total_sales: 2024 年上半年（1-6月份）的总销售量。 max_monthly_sales：2024 年上半年（1-6月份）的总销售量销量最高的月份的销量 min_monthly_sales：2024 年上半年（1-6月份）的总销售量销量最低的月份的销量 avg_monthly_sales：2024 年上半年（1-6月份）的月平均销量（round保留整数） 商品id排序 */ WITH t1 AS ( SELECT b.product_id, b.product_name, a.sale_month, SUM(a.quan...

WITH t1 AS ( -- 计算商家总销售额 SELECT merchant_id, SUM(sale_amount) AS total_sales_amount FROM sales_underline GROUP BY merchant_id ), t2 AS ( -- 计算商家总退款额 SELECT merchant_id, SUM(refund_amount) total_refund_amount FROM refunds_underline GROUP BY merchant_id ), t3 AS ( -- 计算商家满意度 SELECT merchant_id, AVG(sa...