jl0427

分类

/ 注册

全部文章（共50篇）

题解 | #查询所有列#

select * from user_profile

2024-02-03

0 166

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

select *,cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta from( select tag,exam_cnt_20,exam_cnt_21,concat(round((exam...

2023-12-05

1 311

题解 | #连续两次作答试卷的最大时间窗#

select uid,days_window,round(exam_cnt/days_off*days_window,2) as avg_exam_cnt from( select uid,count(start_time) as exam_cnt, datediff(max(star...

2023-12-05

1 282

题解 | #筛选某店铺最有价值用户中消费最多前5名#

import pandas as pd sales = pd.read_csv('sales.csv') R = pd.qcut(sales["recency"], 4,labels=["4", "3", "2", &q...

2023-11-30

0 310

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

select city,max(uv_cnt) as max_wait_uv from( select city,sum(uv)over(partition by city order by uv_time,uv desc) as uv_cnt from( SELECT city,event_tim...

2023-11-28

1 355

题解 | #截至当月的练题情况#

select device_id, date_format(event_date,'%Y-%m') as ym, sum(count(1))over(partition by device_id order by date_format(event_date,'%Y-%m')) as sum_cnt...

2023-11-27

3 258

题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#

select distinct user_id,count(1) as days_cnt from( select *,row_number() over (partition by user_id order by sales_date) as rk from sales_tb) as t1 gr...

2023-11-26

1 367

题解 | #某宝店铺动销率与售罄率#

select style_id, round(sales_num_cnt * 100 / (inventory_cnt - sales_num_cnt),2) as pin_rate, round(gmv * 100 / total_gmv,2) as sell_through_rate from(...

2023-11-25

1 301

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

select period, count(event_time) as get_car_num, round(avg(wait_time)/60,1) as avg_wait_time, round(avg(dispatch_time)/60,1) as avg_dispatch_time from...

2023-11-25

1 293

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

select * from( select order_day, round(sum(order_num)over(order by order_day rows 6 preceding)/7,2) as finish_num_7d, round(sum(cancel_order)over(orde...

2023-11-25

0 389