Skywarp

分类

/ 注册

全部文章（共24篇）

题解 | 每月及截止当月的答题情况

SELECT start_month, COUNT(DISTINCT uid) AS mau, SUM(IF(rk=1,1,0)) AS month_add_uv, MAX(SUM(IF(rk=1,1,0))) OVER(ORDER BY start_month) A...

2025-04-29

0 26

题解 | 对试卷得分做min-max归一化

SELECT uid,exam_id, ROUND(AVG(new_score)) avg_new_score FROM( SELECT uid, exam_id, COALESCE(100*(score-(MIN(score)...

2025-04-29

0 34

题解 | 试卷完成数同比2020年的增长率及排名变化

WITH t AS( SELECT YEAR(start_time) year, exam_id, tag, COUNT(submit_time) cnt, RANK() OVER(PARTITION BY YE...

2025-04-29

0 41

题解 | 未完成率top50%用户近三个月答卷情况

SELECT uid,start_month,total_cnt,complete_cnt FROM( SELECT uid, DATE_FORMAT(start_time, "%Y%m") start_month, ...

2025-04-29

0 29

题解 | 实习广场投递简历分析(三)

WITH t AS ( SELECT job, YEAR(date) year, MONTH(date) month, SUM(num) cnt FROM resume_info GROUP BY...

2025-04-28

0 30

题解 | 牛客的课程订单分析(七)

SELECT COALESCE(name,'GroupBuy') source, COUNT(*) cnt FROM( SELECT o.id, is_group_buy, c.name, COUNT(*) OV...

2025-04-28

0 24

题解 | 牛客每个人最近的登录日期(五)

# 不使用连表也能做 SELECT date, ROUND(IFNULL( SUM(date=first_date AND DATEDIFF(next_date,first_date)=1) / SUM(date=first_date), ...

2025-04-27

0 32

题解 | 每个6/7级用户活跃情况

SELECT uid, COUNT(DISTINCT DATE_FORMAT(active_date,"%Y%m")) act_month_total, SUM(IF(YEAR(active_date)=2021,1,0)) act_days_2021, ...

2025-04-25

0 21

题解 | 满足条件的用户的试卷完成数和题目练习数

# 两次连表 SELECT uid, exam_cnt, IFNULL(question_cnt,0) question_cnt FROM( # 统计不同用户的试卷完成数，2021年没有记录的直接筛掉 SELECT uid,COUNT(DIST...

2025-04-25

0 37

题解 | 国庆期间近7日日均取消订单量

# 解法1: 滑动窗口RANGE版 SELECT DISTINCT dt, ROUND(finish/7,2) finish_num_7d, ROUND((total-finish)/7,2) cancel_num_7d FROM( SELECT DA...

2025-04-24

0 27