不做造粪机器

分类

/ 注册

全部文章（共95篇）

题解 | #统计每个退货率不大于0.5的商品各项指标#

with t1 as(select product_id,count(product_id) cnt, sum(if_click) click, sum(if_cart) cart, sum(if_payment) payment, sum(if_refund) refund from tb_use...

2024-08-22

0 142

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

select date_format(event_time,'%Y-%m') month, sum(total_amount) GMV from tb_order_overall where status in (0,1) and event_time between '2021-01-01' an...

2024-08-22

0 122

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

with t1 as(select a.uid, a.dt, a.rank_, dense_rank() over(partition by uid,a.dt-a.rank_ order by date(a.dt)) rank_1 from (select uid, date(in_time) d...

2024-08-20

0 112

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

select c.dt, c.dau, round(ifnull(b.nu/c.dau,0),2) from (select a.dt,count(distinct a.uid) nu from (select uid, date_format(min(in_time), '%Y-%m-%d') d...

2024-08-20

0 128

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

with t2 as(select uid, case when first_days<7 then '新晋用户' when last_days<7 then '忠实用户' when last_days<30 then '沉睡用户' else '流失用户' end user_...

2024-08-19

0 127

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

select a.dt, round(count(distinct b.uid)/count(distinct a.uid),2) from (select uid, min(date_format(in_time,'%Y-%m-%d')) dt from tb_user_log group by ...

2024-08-19

0 118

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

with t1 as(select artical_id,in_time, sum(uv) over(partition by artical_id order by in_time,uv desc) uv_cnt from (SELECT uid,artical_id,in_time,1 uv F...

2024-08-19

0 110

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

select date_format(in_time,'%Y-%m-%d')dt, round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec from tb_user_log ...

2024-08-19

0 111

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

select v.video_id, round((100*avg(if(timestampdiff(second,u.start_time,u.end_time)<v.duration,0,1))+5*sum(u.if_like)+3*count(u.comment_id)+2*sum(u....

2024-08-19

0 119

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

with t1 as( select v.tag, date_format(u.start_time,'%Y-%m-%d') dt, sum(sum(u.if_like)) over(partition by v.tag order by date_format(u.start_ti...

2024-08-19

0 113