后街小行家

分类

题解(10)

/ 注册

全部文章（共41篇）

题解 | #平均绩点大于3.6且人数大于2的学校里学生情况#

select university , count(*) as total_cnt, count(gender='male' or null) as male_cnt, count...

2024-03-25

0 222

题解 | #存在绩点大于该校平均绩点时的学生信息#

with cte as ( select device_id,university,gpa, avg(gpa)over(partition by university) as avg_gpa from user_profile ) select...

2024-03-25

0 192

题解 | #每个年龄的学生在各学校最大数量#

with cte as( select university,age,count(distinct device_id ) as num from user_profile group by university,age ) select age,max(num) as max_cnt from ...

2024-03-25

0 164

题解 | #学生数不大于2的学校的学生情况#

select university,count(distinct device_id ) as cnt,group_concat(device_id separator';') as device_list from user_profile group by university having ...

2024-03-25

0 164

题解 | #每个人的累计搜索点击数排名#

with cte as ( select uid,search_num,ifnull(click_num,0) as click_num from ( select uid,search_num from ( select uid,count(*) as search_num from searc...

2024-03-23

0 146

题解 | #对比一周的播放完成率变化#

with cte as ( select uid,a.vid,time_len,weekday(start_time)+1 as weekth, unix_timestamp(end_time)-unix_timestamp(start_time) as seconds fro...

2024-03-23

0 150

题解 | #最近7天每天的人均停留时长和次均有效时长#

with cte as ( select uid,vid,unix_timestamp(end_time)-unix_timestamp(start_time) as secends, date(start_time) as dt, ...

2024-03-23

0 220

题解 | #每天的平均uv点击率和平均曝光点击率#

with cte as ( select ds,vid, count(distinct if(is_click=1,uid,null))/count(distinct uid) as uctr, sum(is_click)/count(*) as pctr from vi...

2024-03-23

0 140

题解 | #点击率排名与转化率排名差异#

with cte as ( select uid,ctr,cvr, row_number()over(order by ctr desc,uid desc) as dj_rk, row_number()over(order by cv...

2024-03-23

0 178

题解 | #广告点击率排名#

with cte as( select uid,sum(is_click)/count(*) as rk_7 from user_res_event_log_tb where rid like 'ad%' and left(event_date,7)='2022-07' group by uid o...

2024-03-23

0 191