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

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

select city, cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from weather_rcd_china
where year(dt) = 2021
group by city
order by avg_tmp_h desc

select city, cast(avg(tmp_h) as decimal(5,2)) as avg_tmp_h
from weather_rcd_china
where year(dt) = 2021
group by city
order by avg_tmp_h desc

select city, round(avg(tmp_h),2) as avg_tmp_h
from weather_rcd_china
where year(dt) = 2021
group by city
order by avg_tmp_h desc

select avg(timestampdiff(second,call_time,grab_time)) as avg_response_time_seconds
from didi_order_rcd
where grab_time!= '1970-01-01 00:00:00'

select ks_live_t2.live_id, live_nm, count(enter_time) as enter_cnt
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id = ks_live_t2.live_id
where date(enter_time) = '2021-09-12' and date_format(enter_time,'%H') = 23
group by live_id,live_nm
order by enter_cnt desc
limit 5

select date_format(enter_time,'%H') as hour_entered,
	count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%H')
order by hour_entered

select hour(enter_time) as hour_entered,
	count(*) as enter_count
from ks_live_t1
group by hour(enter_time)
order by hour(enter_time)

select hour(enter_time) as hour_entered,
	sum(case when usr_id is not null then 1 else 0 end) over(partition by hour(enter_time) order by hour(enter_time)) as ebter_count
from ks_live_t1

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

select id,card1,card2
from hand_permutations
where left(card1,1) = left(card2,1)
order by id

select id,card1,card2
from hand_permutations
where (card1 like '%A%' or card2 like '%A%') and right(card1,1) = right(card2,1)
order by id

select id,card1,card2
from hand_permutations
where card2 like '%A%' and right(card1,1) = right(card2,1)
order by id

select id,card1,card2
from hand_permutations
where card2 like '%A%'

select id,card1,card2
from hand_permutations
where concat(card1,card2) like 'A%'

select id, card1, card2
from hand_permutations
where (left(card1,1) = 'A' and left(card2,1) = 'A')
	or (left(card1,1) = 'K' and left(card2,1) = 'K')
or (left(card1,1) = 'A' and left(card2,1) = 'K')
or (left(card1,1) = 'K' and left(card2,1) = 'A')

select t2.prd_id, prd_nm, sum(if_snd) as exposure_count
from tb_pg_act_rcd as t1
join tb_prd_map as t2 on t1.prd_id = t2.prd_id
group by t2.prd_id,prd_nm
order by exposure_count desc
limit 1