with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝|滴滴') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
when mch_nm regexp ('滴滴') then '滴滴'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
when mch_nm regexp ('携程') then '携程'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多|格物致品') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书|携程') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝|携程') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音|携程') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm regexp ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm regexp ('行吟信息|小红书') then '小红书'
when mch_nm regexp ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm regexp ('合众易宝|空间变换|所见所得|格物致品|抖音') then '抖音' 
when mch_nm regexp ('寻梦信息|拼多多') then '拼多多'
when mch_nm in ('天猫|今日卖场|淘宝') then '淘系'
when mch_nm in ('行吟信息|小红书') then '小红书'
when mch_nm in ('网银在线|京东') then '京东'
else '其他'
end as platform
from t1
order by merchant_name asc

with t1 as (
select mch_nm
from ccb_trx_rcd
group by mch_nm
)
select mch_nm as merchant_name,
case when mch_nm in ('合众易宝','空间变换','所见所得','格物致品','抖音') then '抖音' 
when mch_nm in ('寻梦信息','拼多多') then '拼多多'
when mch_nm in ('天猫','今日卖场','淘宝') then '淘系'
when mch_nm in ('行吟信息','小红书') then '小红书'
when mch_nm in ('网银在线','京东') then '京东'
else '其他'
end as platform
from t1

with t1 as (
select concat(card1,card2) as card12,
case when left(card1,1)='1' then 'T' else left(card1,1) end as left_card1,
case when left(card2,1)='1' then 'T' else left(card2,1) end as left_card2,
right(card1,1) as right_card1,
right(card2,1) as right_card2
from hand_permutations
),
t2 as (
select card12,
case when left_card1='T' then 10
when left_card1='J' then 11
when left_card1='Q' then 12
when left_card1='K' then 13
when left_card1='A' then 14
else cast(left_card1 as signed) 
end as left_card1,
case when left_card2='T' then 10
when left_card2='J' then 11
when left_card2='Q' then 12
when left_card2='K' then 13
when left_card2='A' then 14
else cast(left_card2 as signed) 
end as left_card2,
case when right_card1=right_card2 and left_card1<>left_card2 then 's'
when right_card1<>right_card2 and left_card1<>left_card2 then 'o'
else ''
end as is_same 
from t1
),
t3 as (
select card12,
if(left_card1>left_card2,left_card1,left_card2) as left_card1,
if(left_card1<left_card2,left_card1,left_card2) as left_card2,
is_same
from t2
),
t4 as (
select card12,
case when left_card1=10 then 'T'
when left_card1=11 then 'J'
when left_card1=12 then 'Q'
when left_card1=13 then 'K'
when left_card1=14 then 'A'
else cast(left_card1 as char) 
end as left_card1,
case when left_card2=10 then 'T'
when left_card2=11 then 'J'
when left_card2=12 then 'Q'
when left_card2=13 then 'K'
when left_card2=14 then 'A'
else cast(left_card2 as char)
end as left_card2,
is_same
from t3
),
t5 as (
select card12,
concat(left_card1,left_card2,is_same) as hand 
from t4
)
select card12,
case when ranking<(select ranking from hand_probabilities where hand='J4o') then 1
else 0 end as if_garrett
from t5 join hand_probabilities using (hand)
order by ranking asc

with t1 as (
select concat(card1,card2) as card12,
case when left(card1,1)='1' then 'T' else left(card1,1) end as left_card1,
case when left(card2,1)='1' then 'T' else left(card2,1) end as left_card2,
right(card1,1) as right_card1,
right(card2,1) as right_card2
from hand_permutations
),
t2 as (
select card12,
case when left_card1='T' then 10
when left_card1='J' then 11
when left_card1='Q' then 12
when left_card1='K' then 13
when left_card1='A' then 14
else cast(left_card1 as signed) 
end as left_card1,
case when left_card2='T' then 10
when left_card2='J' then 11
when left_card2='Q' then 12
when left_card2='K' then 13
when left_card2='A' then 14
else cast(left_card2 as signed) 
end as left_card2,
case when right_card1=right_card2 and left_card1<>left_card2 then 's'
when right_card1<>right_card2 and left_card1<>left_card2 then 'o'
else ''
end as is_same 
from t1
),
t3 as (
select card12,
if(left_card1>left_card2,left_card1,left_card2) as left_card1,
if(left_card1<left_card2,left_card1,left_card2) as left_card2,
is_same
from t2
),
t4 as (
select card12,
case when left_card1=10 then 'T'
when left_card1=11 then 'J'
when left_card1=12 then 'Q'
when left_card1=13 then 'K'
when left_card1=14 then 'A'
else cast(left_card1 as char) 
end as left_card1,
case when left_card2=10 then 'T'
when left_card2=11 then 'J'
when left_card2=12 then 'Q'
when left_card2=13 then 'K'
when left_card2=14 then 'A'
else cast(left_card2 as char)
end as left_card2,
is_same
from t3
),
t5 as (
select card12,
concat(left_card1,left_card2,is_same) as hand 
from t4
)
select card12,
case when ranking<(select ranking from hand_probabilities where hand='87o') then 1
else 0 end as if_garrett
from t5 join hand_probabilities using (hand)
order by ranking asc

