Vangen_Data_Analytics

分类

/ 注册

全部文章（共28篇）

题解 | 牛客每个人最近的登录日期(五)

with t0 as(select user_id,min(date) as dt from login group by 1) select distinct l.date,coalesce(p,0.000) as p from login l left join ( select dt,roun...

2025-10-05

1 40

题解 | 内容社区用户活跃度、转化与广告归因分析

with t0 as( select campaign_id,channel as top_channel_2024_02 from ( select campaign_id,channel,count(channel) as dt,DENSE_RANK() over(partition by ca...

2025-10-05

2 43

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

SELECT product_id, round( sum( IF ( dt >= 2, 1, 0 ))/ count(*), 3 ) AS repurchase_rate FROM ( SELECT uid, d.product_id, count( cnt ) AS ...

2025-09-27

0 49

题解 | 连续签到领金币

with t0 as( select uid,date_format(in_time,'%Y%m%d') as dt ,DATE_FORMAT(in_time,'%Y%m') as month,sign_in from tb_user_log where artical_id=0 and in_ti...

2025-09-26

1 45

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

WITH t0 AS ( SELECT *, price * cnt AS t_price FROM tb_order_detail ), t1 as ( SELECT p.product_id, in_price * sum( cnt ) as cb, sum( t_price ) as l...

2025-09-26

1 40

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

WITH t0 AS ( SELECT DISTINCT uid, date( in_time ) AS dt, min( date( in_time )) over ( PARTITION BY uid ) AS new_dt FROM tb_user_log UNION...

2025-09-26

1 39

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

SELECT author, MONTH, fans_growth_rate, sum( net_fans ) over ( PARTITION BY author ORDER BY MONTH ) AS total_fans FROM ( SELECT author, DAT...

2025-09-24

1 34

题解 | 每类视频近一个月的转发量/率

SELECT tag, sum( IF ( if_retweet = 1, 1, 0 )) AS retweet_cnt, round( sum( IF ( if_retweet = 1, 1, 0 ))/ count(*), 3 ) AS retweet_rate FROM tb_u...

2025-09-24

1 37

题解 | 最长连续登录天数

WITH t0 AS ( SELECT fdate, row_number() over ( PARTITION BY user_id ORDER BY fdate ) AS dt, user_id FROM tb_dau GROUP BY 1, 3 ), t1 AS ( SELECT fdate,...

2025-09-22

2 43

题解 | 每个月Top3的周杰伦歌曲

select * from ( SELECT month ( fdate ) AS month, ROW_NUMBER() over ( PARTITION BY MONTH ( fdate ) ORDER BY count(*) DESC,s.song_id ) AS ranking, so...

2025-09-21

1 72