select merchant_name,
		CASE 
WHEN merchant_name LIKE '%小红书%' OR merchant_name LIKE '%行吟信息科技' THEN '小红书'
WHEN merchant_name LIKE '%拼多多%' OR merchant_name LIKE '%上海寻梦信息技术%'THEN '拼多多'
WHEN merchant_name LIKE '%京东%' OR merchant_name LIKE '%网银在线%' OR merchant_name LIKE '%京东平台商户%' THEN '京东'
WHEN merchant_name LIKE '%抖音生活服务商家%' OR merchant_name LIKE '%合众易宝%' OR merchant_name LIKE '%格物致品网络科技%' OR merchant_name LIKE '%成都所见所得科技%' OR merchant_name LIKE '%北京空间变换科技%' THEN '抖音'
WHEN merchant_name LIKE '%淘宝%' OR merchant_name LIKE '%天猫%' OR merchant_name LIKE '%杭州今日卖场%' THEN '淘系'
ELSE '其他'end as platform
from (select distinct mch_nm as merchant_name from ccb_trx_rcd) t

select mch_nm as merchant_name,
		case when mch_nm like '%拼多多%' then '拼多多'
	when mch_nm like '%京东%' then '京东'
when mch_nm like '%淘宝%' then '淘宝'
when mch_nm like '%抖音%' then '抖音'
when mch_nm like '%小红书' then '小红书'
else '其他' end as platform
from ccb_trx_rcd

select mch_nm,
		sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where year(trx_time) = 2024 and usr_id = '5201314520'
group by mch_nm
order by sum(trx_amt) desc

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

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

select mch_nm,
		sum(trx_amt)
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by mch_nm
order by sum(trx_amt) desc
limit 5

select mch_nm,
		sum(trx_amt)
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by mch_nm
order by sum(trx_amt)
limit 5