在笔试的杰克很勤劳

分类

/ 注册

全部文章（共36篇）

题解 | #各城市最大同时等车人数#

with zb as (with cb2 as ((with cb as (select city,event_time,1 as wait_nu,if(start_time is not null,start_time,end_time) end_time,-1 as leave_num /*将...

2023-12-27

1 233

题解 | #工作日各时段叫车量、等待接单时间和调度时间#

select aperiod,count(order_id) get_car_num,round(sum(wait_time)/(count(order_id) *60),1) avg_wait_time,/*单位是分每单*/ round(sum(dispatch_time)/(count(star...

2023-12-27

1 236

题解 | #连续签到领金币#

with zb_0 as (select *,CASE WHEN da >= 7 AND FLOOR(MOD(da,7) / 3) < 1 THEN FLOOR(da / 7) * 15 + MOD(da,7) /*大于7天且余数不大于3*/ WHEN da >= 7 A...

2023-12-21

1 270

题解 | #每天的日活数及新用户占比#

with zb2 as (with zb as (select uid,in_time dt from tb_user_log union /**/ select uid,out_time from tb_user_log) select uid,date_format(dt,"%Y...

2023-12-19

1 195

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

with zb3 as (with zb2 as (with zb as (select uid,out_time,max(out_time) over() as max_time,/*离现在最近时间*/ first_value(out_time) over(partition by uid ord...

2023-12-19

1 232

题解 | #2021年11月每天新用户的次日留存率#

with zb3 as(with zb1 as(with zb as (select uid,in_time,row_number() over(partition by uid order by in_time) a from tb_user_log union all select uid,ou...

2023-12-18

1 174

题解 | #筛选限定昵称成就值活跃日期的用户#

select uid,nick_name,achievement from user_info where nick_name like "牛客%号" and achievement between 1200 and 2500 and uid in ( select uid fr...

2023-12-13

1 206

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

with zb4 as(with zb2 as (select uid,date_format(start_time,'%Y%m') start_month,start_time,first_value(start_time) over(partition by uid order by start...

2023-12-05

0 202

题解 | #每份试卷每月作答数和截止当月的作答总数。#

with zb as (select exam_id,date_format(start_time,'%Y%m') start_month,count(uid) month_cnt from exam_record e1 group by exam_id,date_format(start_time...

2023-12-04

1 173

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

with zb as (with cb1 as (select uid,er.exam_id,score,max(score) over(partition by exam_id) max_s, min(score) over(partition by exam_id) min_s from exa...

2023-12-02

1 248