select ts_code,open_price,close_price 
from daily_stock_prices
where
	trade_date = '2023-10-09'
 	and (close_price-open_price)/open_price >0.01

select dt,tmp_h,tmp_l,con 
from weather_rcd_china
where
	city = 'beijing'
and con like '%雪%'

select dt,tmp_h,tmp_l,con 
from weather_rcd_china
where
	city = 'beijing'
and con like '%雪%'
limit 5;

select y,count(y)
from (select ts_code,year(list_date) y from stock_info where industry = '银行') s1
group by y
order by y
limit 5

select y,count(y)
from (select ts_code,year(list_date) y from stock_info) s1
group by y
order by y
limit 5

select length(singer_name),count(singer_id)
from singer_info 
group by length(singer_name)

select 
	user_id,
dayname(start_time),
count(1)
from listen_rcd 
group by
	user_id,
dayname(start_time)
order by 1, 2

select * 
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and 
(date(trx_time) between '2024-06-08' and '2024-06-10'
OR date(trx_time) between '2024-09-15' and '2024-09-17')

SELECT 
	count( distinct
	case when (DATE_FORMAT(login_time, '%H:%i:%s') between '07:30:00' and '09:30:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '18:30:00' and '20:30:00' ) 
 	then usr_id 
 	else null end) as commute,
    count(distinct(case when DATE_FORMAT(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then usr_id else null end)) as lunch_break,
count(distinct case when (DATE_FORMAT(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59' ) 
 	then usr_id
 	else null end) as commute
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

SELECT 
	sum(
	case when (DATE_FORMAT(login_time, '%H:%i:%s') between '07:30:00' and '09:30:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '18:30:00' and '20:30:00' ) 
 	then 1 
 	else null end) as commute,
    sum((case when DATE_FORMAT(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then 1 else null end)) as lunch_break,
sum(case when (DATE_FORMAT(login_time, '%H:%i:%s') between '00:00:00' and '01:00:00' )
or (DATE_FORMAT(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59' ) 
 	then 1 
 	else null end) as commute
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

select 
	year(dt) as Y,
cast(avg(casewhen city = 'beijing' then tmp_h else null end) as decimal(4,2)) as 北京
,cast(avg(casewhen city = 'shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
,cast(avg(casewhen city = 'shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
,cast(avg(casewhen city = 'guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from weather_rcd_china 
group by
	year(dt)

select 
	year(dt) as Y,
round(avg(casewhen city = 'beijing' then tmp_h else null end),2) as 北京
,round(avg(casewhen city = 'shanghai' then tmp_h else null end),2) as 上海
,round(avg(casewhen city = 'shenzhen' then tmp_h else null end),2) as 深圳
,round(avg(casewhen city = 'guangzhou' then tmp_h else null end),2) as 广州
from weather_rcd_china 
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
	city
order by
	2 desc

select * 
from cmb_usr_trx_rcd 
where 
	usr_id='5201314520'
and trx_amt IN(
	select
			MAX(trx_amt)
	from
			cmb_usr_trx_rcd
where
			usr_id='5201314520'
group by
			usr_id
	)

select * 
from cmb_usr_trx_rcd 
where 
	usr_id='5201314520'
and trx_time like '2024%'
order by
	trx_amt desc
limit	1

select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd 
where 
	mch_nm like '%按摩保健休闲%'
;

select 
	(case trx_amt 
 	when 288 then '1.WithHand' 
 	when 388 then '2.WithMimi'
 	when 588 then '3.BlowJobbie'
 	when 888 then '4.Doi'
 	when 1288 then '5.DoubleFly'
 	else '6.other'
	end ) as ser_typ
,count(1) as trx_cnt
,date(MIN(trx_time)) as first_date
from cmb_usr_trx_rcd 
where usr_id = '5201314520'
	and mch_nm = '红玫瑰按摩保健休闲'
group by
	ser_typ
order by
	ser_typ ASC

select usr_id,mch_nm,SUM(trx_amt),count(1) as trx_cnt,MIN(trx_time)
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and trx_amt > 287
group by
	usr_id,mch_nm
order by
	trx_cnt desc

select usr_id,mch_nm,SUM(trx_amt),count(1) as trx_cnt,MIN(trx_time)
from cmb_usr_trx_rcd 
where	
	usr_id = '5201314520'
and trx_amt > 287
group by
	usr_id,mch_nm
order by
	trx_cnt desc
limit 5;