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

select
	cust_uid,
count(distinct car_cls) as unique_car_classes
from
	didi_sht_rcd
group by
	cust_uid
order by
	unique_car_classes desc

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

select
	t2.live_id,
t2.live_nm,
count(*) as enter_cnt
from
	ks_live_t2 as t2
left join
	ks_live_t1 as t1
on
	t1.live_id = t2.live_id
where
	enter_time>='2021-09-12 23:00:00' and enter_time<'2021-09-13 00:00:00'
group by
	t2.live_id,t2.live_nm
order by
	enter_cnt desc
limit
	5;

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

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

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

select
	b.mch_typ,
a.trx_ant as trx_ant,
a.trx_amt as trx_amt
from
	(select mch_nm,count(1) as trx_ant,sum(trx_amt) as trx_amt from cmb_usr_trx_rcd
 where usr_id = '5201314520' and year(trx_time) = 2024 group by mch_nm) as a
left join cmb_mch_typ as b
on a.mch_nm = b.mch_nm
order by
	2 desc;

select
	a.mch_nm as asshole_tried,
a.trx_ant,
b.mch_nm as darling_tried
from
	(select mch_nm,count(1) as trx_ant from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in(2023,2024)
group by mch_nm
having count(1)>=20) as a
left join
(select distinct mch_nm from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) in(2023,2024)) as b
on a.mch_nm = b.mch_nm
order by a.trx_ant desc;

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

select
	usr_id,
mch_nm,
trx_time,
trx_amt
from
	cmb_usr_trx_rcd
where
	usr_id = '5201314520' and
year(trx_time) = 2024
order by
	trx_amt desc
limit 1;

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

select
	singer_name,
char_length(singer_name) as len
from
	singer_info;

select
	upper(singer_name) as uppered_name
from
	singer_info;

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

select
	left(name,1) as surname,
count(*) as cnt
from
	students
group by
	surname
order by
	cnt desc
limit 5;

select
	city,
	sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 1 else 0 end)/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
	case 
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
	count(distinct mch_nm) as mch_cnt
from
	cmb_usr_trx_rcd
where
	mch_nm like '%按摩保健休闲%' or lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by
	reg_rules
order by
	mch_cnt desc;

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

select
	cust_uid,
mch_nm
from
	mt_trx_rcd1
where
	cust_uid = 'MT10000'
group by
	cust_uid,mch_nm
order by
	mch_nm asc;