with data1 as (
select distinct usr_id,
date(login_time) as login_date
from user_login_log
),
 data2 as (
 select usr_id,
login_date,
first_value(login_date) over (partition by usr_id order by login_date) as `start_date`,
lag(login_date) over (partition by usr_id order by login_date) as `pre_date`
 from data1
 ),
 data3 as (
 selectdistinct data1.usr_id,
 data1.login_date,
 case
 when pre_date is null and datediff(data1.login_date,start_date) = 0 then 'flag-1'
 when pre_date is not null and datediff(data1.login_date,pre_date) <= 3 then 'flag-2'
 else 'flag-3'
 end as flag
 from data1
left join data2
on (data1.usr_id,data1.login_date) = (data2.usr_id,data2.login_date)
 ),
 data4 as (
 select login_date,
round((sum(if(flag = 'flag-1', 1, 0)) / count(1)) * 100, 2) as rate_flag_1,
round((sum(if(flag = 'flag-2', 1, 0)) / count(1)) * 100, 2) as rate_flag_2,
round((sum(if(flag = 'flag-3', 1, 0)) / count(1)) * 100, 2) as rate_flag_3
 from data3
 group by login_date
 )
select login_date,
 concat_ws(',', rate_flag_1, rate_flag_2, rate_flag_3) as pct
from data4
where year(login_date) = '2024'
group by login_date
order by login_date;

with t1 as (
select distinct usr_id,date(login_time) as dt
from user_login_log
where login_time>='2024-01-01' and login_time<'2025-01-01'
),
t2 as (
select usr_id,min(date(login_time)) as min_dt
from user_login_log
group by usr_id
),
t3 as (
select distinct usr_id,date(login_time) as dt
from user_login_log
),
t4 as (
select t1.dt as login_date,round(100*count(t2.usr_id)/count(t1.usr_id),2) as xinzengyonghuzhanbi
from t1 left join t2 on t1.usr_id=t2.usr_id and t1.dt=t2.min_dt
group by t1.dt
),
t5 as (
select t1.usr_id,t1.dt as login_date,datediff(t1.dt,t3.dt) as days
from t3 join t1 on t3.usr_id=t1.usr_id and t3.dt<=t1.dt
),
t6 as (
select usr_id,login_date,min(days) as min_day
from t5 
where days>0
group by usr_id,login_date
),
t7 as (
select t1.dt as login_date,
round(100*count(case when min_day>=0 and min_day<=3 then 1 end)/count(*),2) as liucunyonghuzhanbi,
round(100*count(case when min_day>3 then 1 end)/count(*),2) as huiliuyonghuzhanbi
from t1 left join t6 on t1.usr_id=t6.usr_id and t1.dt=t6.login_date
group by t1.dt
)
select t4.login_date,concat(xinzengyonghuzhanbi,', ',liucunyonghuzhanbi,', ',huiliuyonghuzhanbi) as pct
from t4 join t7 using (login_date)
order by login_date asc

with t1 as (
select distinct usr_id,date(login_time) as dt
from user_login_log
where login_time>='2024-01-01' and login_time<'2025-01-01'
),
t2 as (
select usr_id,min(date(login_time)) as min_dt
from user_login_log
group by usr_id
),
t3 as (
select distinct usr_id,date(login_time) as dt
from user_login_log
),
t4 as (
select t1.dt as login_date,round(100*count(t2.usr_id)/count(t1.usr_id),2) as xinzengyonghuzhanbi
from t1 left join t2 on t1.usr_id=t2.usr_id and t1.dt=t2.min_dt
group by t1.dt
),
t5 as (
select t1.usr_id,t1.dt as login_date,datediff(t1.dt,t3.dt) as days
from t3 join t1 on t3.usr_id=t1.usr_id and t3.dt<=t1.dt
),
t6 as (
select usr_id,login_date,min(days) as min_day
from t5 
group by usr_id,login_date
),
t7 as (
select login_date,
round(100*count(case when min_day>0 and min_day<=3 then 1 end)/count(*),2) as liucunyonghuzhanbi,
round(100*count(case when min_day>3 then 1 end)/count(*),2) as huiliuyonghuzhanbi
from t6 
group by login_date
)
select t4.login_date,concat(xinzengyonghuzhanbi,', ',liucunyonghuzhanbi,', ',huiliuyonghuzhanbi) as pct
from t4 join t7 using (login_date)
order by login_date asc

