select year(list_date) as Y,
	count(distinct ts_code) as cnt
from stock_info where industry = '银行' and ts_code like '%SZ'
group by Y
order by Y asc

select year(list_date) as Y,
	count(*) as cnt
from stock_info where industry = '银行' and area = '深圳'
group by Y
order by Y asc

select year(list_date) as Y,
	sum(case area when '深圳' then 1 else 0 end) as cnt
from stock_info where industry = '银行'
group by Y
order by Y asc

select year(list_date) as Y,
	sum(case area when '深圳' then 1 else 0 end) as cnt
from stock_info
group by Y
order by Y asc

select city,
	sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china
where substr(dt,6,2) in ('12','01','02') 
group by city
order by snowy_days 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)/count(1)*100 as decimal(10,2)),'%')  as p
from weather_rcd_china
where year(dt)=2021
group by city
order by p 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, max((LEFT(tmp_h, LENGTH(tmp_h) - 1) + LEFT(tmp_l, LENGTH(tmp_l) - 1))/2) as avg_tmp_h
from weather_rcd_china
where year(dt) = '2021'
group by city
order by avg_tmp_h desc

select city, max((LEFT(tmp_h, LENGTH(tmp_h) - 1) + LEFT(tmp_l, LENGTH(tmp_l) - 1))/2) as avg_tmp_h
from weather_rcd_china
where year(dt) = '2021'
group by city

select * from didi_order_rcd
where cust_uid = 'kjhd30' and finish_time = '1970-01-01 00:00:00'
order by order_id asc limit 1

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

select sum(timestampdiff(second, call_time, grab_time))/count(*) as avg_response_time_seconds from didi_order_rcd
where grab_time != '1970-01-01 00:00:00' and cancel_time = '1970-01-01 00:00:00'

select sum(grab_time - call_time)/count(*) as avg_response_time_seconds from didi_order_rcd
where grab_time != '1970-01-01 00:00:00' and cancel_time = '1970-01-01 00:00:00'

select t2.live_id, t2.live_nm,count(usr_id) as enter_cnt
from ks_live_t1 t1 inner join ks_live_t2 t2
on t1.live_id = t2.live_id
where DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by t2.live_id, t2.live_nm
order by enter_cnt desc limit 5

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

select * 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 * 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')

select goods_id, sum(order_gmv) as total_gmv from order_info
where date(order_time) = '2024-09-10'
group by goods_id
order by total_gmv desc limit 10

select t1.mch_nm as asshole_tried, t1.trx_cnt, t2.mch_nm as darling_tried from 
(select mch_nm, count(*) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in (2023, 2024)
group by mch_nm
having trx_cnt >= 20) as t1
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521') as t2
on t1.mch_nm = t2.mch_nm
order by t1.trx_cnt desc

select distinct t1.mch_nm
from cmb_usr_trx_rcd t1 inner join cmb_usr_trx_rcd t2
on t1.mch_nm = t2.mch_nm
where t1.usr_id = '5201314520' and year(t1.trx_time) = '2024' and t2.usr_id = '5211314521' and year(t2.trx_time) = '2024'
order by t1.mch_nm desc