SQL81 牛客的课程订单分析(五)

select a.user_id, min(case when a.`rank`=1 then date end) as first_buy_date, min(case when a.`rank`=2 then date end) as second_buy_date, numfrom(select user_id, date, row_number() over(partition by user_id order by date) as `rank`, count(*) over(partition by user_id) as numfrom order_infowhere date > '2025-10-15'and status = 'completed'and product_name in ('C++','Java','Python')) awhere a.num >= 2group by a.user_idorder by a.user_id

SQL82 牛客的课程订单分析(六)

select a.id, a.is_group_buy, a.namefrom(select o.id, o.is_group_buy, c.name, count(date) over(partition by user_id) as `rank`from order_info oleft join client c on o.client_id = c.idwhere o.date > '2025-10-15'and o.product_name in ('C++','Java','Python')and status = 'completed'order by o.id) awhere `rank` >= 2

SQL83 牛客的课程订单分析(七)

select ifnull(c.name,'GroupBuy') as source, count(*) as cntfrom(select user_id, client_id, is_group_buy, count(*) over(partition by user_id) as `rank`from order_infowhere date > '2025-10-15'and status = 'completed'and product_name in ('C++','Java','Python')) aleft join client c on a.client_id = c.idwhere `rank` >= 2group by sourceorder by source

SQL84 实习广场投递简历分析(一)

select job, sum(num) as cntfrom resume_infowhere date >= '2025-01-01' and date <= '2025-12-31'group by joborder by cnt desc

select job, sum(num) as cntfrom resume_infowhere YEAR(date) = '2025'group by joborder by cnt desc

解题思路：sum() 求和函数、mysql中求年份YEAR()

SQL85 实习广场投递简历分析(二)

select job, date_format(date, '%Y-%m') as ym, sum(num) as cnt from resume_info where YEAR(date) = '2025' group by job, ym order by ym desc, cnt desc

解题思路：DATE_FORMAT(d,f) 按表达式 f的要求显示日期 d. f的表达式如'%Y-%m-%d %r' 是2011-11-11 11:11:11 AM。 '%Y-%m‘是2011-11；LEFT(s,n) 返回字符串 s 的前 n 个字符