Skywarp

分类

/ 注册

全部文章（共24篇）

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

SELECT dt, ROUND(COUNT(DISTINCT IF(DATEDIFF(dt,event_time) BETWEEN 0 AND 6,product_id,NULL))/total,3) sale_rate, ROUND(1-COUNT(DISTINCT IF...

2025-04-24

0 33

题解 | 零食类商品中复购率top3高的商品

# 一步到位 SELECT DISTINCT pi.product_id, ROUND(AVG(IF(COUNT(*)>1, 1,0)) OVER(PARTITION BY pi.product_id),3) repurchase_rate FROM tb_produc...

2025-04-23

0 36

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

SELECT CASE WHEN DATEDIFF(today,register_day) <= 6 THEN '新晋用户' WHEN DATEDIFF(today,last_active_day) <= 6 THEN '忠实用户' ...

2025-04-18

0 38

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

SELECT video_id, ROUND((AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>=duration,1,0))*100 + SUM(if_like)*5 + COUNT(comment_id)*3...

2025-04-17

0 28

题解 | 每个创作者每月的涨粉率及截止当前的总粉丝量

SELECT author, DATE_FORMAT(start_time,"%Y-%m") `month`, ROUND(SUM(IF(if_follow=2,-1,if_follow))/COUNT(*),3) fans_growth_rate, ...

2025-04-17

0 27

题解 | 大小写混乱时的筛选统计

SELECT tag, t_cnt-cnt answer_cnt FROM( SELECT tag, COUNT(*) cnt, SUM(COUNT(*)) OVER(PARTITION BY UPPER(tag)) t_cnt...

2025-04-17

0 39

题解 | 各用户等级的不同得分表现占比

# 一步到位 SELECT level, CASE WHEN score >= 90 THEN '优' WHEN score >= 75 THEN '良' WHEN score >= 60 THEN '中' ...

2025-04-16

0 29

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

# 窗口函数 SELECT emp_no,growth FROM( SELECT emp_no, to_date, FIRST_VALUE(salary) OVER(PARTITION BY emp_no ORDER BY from_d...

2025-04-15

0 35

题解 | 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

# exists硬凑 SELECT t1.emp_no, t1.salary, last_name, first_name FROM( SELECT emp_no, salary FROM salarie...

2025-04-15

0 24

题解 | 获取所有非manager的员工emp_no

SELECT emp_no FROM employees e WHERE NOT EXISTS( SELECT 1 FROM dept_manager d WHERE d.emp_no = e.emp_no )

2025-04-15

0 25