select * from numbers_for_fun where a=0 and c=0 and b<>0

select * from numbers_for_fun where a=0 and c=0

select * from numbers_for_fun where a=0 and b<>0

select * from hand_permutations 
where card1>'J' and card1 <'K'
and ( card2>'J' and card2 <'K')

select * from subject_score where chinese >100 or math>100 or english >100

select left(name,1)
,count(1)
from students
where	
	length(name)=6 
group by
	left(name,1)
order by
count(1) desc
limit 3

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