select 
	distinct gd_id,
gd_nm,
gd_typ,
case 
	when fav.mch_id is not null and buy.mch_id IS NOT NULL THEN '收藏且购买'
when fav.mch_id is not null and buy.mch_id IS NULL THEN '收藏不购买'
WHEN fav.mch_id IS NULL AND buy.mch_id IS NOT NULL THEN '购买不收藏'
ELSE '不收藏不购买'
end as category
from gd_inf item
left join xhs_fav_rcd fav
on item.gd_id = fav.mch_id 
left join xhs_pchs_rcd buy
on item.gd_id = buy.mch_id

select 
	year(list_date) as Y,
	sum(case when (industryin ('全国地产','区域地产')) then 1 else 0 end) 地产,
sum(case when (industry='软件服务') then 1 else 0 end) 软件服务
from stock_info
where ((industry like '%地产%') or (industry like '%软件%')) and year(list_date) between 2000 and 2024
group by year(list_date) 
order by Y asc

select 
	year(list_date) as Y,
	sum(case when (industry like '%地产%') then 1 else 0 end) 地产,
sum(case when (industry like '%软件%') then 1 else 0 end) 软件服务
from stock_info
where ((industry like '%地产%') or (industry like '%软件%')) and year(list_date) between 2000 and 2024
group by year(list_date) 
order by Y asc

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

with data as( 
select
usr_id,
	live_id
from ks_live_t1
where '2021-09-12 23:48:38' between enter_time and leave_time
)
select 
	t1.live_id,
t2.live_nm,
count(distinct t1.usr_id) online_users
from data t1
left join ks_live_t2 t2
on t1.live_id=t2.live_id
group by live_id, live_nm
ORDER BY 
    online_users DESC,live_id

with data as( 
select
usr_id,
	live_id,
enter_time,
leave_time
from ks_live_t1
where '2021-09-12 11:48:38' between enter_time and leave_time
)
select 
	t1.live_id,
t2.live_nm,
count(distinct t1.usr_id) online_users
from data t1
left join ks_live_t2 t2
on t1.live_id=t2.live_id
group by live_id, live_nm
ORDER BY 
    online_users DESC,live_id

with data1 as(
	select 
		distinct usr_id, 
		date(login_time) as login_date
	from user_login_log
	where datediff(current_date(),login_time)<=30),
data2 as(
select 
t1.usr_id,
t1.login_date as first_login_date,
t2.login_date as next_login_date,
datediff(t2.login_date,t1.login_date) as days_diff
from data1 as t1
left join data1 as t2
on t1.usr_id=t2.usr_id and (datediff(t2.login_date,t1.login_date) between 1 and 14))
select 
	first_login_date,
round(count(distinct case when days_diff=1 then usr_id end)/count(distinct usr_id),2)t_plus_1_retention_rate,
round(count(distinct case when days_diff=3 then usr_id end)/count(distinct usr_id),3)t_plus_3_retention_rate,
round(count(distinct case when days_diff=7 then usr_id end)/count(distinct usr_id),2)t_plus_7_retention_rate,
round(count(distinct case when days_diff=14 then usr_id end)/count(distinct usr_id),2)t_plus_14_retention_rate
from data2
group by first_login_date
order by first_login_date
;

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 	T1.usr_id,
 	T1.login_date as T_date,
 	T1_1.login_date as T_1_date
	from data1 as T1
	left join data1 as T1_1
	on T1.usr_id=T1_1.usr_id and datediff(T1_1.login_date,T1.login_date)=1)
select 	T_date as login_date, 
		concat(round(count(distinct data2.usr_id)/
count(distinct case when T_1_date is not NULL then usr_id end),4),'%') T1_retention_rate
from data2
group by T_date
order by T_date

select m.prd_id, m.prd_nm,count(m.prd_id) exposure_count
from tb_pg_act_rcd r
join tb_prd_map m
on r.prd_id=m.prd_id
where if_snd=1
group by m.prd_id, m.prd_nm
order by exposure_count desc
;

select m.prd_id, m.prd_nm,count(m.prd_id)exposure_count
from tb_pg_act_rcd r
join tb_prd_map m
on r.prd_id=m.prd_id
where if_snd=1
group by m.prd_id, m.prd_nm
;

select m.prd_id,count(m.prd_id)
from tb_pg_act_rcd r
join tb_prd_map m
on r.prd_id=m.prd_id
where if_snd=1
group by m.prd_id
;

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

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;

select 	mch_nm,
		sum(trx_amt)as sum_trx_amt 
from cmb_usr_trx_rcd
where year(trx_time)=2024
group by mch_nm;

select * from numbers_for_fun
where c=-3*b-8 and a=0 and b!=0
;

select * from numbers_for_fun
where c<>-3*b-8 and a=0 and b!=0
;

select * from scores 
where date(exam_date)='2024-06-30' and (score between 100 and 110) and subject='英语'
order by score desc
;

select * from scores 
where date(exam_date)='2024-06-30' and score between 100 and 110
order by score desc
;

select * from students
where class_code in ('C219','C220','C221') and gender='f'
order by student_id;

select * 
from hand_permutations 
where
	(card1>'J' and card1<'K') and (card2>'J' and card2<'K')
;