GuaMiao

分类

/ 注册

全部文章（共92篇）

题解 | 统计每个产品的销售情况

with t1 as ( select product_id, date_format(order_date, '%m') as month, sum(quantity) as month_nums from orders group by product_id, month...

2026-03-13

0 15

题解 | 统计所有课程参加培训人次逗号数量+1就是这个人培训课程数

select sum(course_count) as staff_nums from ( select staff_id, case when course is null then 0 else (length(course) - length(...

2026-03-10

0 13

题解 | 顾客登录名 UPPER, CONCAT, LEFT三个函数

select cust_id, cust_name, UPPER(CONCAT(LEFT(cust_contact, 2), LEFT(cust_city, 3))) as user_login from Customers;

2026-03-09

0 12

题解 | #每个月Top3的周杰伦歌曲#

# 首先找到2022年18-25岁用户听周杰伦的歌的数据 with t1 as ( select month(fdate) as month, song_id from play_log where user_id in (select user_id from user_i...

2024-11-06

1 123

concat函数和concat_ws函数

# 主要考察concat函数，让字符串以特定分隔符连接起来 # concat函数和concat_ws函数的区别，前者分隔符在中间参数，后者分隔符是第一个参数 # select concat(last_name, "'", first_name) as name # from em...

2024-11-05

0 169

题解 | #统计活跃间隔对用户分级结果#

with t1 as (# 找到最近一天 select max(date_format(out_time, '%Y-%m-%d')) as today from tb_user_log ),# 新晋用户（近七天新增） t2 as ( select uid from (...

2024-07-01

0 251

题解 | #每类视频近一个月的转发量/率#

# 先找到近一个月每个视频的播放量和转发量 with t1 as ( select video_id, count(*) as watch_cnt, sum(if_retweet) as retweet_cnt from tb_user_video_log where dat...

2024-06-14

0 328

题解 | #店铺901国庆期间的7日动销率和滞销率#

# 可以分类讨论，对于9-25--10-01 with t1 as ( select count(product_id) as release_cnt from tb_product_info where shop_id = '901' and date_format...

2024-06-13

0 296

题解 | #近一个月发布的视频中热度最高的top3视频#

# 先找到近一个月的记录,并且使用case when看每一个记录是否完全播放 with t1 as ( select a.uid, a.video_id, a.if_follow, a.if_like, b.release_time, a.if_retweet, a.comment_id, ...

2024-06-11

0 273

题解 | #查找在职员工自入职以来的薪水涨幅情况#

# 先找到仍在职员工的最开始薪水和最后一次调整后的薪水、 with t1 as ( select c.emp_no, c.start_salary, d.salary as end_salary from salaries as d right join ( ...

2024-05-25

0 227