阿翟啊

题解

/ 注册

全部文章 / 题解（共39篇）

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

select a.style_id, round(100*sku_sale_num/(sku_on_sale-sku_sale_num),2) pin_rate, round(100*GMV/inven_amount,2) sell_through_rate from (...

Mysql

2022-02-11

0 328

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

select city, max(sum_wait) max_wait_uv from (select *, sum(tag) over(partition by city order by tm,tag desc) sum_wait from ...

Mysql

2022-02-11

0 346

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

select period, count(*) get_car_num, round(avg(wait_time),1) avg_wait_time, round(avg(dispatch_time),1) avg_dispatch_time from (s...

Mysql

2022-02-11

0 423

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

select * from (select dt, round(sum(finish_num) over(order by dt rows 6 preceding)/7,2) as finish_num_7d, round(sum(cancel_n...

Mysql

2022-02-11

0 390

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

select user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) ratio from (select uid, case when datediff('2021-11-04',...

Mysql

2022-02-10

0 325

题解 | #查找入职员工时间排名倒数第三的员工所有信息#

看了一些题友的解答，个人觉得用dense_rank 比较合适这个题虽然是简单题，也有很多方式可以通过，但是个人觉得也得严禁点对待题目说：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。那入职时间排名倒数第一的和倒数第二的，也有可能不止一个所以用 rank() ...

Mysql

2021-12-17

1 354

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

select a.dt, ifnull(round(count(distinct b.uid)/count(a.uid),2),0) uv_left_rate from (select uid, min(date(in_time)) dt fro...

Mysql

2021-12-02

29 1920

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

select user_grade, round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio from ( SELECT uid, case ...

Mysql

2021-12-02

0 398

题解 | #2021年11月每天的人均浏览文章时长#

select dt, round(sum(sec)/count(distinct uid),1) avg_view_len_sec from (select uid, date(in_time) dt, TIMESTAMPDIFF(...

Mysql

2021-12-01

0 322

题解 | #10月的新户客单价和获客成本#

select round(sum(total_amount)/count(order_id),1) avg_amount, round(avg(cost),1) avg_cost from (select a.order_id, total_amount, ...

Mysql

2021-12-01

35 4637