with t1 as (
select loc_nm,
case when loc_nm='将台西' then '地铁站' 
when loc_nm='北京机床研究所' then '写字楼'
else loc_type 
end as loc_type
from gd_loc_map
where (loc_type='写字楼' or loc_type='地铁站')
),
t2 as (
select user_id,date(start_time) as date
from hello_bike_riding_rcd h join t1 on h.start_loc=t1.loc_nm join t1 t11 on h.end_loc=t11.loc_nm 
where start_time>='2020-01-01' and end_time<'2025-01-01' and ((t1.loc_type='地铁站' and t11.loc_type='写字楼') or (t11.loc_type='地铁站' and t1.loc_type='写字楼'))
group by user_id,date(start_time)
),
t3 as (
select user_id,date_format(date,'%Y-%m-01') as month
from t2
group by user_id,date_format(date,'%Y-%m-01')
having count(*)>=5
),
t4 as (
select user_id,date_sub(month,interval row_number() over (partition by user_id order by month asc) month) as continuous_judge
from t3
),
t5 as (
select distinct user_id,1 as active_tag
from t4
group by user_id,continuous_judge 
having count(*)>=3 
),
t6 as (
select user_id
from t2
group by user_id
)
select * from t5

with t1 as (
select loc_nm,
case when loc_nm='将台西' then '地铁站' 
when loc_nm='北京机床研究所' then '写字楼'
else loc_type 
end as loc_type
from gd_loc_map
where (loc_type='写字楼' or loc_type='地铁站')
),
t2 as (
select user_id,date(start_time) as date
from hello_bike_riding_rcd h join t1 on h.start_loc=t1.loc_nm join t1 t11 on h.end_loc=t11.loc_nm 
where start_time>='2020-01-01' and end_time<'2025-01-01' and ((t1.loc_type='地铁站' and t11.loc_type='写字楼') or (t11.loc_type='地铁站' and t1.loc_type='写字楼'))
group by user_id,date(start_time)
),
t3 as (
select user_id,date_format(date,'%Y-%m-01') as month
from t2
group by user_id,date_format(date,'%Y-%m-01')
having count(*)>=5
),
t4 as (
select user_id,date_sub(month,interval row_number() over (partition by user_id order by month asc) month) as continuous_judge
from t3
),
t5 as (
select distinct user_id,1 as active_tag
from t4
group by user_id,continuous_judge 
having count(*)>=3 
),
t6 as (
select user_id
from t2
group by user_id
)
select t6.user_id,coalesce(active_tag,0) as active_tag
from t6 left join t5 on t6.user_id=t5.user_id 
order by t6.user_id asc

with t1 as (
select singer_id
from singer_info
where singer_name in ('周杰伦','蔡依林','Michael Jackson','Taylor Swift','Beyond')
),
t2 as (
select distinct user_id,date(start_time) as dt
from t1 join song_info s on t1.singer_id=s.origin_singer_id join listen_rcd l using (song_id)
order by user_id,dt
),
t3 as (
select user_id,date_sub(dt,interval row_number() over(partition by user_id order by dt asc) day) as continuous_judge
from t2
) ,
t4 as (
select user_id,continuous_judge,count(*) as continuous_days
from t3 
group by user_id,continuous_judge
)
select user_id,max(continuous_days) as max_consecutive_days
from t4 
group by user_id
order by user_id asc

with t1 as (
select video_id,duration 
from ks_video_inf
where duration>180
),
t2 as (
select uid
from t1 join ks_video_wat_log k2 using (video_id)
where start_time>=date_sub(curdate(),interval 1 month)
group by uid
having count(distinct case when timestampdiff(second,start_time,end_time)>=duration then video_id end)>=2
)
select count(*) as total_users
from t2

select k1.video_id,title,
round(100*count(case when timestampdiff(second,start_time,end_time)>=duration then 1 end)/count(*),2) as completion_rate
from ks_video_inf k1 join ks_video_wat_log k2 using (video_id)
where start_time>=date_sub(curdate(),interval 1 month)
group by k1.video_id,title
order by completion_rate desc
limit 5