SELECT 
t2.live_id,
t2.live_nm,
count(distinct usr_id) as cnt
from ks_live_t1 as t1 join ks_live_t2 as t2 on t1.live_id = t2.live_id
where date_format(t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by 1,2
order by cnt desc
limit 5;

select 
date_format(enter_time,'%H') AS hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by 1;

select 
lpad(hour(enter_time),2,'0') as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by 1;

select 
hour(enter_time) as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by 1;

select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt 
    , count(1)/2 as ttl_cnt 
    ,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p 
from hand_permutations

select 
cast(sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / 2 as decimal(3,0)) as cnt,
cast(count(1)/2 as decimal(4,0)) as ttl_cnt ,
cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p
from hand_permutations;

select 
cast(sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / 2 as decimal(3,0)) as cnt,
cast(count(1)/2 as decimal(4,0)) as ttl_cnt ,
cast(sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / count(1) as decimal(4,3)) as p
from hand_permutations;

select 
cast(sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / 2 as decimal(3,0)) as cnt,
cast(count(1)/2 as decimal(4,0)) as ttl_cnt ,
sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / count(1) as p
from hand_permutations;

select 
cast(sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / 2 as decimal(3,0)) as cnt,
count(1)/2 as ttl_cnt,
sum(case when right(card1,1) = right(card2,1) then 1 else 0 end) / count(1) as p
from hand_permutations;

select * from hand_permutations 
where right(card1,1) = right(card2,1);

select * 
from hand_permutations
where left(card1,1) = left(card2,1);

SELECT name, subject, class_code, qualification
FROM teachers
WHERE subject = '数学' AND qualification = 'Senior'
ORDER BY name ASC;

select 
	name,subject,class_code,degree
from teachers
where subject = '数学' and degree = 'Senior'
order by name asc
;

select 
	name,subject,class_code,degree
from teachers
where subject = '数学' and degree is not null
order by name asc
;

select 
	student_id,name, birth_date
from students
whereyear(birth_date) = 2009 and gender = 'f'
order by birth_date asc;

select 
	student_id,name, birth_date
from students
whereyear(birth_date) = 2009 and gender = 'm'
order by birth_date asc;

select 
	city,
	sum(case when con like '%雪%' then 1 else 0 end) as nm
from weather_rcd_china
where month(dt) in (12,1,2)
group by 1
order by 2 desc;

select 
	city,
count(1) 
from weather_rcd_china
where month(dt) in (1,2,12)
and con like '%雪%'
group by 1
order by 2 desc;

select
	city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 1 else 0 end) * 100 / count(1) as decimal (10,2)),'%') as p
from weather_rcd_china
where year(dt) = 2021
group by 1
order by 2 desc;

select
	city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 1 else 0 end) * 100 / count(1) as decimal (5,2)),'%') as p
from weather_rcd_china
where year(dt) = 2021
group by 1
order by 2 desc;