牛客489527393号

分类

/ 注册

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

从0开始的SQL之旅

15篇文章 79人学习

全部文章（共17篇）

题解 | 零食类商品中复购率top3高的商品来自专栏

with t1 as( select uid,product_id,count(*) as cnt FROM tb_order_detail JOIN tb_order_overall USING(order_id) JOIN tb_product_info USIN...

2025-02-25

0 57

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

with t1 as( select p.product_id,shop_id,in_price,quantity,od.order_id,uid,event_time,total_amount,total_cnt,price,cnt from tb_product_info p JOIN ...

2025-02-25

0 43

题解 | 连续签到领金币

select uid,month,sum(coin) as coin from (SELECT uid, DATE_FORMAT(dt, '%Y%m') AS month, CASE DENSE_RANK() over(PARTITION BY DA...

2025-02-24

0 58

题解 | 每天的日活数及新用户占比来自专栏

select dt,count(*) as dau, round(sum(new_in = dt)/count(*),2) as uv_new_ratio from (select uid,date(in_time) as dt from tb_user_log union select ui...

2025-02-23

0 45

题解 | 统计活跃间隔对用户分级结果来自专栏

select user_grade,round(count(*)/user_cnt,2) as ratio from (select distinct uid,(select count(distinct uid) from tb_user_log ) as user_cnt, case ...

2025-02-23

0 47

题解 | 2021年11月每天新用户的次日留存率来自专栏

select t.dt, round(count(distinct a.uid) / count(distinct t.uid),2) as uv_left_rate from ( select date (min(in_time)) ...

2025-02-22

0 50

题解 | 每篇文章同一时刻最大在看人数来自专栏

select artical_id, max(cnt_uv) as max_uv from ( ( select artical_id, sum(uv) over ( ...

2025-02-22

0 58

题解 | 近一个月发布的视频中热度最高的top3视频来自专栏

select video_id, round((100*finish+ 5*cnt_like+ 3*cnt_comt+ 2*cnt_ret)*fresh,0) as hot_index from (select video_id, avg(if(timestampdiff(se...

2025-02-22

0 59

题解 | 国庆期间每类视频点赞量和转发量来自专栏

select * from (select tag, dt, SUM(add_likes) OVER ( PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWIN...

2025-02-21

0 56

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

select author, month, round((addfans)/cnt,3) as fans_growth_rate, sum(addfans) over(partition by author order by author,month) as total_fans from( ...

2025-02-20

0 65