select
date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲' and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date;

select mch_nm, sum(trx_amt) as sum_trx_amt from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) = '2024'
group by mch_nm
order by sum_trx_amt desc;

select * 
from cmb_usr_trx_rcd
where (date(trx_time) between '2024-09-01' and '2024-09-30') and (hour(trx_time) in (22,23,0,1,2,3,4,5)) and usr_id='5201314520'
order by trx_time;

select * 
from cmb_usr_trx_rcd
where (date(trx_time) between '2024-09-01' and '2024-09-30') and (hour(trx_time) >= 22 or hour(trx_time) <=5) and usr_id='5201314520'
order by trx_time;

select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and hour(trx_time) between 1 and 5
order by trx_time;

select 
usr_id, mch_nm, trx_time, trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'and date(trx_time) between '2024-9-1' and '2024-9-30'
order by trx_time;

select 
usr_id, mch_nm, trx_time, trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520'and trx_time between '2024-9-1' and '2024-9-30'
order by trx_time;

SELECT 
    ts_code, 
    open_price, 
    close_price
FROM 
    daily_stock_prices
WHERE 
    trade_date = '2023-10-09' AND pct_change > 1;

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as 北京
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
    ,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
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 (12,1,2)
group by city
order by 2 desc;

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

select
	city, count(1) as cloudy_days , concat(round(count(1)/365*100,2),"%") as p
from weather_rcd_china
where year(dt) = 2021 and con like "%多云%"
group by city
order by 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 
    2 
desc

select
	city,cast(avg(tmp_h) as decimal(4,2)) as avg_tmp_h
from weather_rcd_china
group by city
order by 2 desc;

SELECT 
    sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

SELECT 
    t2.live_id,
    t2.live_nm,
    COUNT(*) AS enter_cnt
FROM 
    ks_live_t1 t1
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 
    t1.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

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

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

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