讨厌吃香菜的小山竹在写文章

分类

/ 注册

全部文章（共27篇）

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

with reg as( select uid ,min(event_time) first_time from tb_order_overall group by uid ) select round(avg(total_amount), 1) as av...

2024-07-05

0 182

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

with tmp as ( select uid ,tb_order_detail.product_id product_id ,count(1) cnt from tb_order_detail left join tb_order_overa...

2024-07-05

0 107

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

select product_id ,profit_rate from ( select info.product_id as product_id ,concat(round((1 - sum(in_price) / sum(price)) * 10...

2024-07-05

0 148

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

with t1 as( select uid ,date(in_time) dt from tb_user_log where artical_id = 0 and date(in_time) between '2021-07-07' and '202...

2024-07-04

0 173

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

with reg as ( select uid ,date(min(in_time)) first_time from tb_user_log group by uid ), user_log as ( select uid ,da...

2024-07-04

0 133

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

with tmp as( select uid ,date(min(in_time)) first_date ,date(max(in_time)) last_date ,(select date(max(in_time)) from tb_user_log)...

2024-07-04

1 177

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

with log as( select uid ,artical_id ,in_time dt ,1 is_in from tb_user_log where artical_id != 0 and artical_id != '0' ...

2024-07-04

0 144

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

select dt ,round(sum(viiew_len_sec) / count(distinct uid), 1) avg_viiew_len_sec from( select uid ,left(in_time, 10) dt ,sum(timestampd...

2024-07-04

0 163

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

select video_id ,round((avg(if_comp) * 100 + sum(if_like) * 5 + sum(if_comment) * 3 + sum(if_retweet) * 2 ) * (1 / (1 + min(diff_time)))) hot_index fr...

2024-07-04

0 143

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

select tag ,dt ,sum_like_cnt_7d ,max_retweet_cnt_7d from( select tag ,dt ,sum(like_cnt)over(partition by tag rows between 6 preceding...

2024-07-04

0 149