烦恼的安德鲁不放弃

分类

/ 注册

全部文章（共8篇）

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

select tag, dt, sum_like_cnt_7d, max_retweet_cnt_7d from (select tvi.tag, date_format(start_time, "%Y-%m-%d") as dt, sum(sum(tuvl.if_like)) over(p...

2023-03-10

0 433

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

select tvi.author, date_format(tuvl.start_time, "%Y-%m") as month, round(sum(case when tuvl.if_follow=1 then 1 when tuvl.if_follow=2 then -1 end)/coun...

2023-03-09

111 1418

题解 | #店铺901国庆期间的7日动销率和滞销率#

这个题目要是需求真的很奇葩。。。主要问题是这句话没理解到位（只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。）以下记录一下我题解过程的思路我最开始想的是记录国庆三天 10-01 10-02 10-03的数据，所以想通过 (select "2021-10-01" ...

2023-03-06

0 653

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

题目算积分没有明确是年内/月内的连续签到，测试用例的签到数据也是没有连着月的。比如09-29到10-01的连续签到，经历了09-29 | 09-30 | 10-01，那么10月1号的签到是领1积分还是3积分？在此记录一下我额外的想法。月内连续签到 select uid,sign_month, s...

2023-03-05

0 455

题解 | #考试分数(五)#

select id, job, score, s_rank from (select id, job, score, row_number() over(partition by job order by score desc) as s_rank, count(sc...

2023-03-05

0 295

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

select tmp.video_id, round((100*tmp.spwbl+5*tmp.dzs+3*tmp.pls+2*tmp.zfs)*(1/(datediff((select max(end_time) from tb_user_video_log),tmp.maxet)+1)), 0)...

2023-03-04

0 360

题解 | #浙大不同难度题目的正确率#

select qd.difficult_level, sum(if(qpd.result="right",1,0))/count(qpd.question_id) as correct_rate from user_profile as up inner join quest...

2023-03-03

0 273

题解 | #计算用户的平均次日留存率#

select ( select count(distinct qpd1.device_id, qpd1.date) from question_practice_detail as qpd1 inner join question_practice_detail as qpd2 on date_f...

2023-02-28

1 348