select user_id,DAYNAME(start_time) day_of_week,count(1) as listens_per_day from listen_rcd
group by user_id,day_of_week
 order by user_id

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'))
 order by trx_time

select year(list_date) Y,count(1) as cnt from stock_info 
where industry='银行' and right(ts_code,2)='SZ'
group by year(list_date)
 order byY

select goods_id as oods_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 as oods_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

select lpad(hour(enter_time), 2, '0') hour_entered, count(1) as enter_count
from ks_live_t1 
group by hour_entered
order by hour_entered

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

select 
goods_id as oods_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 * from cmb_usr_trx_rcd
where year(trx_time) = 2024
and usr_id = 5201314520
order by trx_amt desc
limit 1

select * from listen_rcd 
where 
 date(start_time) between'2023-12-10' and '2023-12-31'
 and song_id=13
 order by start_time

select 
city,
cast(avg(replace(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     
t2.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
from ks_live_t1 t1
 inner 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 
asshole_tried,trx_cnt,b.mch_nm
from (
select distinct mch_nm as asshole_tried ,count(1) as trx_cntfrom 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.asshole_tried=b.mch_nm
 order by trx_cnt desc

select 
city,
sum(if(con like '%多云%',1,0)) as cloudy_days,
concat(cast(sum(if(con like '%多云%',1,0))/count(1)*100 AS DECIMAL(10,2)),'%') as p
from weather_rcd_china 
where year(dt)=2021
group by city
order by p desc

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 right(card1,1)!=right(card2,1)
 and 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 card1 like 'A%'
or card2 like 'A%'

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;

select a.* from 
(select distinct mch_nm from cmb_usr_trx_rcd 
where usr_id = 5201314520
and year(trx_time)=2024) a
inner join 
(selectdistinct mch_nm from cmb_usr_trx_rcd 
where usr_id = 5211314521
and year(trx_time)=2024) b
on a.mch_nm = b.mch_nm
order by mch_nm desc