努力学习数分的一小只

分类

/ 注册

全部文章（共8篇）

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

select round(avg(total_amount),1) as avg_amount ,round(avg(raw_amount-total_amount),1) as avg_cost from( select uid,sum(price*cnt) as raw_...

2024-08-21

0 158

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

用到的知识点：lead窗口函数，date_sub日期函数，case when end语句，取余是%，向下取整floor函数，group/having/order可以用别名step1:统计每个用户在2021年7月7日到2021年10月31日的的登录情况，并按照登录日期排序。with t1 as( ...

2024-08-08

0 202

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

做一次忘一次，这次终于整理好思路啦！先来看看知识点：滑动窗口函数：min(字段1) over(partition by 字段1 order by 字段2 rows between a and b） a取值：unbounded preceding（划分排序后前面所有行）/ n prece...

2024-08-07

0 324

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

select video_id, round((100*already_rate + 5*like_num + 3*comment_num + 2*retweet_num)/(1+last_no_day)) as hot_index from( select a.video_...

2024-04-24

0 223

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

select author,imonth, round(sum( case when if_follow=1 then 1 when if_follow=2 then -1 else 0...

2024-04-24

0 145

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

select uid,in_month, sum(case when days%7 in (1,2) then 15*floor(days/7)+days%7 when days%7 in(3,4,5,6) then 15*floor(days/...

2024-04-24

0 179

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

select act_day as dt, count(*) as dau, round(sum(if_new)/count(*),2) as uv_new_ratio from( select uid, date(in_time) as act_day, ...

2024-04-24

0 150

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

select user_grade,round(count(*)/total_num,2) as ratio from ( select uid,total_num, case when datediff(today,min(in_day))>6 and datedi...

2024-04-23

0 210