烛少

题解

/ 注册

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

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

select author,month,fans_growth_rate, sum(follow) over(partition by author order by month) as total_fans from( select author,left(start_time,7) month,...

Mysql

2022-01-22

0 329

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

select uid,month,sum(coin) as coin from (select uid,month,rowsub,count(*) as cnt, case when mod(count(*),7)>2 then floor((count(*)/7))*15+mod(count...

Mysql

2022-01-22

0 373

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

with a as( select uid,dt,dense_rank() over(partition by uid order by dt) as rank1 from (select uid,date(in_time) dt from tb_user_log UNION select uid,...

Mysql

2022-01-22

0 289

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

with a as ( select distinct uid,date(in_time) dt , dense_rank() over(partition by uid order by date(in_time)) rank1 from tb_user_log ), c as ( select ...

Mysql

2022-01-22

0 275

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

select round(avg(avg_a),1) avg_amount,round(avg(avg_in-avg_a),1) as avg_cost from (select uid,avg(total_amount) avg_a,sum(cnt*price) avg_in from (sele...

Mysql

2022-01-21

0 366

题解 | #零食类商品中复购率top3高的商品#

with a as( select tod.product_id,uid,cnt,event_time from tb_order_detail tod,tb_product_info tpi,tb_order_overall too where tod.product_id...

Mysql

2022-01-21

0 336

题解 | #某店铺的各商品毛利率及店铺整体毛利率#

with a as (select tod.product_id,tod.price,in_price,tod.cnt from tb_order_overall too,tb_order_detail tod,tb_product_info tpi where too.order_id=tod.o...

Mysql

2022-01-20

0 332

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

with a as( select city,event_time,date(event_time) as dt,start_time,end_time from tb_get_car_record t2,tb_get_car_order t1 where t1.order_id=t2.order_...

Mysql

2022-01-20

0 347

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

with a as( select date(order_time) od, count(start_time) as deal, count(order_id)-count(start_time) nodeal from tb_get_car_order t1 ...

Mysql

2022-01-20

1 395

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

select city,driver_id,avg_grade,avg_order_num,avg_mileage from (select *,dense_rank() over(partition by city order by avg_order_num desc) rank1 from (...

Mysql

2022-01-20

0 376