select user_id,dayname(start_time) as day_of_week,count(*) as listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week

select *
from cmb_usr_trx_rcd 
where (trx_time>='2024-06-08' and trx_time<'2024-06-11' or trx_time>='2024-09-15' and trx_time<'2024-09-18')and usr_id=5201314520
order by trx_time

select *
from cmb_usr_trx_rcd 
where trx_time>='2024-06-08' and trx_time<'2024-06-11' or trx_time>='2024-09-15' and trx_time<'2024-09-18'and usr_id=5201314520
order by trx_time

select *
from cmb_usr_trx_rcd 
where trx_time>='2024-06-08' and trx_time<'2024-06-11' or trx_time>='2024-09-15' and trx_time<'2024-09-18'and usr_id='5201314520'
order by trx_time

select count(distinct case when time(login_time) between '07:30:00' and '09:30:00' or time(login_time) between '18:30:00' and '20:30:00' then usr_id end) as commute,
count(distinct case when time(login_time) between '11:30:00' and '14:00:00' then usr_id end) as lunch_break, 
count(distinct case when time(login_time) between '22:30:00' and '23:59:59' or time(login_time) between '00:00:00' and '01:00:00' then usr_id end) as bedtime
from user_login_log
where login_time>=date_format(date_sub(curdate(),interval 1 month),'%Y-%m-01') and login_time<date_format(curdate(),'%Y-%m-01')

select count(distinct usr_id) as active_users
from user_login_log
where year(login_time)=year(curdate()) and month(login_time)=month(curdate())-1

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') and login_time<date_format(curdate(),'%Y-%m-01')

select year(dt) as Y,
cast(avg(case when city='beijing' then tmp_h end) as decimal(4,2)) as 北京,
cast(avg(case when city='shanghai' then tmp_h end) as decimal(4,2)) as 上海,
cast(avg(case when city='shenzhen' then tmp_h end) as decimal(4,2)) as 深圳,
cast(avg(case when city='guangzhou' then tmp_h end) as decimal(4,2)) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)

select year(dt) as Y,
round(avg(case when city='beijing' then tmp_h end),2) as 北京,
round(avg(case when city='shanghai' then tmp_h end),2) as 上海,
round(avg(case when city='shenzhen' then tmp_h end),2) as 深圳,
round(avg(case when city='guangzhou' then tmp_h end),2) as 广州
from weather_rcd_china
where year(dt) between 2011 and 2022
group by year(dt)

select city,count(case when con like '%雪%' then 1 end) as snowy_days
from weather_rcd_china 
where month(dt) in (12,1,2)
group by city
order by snowy_days desc

select round(avg(timestampdiff(second,call_time,grab_time)),4) as avg_response_time_seconds
from didi_order_rcd 
where grab_time<>'1970-01-01 00:00:00'

select usr_id,min(trx_time) as first_time,mch_nm
from cmb_usr_trx_rcd 
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'

select trx_amt,count(*) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,
round(count(*)/count(distinct usr_id),4) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲' and trx_time>='2023-01-01' and trx_time<'2024-07-01'
group by trx_amt
order by avg_trx_per_user desc
limit 5

select trx_amt,count(*) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲' and year(trx_time)=2024 and month(trx_time) between 1 and 7
group by trx_amt
order by trx_cnt desc
limit 5

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 sum_trx_amt
from cmb_usr_trx_rcd 
where trx_time>='2024-09-01' and trx_time<'2024-10-01' and mch_nm='红玫瑰按摩保健休闲'
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 usr_id=5201314520 and trx_time>='2024-09-01' and trx_time<'2024-10-01' and hour(trx_time) in (22,23,0,1,2,3,4,5)
order by trx_time

select * 
from cmb_usr_trx_rcd 
where usr_id=5201314520 and trx_time>='2024-09-01' and trx_time<'2024-10-01' and hour(trx_time) between 1 and 5
order by trx_time

select * 
from cmb_usr_trx_rcd
where usr_id=5201314520 and trx_time>='2024-09-01' and trx_time<'2024-10-01'
order by trx_time

select distinct cust_uid,mch_nm
from mt_trx_rcd1 
where cust_uid='MT10000'
order by mch_nm