select 
	ts_code,
open_price,
close_price
from 
	daily_stock_prices
where
	trade_date='2023-10-9'
and pct_change > 1
limit 5;

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

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
	snowy_days desc;

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

select 
	* 
from 
	didi_order_rcd 
where
	cust_uid='kjhd30'
and date(finish_time)='1970-01-01'
order by
	call_time asc
limit 1;

select 
	avg(timestampdiff(second,grab_time,call_time))as avg_response_time_seconds
from 
	didi_order_rcd
where
	date(finish_time) !='1970-01-01'
and date(grab_time) !='1970-01-01'
;

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

select 
	lpad(hour_entered,2,'0') as hour_entered,
enter_count
from
(select 
	hour(enter_time) as hour_entered,
count(*) as enter_count
from ks_live_t1
join
 	ks_live_t2
 on
 		ks_live_t1.live_id=ks_live_t2.live_id
group by hour(enter_time)) as new_table
order by
	hour_entered asc;

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

with cnt as
	(select *
	from hand_permutations
	where
		right(card1,1)=right(card2,1)) 
select 
	round((select count(*)from cnt)/2,0) as cnt,
round((52*51/2),0) as ttl_cnt,
round(round((select count(*)from cnt)/2,0)/(52*51/2),3) as p
from 
	hand_permutations
limit 1;

with cnt as
	(select *
	from hand_permutations
	where
		right(card1,1)=right(card2,1)) 
select 
	(select count(*)from cnt)/2 as cnt,
(52*51/2) as ttl_cnt,
(select count(*)from cnt)/(52*51/2) as p
from 
	hand_permutations
limit 1;

with cnt as
	(select *
	from hand_permutations
	where
		right(card1,1)=right(card2,1)) 
select 
	(select count(*)from cnt) as cnt,
(52*51/2) as ttl_cnt,
(select count(*)from cnt)/(52*51/2) as p
from 
	hand_permutations
limit 1;

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

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
	substr(card1,1,1) in ('A','K')
and substr(card2,1,1) in ('A','K')
;

with datal as
	(select 
 	distinct usr_id,
		date(login_time) as login_date
 from
 	user_login_log
 where
 	datediff(current_date,date(login_time))<=30
 ) ,
date2 as
	(select
	T.usr_id,
	T.login_date as T_date,
	T_1.login_date as T_1_date
from
	datal as T
left join
	datal as T_1
on
 		T.usr_id=T_1.usr_id
		and datediff(T.login_date,T_1.login_date)=-1)
select
	T_date as first_login_date,
concat(round(avg(T_1_date is not null)*100,2),'%') as T1_retention_rate
from 
	date2
group by
	T_date
order by
	T_date;

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