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

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

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

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 daily_user AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE datediff(current_date,date(login_time)) <=30
GROUP BY usr_id, login_date
)
SELECT 
a.login_date,
CONCAT(
ROUND(COUNT(b.usr_id) / COUNT(a.usr_id) * 100, 2),
'%'
) AS T1_retention_rate
FROM daily_user a
LEFT JOIN daily_user b
ON a.usr_id = b.usr_id
AND a.login_date + INTERVAL 1 DAY = b.login_date
GROUP BY a.login_date
ORDER BY a.login_date;

WITH daily_user AS (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY usr_id, login_date
)
SELECT 
a.login_date,
CONCAT(
ROUND(COUNT(b.usr_id) / COUNT(a.usr_id) * 100, 2),
'%'
) AS T1_retention_rate
FROM daily_user a
LEFT JOIN daily_user b
ON a.usr_id = b.usr_id
AND a.login_date + INTERVAL 1 DAY = b.login_date
GROUP BY a.login_date
ORDER BY a.login_date;

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 goods_id,sum(order_gmv) as total_gmv
from order_info 
where date(order_time)="2024-09-30"
group by goods_id 
order by total_gmv desc
limit 10;