爱吃菜叶的猫
爱吃菜叶的猫
全部文章
分类
评论(2)
题解(61)
归档
标签
去牛客网
登录
/
注册
爱吃菜叶的猫的博客
全部文章
(共70篇)
题解 | #计算商城中2021年每月的GMV#
SELECT SUBSTRING(event_time, 1, 7) AS month, ROUND(SUM(total_amount), 0) AS GMV FROM tb_order_overall WHERE YEAR(event_time) = '2021' AND stat...
Mysql
2022-05-13
0
157
题解 | #连续签到领金币#
SELECT uid, DATE_FORMAT(DATE_ADD(new_dt, INTERVAL 1 DAY), '%Y%m') AS month, SUM(day_coin) AS coin FROM ( -- 获取各uid每个消费日期与其日期排名的差值,差值一样的消费...
Mysql
2022-05-12
0
214
题解 | #每天的日活数及新用户占比#
-- 建立活跃用户表 WITH act_user AS ( SELECT uid, DATE(in_time) AS dt FROM tb_user_log UNION SELECT uid, DATE(out_time) AS dt ...
Mysql
2022-05-11
0
140
题解 | #统计活跃间隔对用户分级结果#
WITH min_day AS ( SELECT uid, DATE(MIN(in_time)) AS mday FROM tb_user_log GROUP BY uid ) SELECT user_grade, ROUND(COUNT(DISTINCT uid)...
Mysql
2022-05-10
0
171
题解 | #2021年11月每天新用户的次日留存率#
SELECT u_m_a.dt, # u_m_a.uid ROUND(COUNT(u_a.dt) / COUNT(u_m_a.dt), 2) AS uv_rate FROM ( -- 筛选每个用户的最早活跃日期 SELECT uid, MIN(DATE...
Mysql
2022-05-05
1
155
题解 | #2021年11月每天的人均浏览文章时长#
-- 每天人均浏览文章时长 = 文章浏览总时长 / 总浏览人数 SELECT dt, ROUND(SUM(cost_time) / COUNT(DISTINCT uid), 1) AS avg_viiew_len_sec FROM ( SELECT uid, SUBS...
Mysql
2022-05-04
0
188
题解 | #近一个月发布的视频中热度最高的top3视频#
使用COUNT()函数时,注意值为NULL和0的区别。值为NULL时不会被算入内,但为0时会被count()纳入计算。 根据解释,最近无播放天数应该视频的最近播放日期和全部视频中最近播放天数之差 SELECT video_id, ROUND((100 * play_rate ...
Mysql
2022-05-02
0
155
题解 | #国庆期间每类视频点赞量和转发量#
SELECT * FROM ( SELECT tag, dt, SUM(like_cnt) OVER(PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING) AS sum_lik...
Mysql
2022-05-02
0
163
题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#
SELECT author, month, ROUND(fans / play_cnt, 3) AS fans_growth_rate, SUM(fans) OVER(PARTITION BY author ORDER BY month) AS total_fans FROM ( ...
Mysql
2022-05-02
0
179
题解 | #每类视频近一个月的转发量/率#
-- 有用户互动的最近时间:max(start_time) -- 最近一个月:当天-29天 SELECT tag, SUM(if_retweet) AS retweet_cut, ROUND(SUM(if_retweet) / COUNT(start_time), 3) AS r...
Mysql
2022-05-01
0
143
首页
上一页
1
2
3
4
5
6
7
下一页
末页