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 '淘系'
else '其他'
end as platform
from
ccb_trx_rcd
group by mch_nm
select
sum(case when days between 1 and 5 then 1 else 0 end) as days_1_to_5,
sum(case when days between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when days between 11 and 20 then 1 else 0 end) as days_11_to_20,
sum(case when days >20 then 1 else 0 end) as days_over_20
from
(select
usr_id,
count(distinct login_day) as days
from
(select
usr_id,
date(login_time) as login_day
from
user_login_log
where
login_time>date_sub('2024-10-01 12:23:24',interval 180 day)
and login_time<='2024-10-01 12:23:24'
)t
group by usr_id
)t1
select
count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from
user_login_log
where
login_time<=last_day(date_sub(current_date,interval 2 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 3 month)),interval 1 day)
select
count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from
user_login_log
where
login_time<=last_day(date_sub(current_date,interval 1 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 2 month)),interval 1 day)
select
count(distinct(case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then usr_id end)) as commute,
count(distinct(case when hour(login_time) between 11 and 13 then usr_id end)) as lunch_break,
count(distinct(case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 thenusr_id end)) as bedtime
from
user_login_log
select
sum((case when hour(login_time) between 7 and 9 or hour(login_time) between 18 and 20 then 1 else 0 end)) as commute,
sum((case when hour(login_time) between 11 and 13 then 1 else 0 end)) as lunch_break,
sum((case when hour(login_time) between 22 and 24 or hour(login_time) between 0 and 1 then 1 else 0 end)) as bedtime
from
user_login_log
SELECT
COUNT(DISTINCT usr_id) AS active_users
FROM
user_login_log
where
login_time<=last_day(date_sub(current_date,interval 1 month))
and login_time>=date_add(last_day(date_sub(current_date,interval 2 month)),interval 1 day)
select
gd_inf.gd_id,
gd_inf.gd_nm,
count(*) as fav_count
from
gd_inf
right join
xhs_fav_rcd
on gd_inf.gd_id=xhs_fav_rcd.mch_id
group by
gd_id,
gd_nm
limit 1
select
year(dt) as Y,
cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as beijing,
cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as shanghai,
cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as shenzhen,
cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as guangzhou
from
weather_rcd_china
group by year(dt)
select
city,
sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from
weather_rcd_china
where
month(dt) in (12,1,2)
group by city
order by 2 desc
select
city,
sum(case when con like '%雪%' then 1 else 0 end) as snowny_days
from
weather_rcd_china
where
month(dt) in (12,1,2)
group by city
order by 2 desc
select
city,
sum(case when con like '%雪%' then 1 else 0 end) as cloudy_days
from
weather_rcd_china
where
month(dt) in (12,1,2)
group by city
order by 2 desc
select
city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 0 else 1 end)/count(con)*100 as decimal(4,2)),'%') as p
from
weather_rcd_china
where year(dt)=2021
group by city
order by 3 desc
select
city,
sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%' then 0 else 1 end)/count(con)*100 as decimal(4,2)),'%') as p
from
weather_rcd_china
group by city
order by 3 desc
select
distinct city,
CAST(avg(tmp_h) over(partition by city,year(dt)) AS DECIMAL(4,2)) as avg_tmp_h
from
weather_rcd_china
WHERE YEAR(dt)=2021
order by 2 desc