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

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 d.*
FROM didi_sht_rcd d
JOIN loc_nm_ctg s ON d.start_loc = s.loc_nm
JOIN loc_nm_ctg e ON d.end_loc = e.loc_nm
WHERE s.loc_ctg = '餐饮'
AND e.loc_ctg = '餐饮'
order by d.start_tm

select distinct
	t1.gd_id as gd_id,
t1.gd_nm as gd_nm,
t1.gd_typ as gd_typ
from
	gd_inf t1
join
	xhs_fav_rcd t2
on
	t1.gd_id=t2.mch_id
left join
	xhs_pchs_rcd t3
on
	t2.mch_id=t3.mch_id
where 
t3.mch_id is null

select
	date_format(t1.trx_time,'%Y-%m')as trx_mon,
last_day(max(t1.trx_time))as last_day,
day(last_day(max(t1.trx_time)))as day_of_mon,
sum(t1.trx_amt)as trx_amt,
count(t1.trx_time)as trx_cnt,
sum(t1.trx_amt)/day(last_day(max(t1.trx_time)))as avg_day_amt,
		count(t1.trx_time)/day(last_day(max(t1.trx_time)))as avg_day_cnt
from
	cmb_usr_trx_rcd t1
join
	cmb_mch_typ t2
on
	t1.mch_nm=t2.mch_nm
where
	t1.usr_id=5201314520
and
	year(t1.trx_time)in(2023,2024)
and
	t2.mch_typ='休闲娱乐'
group by
	trx_mon
order by
	trx_mon

select
	date_format(t1.trx_time,'%Y-%m')as trx_mon,
last_day(max(t1.trx_time))as last_day,
day(last_day(max(t1.trx_time)))as day_of_mon
from
	cmb_usr_trx_rcd t1
join
	cmb_mch_typ t2
on 
	t1.mch_nm=t2.mch_nm
where
	t1.usr_id=5201314520
and
	year(t1.trx_time)in(2023,2024)
and
	t2.mch_typ='休闲娱乐'
group by
	trx_mon
order by
	trx_mon

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

select 
	t1.singer_id as singer_id,
t1.singer_name as singer_name,
t2.album_id as album_id,
t2.album_name as album_name,
count(t4.if_finished)as play_count
from 
	singer_info t1
join
	album_info t2 on t1.singer_id=t2.singer_id
left join
	song_info t3 on t2.album_id=t3.album_id
left join
	listen_rcd t4 on t3.song_id=t4.song_id
group by
	t1.singer_id,
t1.singer_name,
t2.album_id,
t2.album_name
HAVING 
  play_count = 0

select
	t1.prd_id as prd_id,
t2.prd_nm as prd_nm,
sum(t1.if_snd)as exposure_count
from tb_pg_act_rcd t1
join tb_prd_map t2 on t1.prd_id=t2.prd_id
group by t1.prd_id,t2.prd_nm
order by exposure_count desc
limit 1

select 
	t1.live_id as live_id,
t2.live_nm as live_nm,
count(t1.usr_id)as enter_cnt
from ks_live_t1 t1
join ks_live_t2 t2 on t1.live_id=t2.live_id
where t1.enter_time between '2021-09-12 23:00:00' and '2021-09-12 23:59:59'
group by t1.live_id,t2.live_nm
order by enter_cnt desc
limit 5

select 
	t1.gd_typ as gd_typ,
count(distinct t2.cust_uid)as buyer_count
from gd_inf t1
join xhs_pchs_rcd t2
on t1.gd_id=t2.mch_id
group by t1.gd_typ
order by buyer_count desc
limit 1

select 
	t1.gd_typ as gd_typ,
count(t2.pch_trq)as buyer_count
from gd_inf t1
join xhs_pchs_rcd t2
on t1.gd_id=t2.mch_id
group by t1.gd_typ
order by buyer_count desc
limit 1

select gd_id,gd_nm,count(t2.fav_trq)as fav_count
from gd_inf t1
join xhs_fav_rcd t2
on t1.gd_id=t2.mch_id
group by t1.gd_id,t1.gd_nm
order by fav_count desc
limit 1

select *
from singer_info
where type2="港台"and type3="乐队"
order by singer_id

select *
from singer_info
where type2="港台"
order by singer_id

select
	(case when trx_amt=288 then '1.WithHand'
when trx_amt=388 then '2.WithMimi'
when trx_amt=588 then '3.BlowJobbie'
when trx_amt=888 then '4.Doi'
when trx_amt=1288 then '5.DoubleFly'
else '6.other' end)as ser_typ,
count(1)as trx_cnt,
min(date(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

select
	t2.mch_typ,
t1.mch_nm,
count(t1.trx_amt) as trx_cnt,
sum(t1.trx_amt)as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where t1.usr_id =5201314520 and year(t1.trx_time)=2024 and t2.mch_typ is null
group by t2.mch_typ,
t1.mch_nm
order by trx_cnt desc

select 
	cmb_mch_typ.mch_typ,
count(*)as trx_cnt,
sum(cmb_usr_trx_rcd.trx_amt)as trx_amt
from cmb_usr_trx_rcd
left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm=cmb_mch_typ.mch_nm
where cmb_usr_trx_rcd.usr_id=5201314520
and year(cmb_usr_trx_rcd.trx_time) = 2024
group by cmb_mch_typ.mch_typ
order by trx_cnt desc

select 
	coalesce(cmb_mch_typ.mch_typ, 'null') as mch_typ,
count(*)as trx_cnt,
sum(cmb_usr_trx_rcd.trx_amt)as trx_amt
from cmb_usr_trx_rcd
right join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm=cmb_mch_typ.mch_nm
where cmb_usr_trx_rcd.usr_id=5201314520
and year(cmb_usr_trx_rcd.trx_time) = 2024
group by coalesce(cmb_mch_typ.mch_typ, 'null')
order by trx_cnt desc

select didi_sht_rcd.cust_uid,didi_sht_rcd.start_loc,didi_sht_rcd.end_loc,didi_sht_rcd.start_tm,didi_sht_rcd.car_cls
from didi_sht_rcd 
join loc_nm_ctg
on didi_sht_rcd.end_loc=loc_nm_ctg.loc_nm
where loc_nm_ctg.loc_ctg='餐饮'
order by didi_sht_rcd.start_tm