select year(dt) as Y
,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as beijing
,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as shanghai
,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as shenzhen
,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as guangzhou
from
weather_rcd_china
where
year(dt) between 2011 and 2022
group by
year(dt)
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 1
order by 2 desc
select
city
,sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days
,concat(round(sum(case when con like '%多云%' then 1 else 0 end)/count(1)*100,2),'%') as p
from weather_rcd_china
where year(dt) = 2021
group by city
order by p desc
select
a.live_id
,b.live_nm
,count(1) as enter_cnt
from ks_live_t1 a
left join ks_live_t2 b on a.live_id = b.live_id
where date(enter_time) = '2021-09-12' and hour(enter_time) = 23
group by 1,2
order by 3 desc
limit 5
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 *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
with data1 as (
select distinct
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
)
select
T.login_date as login_date
,concat(round(avg(T_1.login_date is not null)*100, 2), '%') as T1_retention_rate
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id and datediff(T.login_date, T_1.login_date) = -1
group by login_date
order by login_date
select
a.mch_nm as asshole_tried
,a.trx_cnt
,b.mch_nm as darling_tried
from
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
select
a.mch_nm as asshole_tried
,a.trx_cnt
,b.mch_nm as darling_tried
from
(select
mch_nm
,count(1) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' andyear(trx_time) in (2023,2024)
group by 1
having trx_cnt >= 20) a
left join
(select
distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = 'usr_id=5211314521' and year(trx_time) in (2023,2024)) b
on a.mch_nm = b.mch_nm
order by 2 desc