select
user_id
,dayname(start_time)
,count(1)
from listen_rcd
group by 
user_id
,dayname(start_time)
order by
user_id
,dayname(start_time)

select
song_name
,count(distinct user_id)
,case when count(distinct user_id)>=50 then '热门歌曲' else '普通歌曲' end type
from song_infosi
left join listen_rcd lr on si.song_id=lr.song_id
group by
	song_name
order by
	song_name
,count(distinct user_id) desc

select
	user_id
,sum(if_finished)/count(1)*100
from listen_rcd
group by
	user_id
having
	sum(if_finished)/count(1)>=0.5

select
	user_id
,sum(if_finished)/count(1)
from listen_rcd
group by
	user_id
having
	sum(if_finished)/count(1)>=0.5

select
t3.album_name
,count(distinct t1.user_id)
from 
	listen_rcd t1
join song_info t2 on t1.song_id=t2.song_id
join album_info t3 on t2.album_id=t3.album_id
group by 
	t3.album_id
	,t3.album_name
order by
	count(distinct t1.user_id) desc,t3.album_name asc
limit 5;

select
t3.album_name
,count(distinct user_id)
from 
	listen_rcd t1
inner join song_info t2 on t1.song_id=t2.song_id
inner join album_info t3 on t2.album_id=t3.album_id
group by 
	t3.album_id
	,t3.album_name
order by
	count(1) desc,t3.album_name asc
limit 5;

with a as 
(select
user_id
,count(distinct origin_singer_id) singer_cnt
from listen_rcd l
left join
	song_info s 
on l.song_id=s.song_id
group by
	user_id
)
select
	user_id
,case whensinger_cnt >=3 then '多样化听众'
else '单一化听众' end 
from a

select usr_id from bilibili_t100 t1
where 
	not exists 
(select 1 from bilibili_t20 t2 where t2.usr_id=t1.usr_id )

SELECT DISTINCT usr_id
FROM bilibili_t100
WHERE usr_id NOT IN (SELECT DISTINCT usr_id FROM bilibili_t20);

select
uid
,round(avg(watch_time),0)
from
(select
uid
,date(start_time)
,sum(timestampdiff(second,start_time,end_time)) watch_time
from ks_video_wat_log 
group by
	uid
,date(start_time)
)a
group by
	uid
order by
	avg(watch_time) desc
limit 5

select
uid
,round(avg(watch_time),0)
from
(select
uid
,date(start_time)
,sum(timestampdiff(second,start_time,end_time)) watch_time
from ks_video_wat_log 
group by
	uid
,date(start_time)
)a
group by
	uid
order by
	avg(watch_time) desc

select
author_id
,round(sum(if_like+if_comment+if_retweet+if_fav)/count(distinct video_id),2) avg_Interactions
from
(select
author_id
 ,t1.video_id
,if_like
,case when comment_id is not null then 1 else 0 endif_comment
,if_retweet
,if_fav
from ks_video_wat_log t1
inner join ks_video_inf t2
on t1.video_id=t2.video_id
) a 
group by
	author_id
order by
	avg_Interactions desc

select
author_id
,round(sum(if_like)+sum(if_comment)+sum(if_retweet)+sum(if_fav)/count(distinct video_id),2) avg_Interactions
from
(select
author_id
 ,t1.video_id
,if_like
,case when comment_id is not null then 1 else 0 endif_comment
,if_retweet
,if_fav
from ks_video_wat_log t1
inner join ks_video_inf t2
on t1.video_id=t2.video_id
) a 
group by
	author_id
order by
	avg_Interactions desc

select 
t2.video_id
,t2.title
,coalesce(avg(timestampdiff(second,start_time,end_time)/duration ),0)rate
from ks_video_wat_log t1
inner join
	ks_video_inf t2
on t1.video_id =t2.video_id
group by
	t2.video_id
order by
	coalesce(avg(timestampdiff(second,start_time,end_time)/duration ),0) desc

select 
video_id
,author_id
,title
from ks_video_inf
where
	screen_type='p'
and release_time>=date_sub(current_date,interval 7 day)

select
	t1.video_id
,t2.title
,sum(if_like)
from 
	ks_video_wat_log t1
left join
	ks_video_inf t2
on t1.video_id=t2.video_id
group by
	t1.video_id
,t2.title
order by
	sum(if_like) desc;

select
	uid
,avg(timestampdiff(second,start_time,end_time))
from 
	ks_video_wat_log
 group by
 	uid
order by
	avg(timestampdiff(second,start_time,end_time)) desc

select
count(1)
from
	stock_info
where
	industry in ('通信设备','生物制药','旅游景点','医疗保健','食品')
 	and 
(name like '中%' or name like '%华%')

select
	ts_code
,symbol
,name
,area
,industry
,list_date
from
(select
*
,row_number()over(partition by industry order by list_date asc) rn
from stock_info
where industry is not null
) a 
where
	a.rn=1

select
case when ts_code like '%SZ' THEN '深交所'
when ts_code like '%SH' THEN '上交所'
when ts_code like '%BJ' THEN '北交所'
else null endtype
,industry
,count(1)
from stock_info 
where industry in('软件服务','银行')	
group by 
	type 
	,industry
order by
	count(1) desc;