Juicelabxx

分类

/ 注册

全部文章（共38篇）

有时候做出来会觉得自己很牛 | #牛客的课程订单分析(七)#

WITH ranked AS ( SELECT *, count(*) over (partition by user_id) AS cnt FROM order_info WHERE date >...

2024-09-14

1 67

昨天开始规范格式，于是...| #牛客的课程订单分析(五)#

只能说从昨天开始强迫自己增加代码可读性之后，写出来的SQL突然就美丽了许多。。 WITH ranked AS ( SELECT *, row_number() over (partition by user_id ORDER BY date) AS rn,...

2024-09-14

1 80

你不能像别人一样默认第二年为2026

WITH first_year AS ( SELECT job, SUM(num) AS first_year_cnt, DATE_FORMAT(date, '%Y-%m') AS first_year_mon FROM ...

2024-09-14

1 92

在where里面模拟casewhen| #考试分数(五)#

with ranked as (select id, job, score, row_number() over (partition by job order by score DESC) as t_rank, count(id) over (partition by job) as cnt fr...

2024-09-14

1 89

把逻辑给理顺了 | #异常的邮件概率#

select date , round(count(case when type = 'no_completed' then 1 end) / count(*),3) as p from (select send_id, receive_id, type, date from...

2024-09-14

1 125

题解 | #获得积分最多的人(三)#

with final as (select a.id, b.name, a.grade_num, dense_rank() over (order by a.grade_num DESC) as dr from (select user_id as id, sum(case when type =...

2024-09-13

1 82

题解 | #考试分数(四)#

我是觉得逻辑很清晰的，直接case when每种情况 select job, case when count(*) <= 2 then 1 when count(*) > 2 and count(*) % 2 = 1 then ceiling(count(*)/2) ...

2024-09-13

1 92

美丽的CTE

with ranked as (select emp_no, first_name, row_number() over ( order by first_name) as rn from employees), original_order as (select emp_no, first_na...

2024-09-13

1 99

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

with ranked as (select *, row_number() over (partition by user_id order by date) as rn, count(*) over (partition by user_id) as cnt from order_info wh...

2024-09-13

1 88

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

select name, date, sum(sump) over (partition by name order by date) as ps_num from (select u.name, p.date, sum(p.number) as sump from login l join pas...

2024-09-13

1 83