冷凡社长

分类

题解(2)

/ 注册

TA的专栏 0篇文章 0人订阅

0篇文章 0人学习

全部文章（共136篇）

题解 | #统计2021年10月每个退货率不大于0.5的#

select product_id, round(sum(if_click)/count(*),3) as ctr, round(sum(if_cart)/sum(if_click),3) as cart_rate, round(sum(if_payment)/sum(if_cart),3) as ...

Mysql

2022-08-26

1 276

题解 | #计算商城中2021年每月的GMV#

select date_format(event_time,'%Y-%m') as month ,sum(total_amount) as GMV from tb_order_overall where status in (0,1) and year(event_time)=202...

Mysql

2022-08-26

1 276

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

with t1 as ( select uid,date(in_time) dayt,sign_in,datediff(lead(date(in_time)) over(partition by uid order by in_time),date(in_time)) as diff_d fro...

Mysql

2022-08-26

1 364

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

select date(in_time) as dt ,count(distinct t1.uid) as dau, round(count(distinct t.uid)/count(distinct t1.uid),2) as uv_new_ratio from (select ui...

Mysql

2022-08-25

1 306

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

with t1 as (select uid,min(date(in_time))as dt,max(date(in_time)) as dt_max from tb_user_log group by uid ) select case when datediff('2021-11-...

Mysql

2022-08-25

1 297

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

with t1 as (select uid, dt, rank() over(partition by uid order by date(dt)) as ranking from (select uid,date(in_time) as dt from tb_user_log union ...

Mysql

2022-08-25

1 290

题解 | #每篇文章同一时刻最大在看人数#

select artical_id,max(num) as max_uv from ( select t1.artical_id,t1.id, sum(case when t1.out_time between t2.in_time and t2.out_time then 1 el...

Mysql

2022-08-25

1 366

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

select date(in_time) as dt, round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) as avg_viiew_len_sec from tb_user_log where year(...

Mysql

2022-08-25

1 216

题解 | #近一个月发布的视频中热度最高的top3视频#

select t.video_id, round(((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(*))*100+ -- 视频完播率 ...

Mysql

2022-08-25

1 366

题解 | #国庆期间每类视频点赞量和转发量#

select tag,dt,num1 as sum_like_cnt_7d, num2 as max_retweet_cnt_7d from( select tag , date(start_time) dt, sum(if_like),sum(if_retweet), sum(sum(if_lik...

Mysql

2022-08-25

1 215