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 (
select distinct 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;
求指点这个思路的错误呀,一直是0过不去
with data1 as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time,
ks_live_t1.leave_time,
ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
live_id,
live_nm,
enter_time as action_time,
1 as flag
from data1
union all
select
live_id,
live_nm,
leave_time as action_time,
-1 as flag
from data1
),
data3 as(
select
live_id,
live_nm,
action_time,
sum(flag)over(partition by live_id,live_nm order by action_time) as each_cnt
from data2
),
data4 as(
select
live_id,
live_nm,
action_time,
each_cnt,
max(each_cnt)over(partition by live_id,live_nm) as max_cnt
from data3
)
select
live_id,
live_nm,
max_cnt as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from data4
where each_cnt = max_cnt
group by live_id,live_nm,max_cnt
order by max_cnt desc;
with data1 as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time,
ks_live_t1.leave_time,
ks_live_t2.live_nm
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
live_id,
live_nm,
enter_time as action_time,
1 as flag
from data1
union all
select
live_id,
live_nm,
leave_time as action_time,
-1 as flag
from data1
),
data3 as(
select
live_id,
live_nm,
action_time,
sum(flag)over(partition by live_id,live_nm order by action_time) as each_cnt
from data2
),
data4 as(
select
live_id,
live_nm,
action_time,
each_cnt,
max(each_cnt)over(partition by live_id,live_nm) as max_cnt
from data3
)
select
live_id,
live_nm,
max_cnt as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from data4
where each_cnt = max_cnt
group by live_id,live_nm,max_cnt
with data1 as(
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
enter_time,
leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
live_id,
live_nm,
enter_time as action_time,
1 as flag
from data1
union all
select
live_id,
live_nm,
leave_time as action_time,
-1 as flag
from data1
)
select
live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
order by max_online_users desc;
with data1 as(
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
enter_time,
leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
live_id,
live_nm,
enter_time as action_time,
1 as flag
from data1
union
select
live_id,
live_nm,
leave_time as action_time,
-1 as flag
from data1
)
select
live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
order by max_online_users desc;
with data1 as(
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
enter_time,
leave_time
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
),
data2 as(
select
live_id,
live_nm,
enter_time as action_time,
1 as flag
from data1
union
select
live_id,
live_nm,
leave_time as action_time,
-1 as flag
from data1
)
select
live_id,
live_nm,
max(each_num) as max_online_users
from(
select
live_id,
live_nm,
action_time,
sum(flag) over(partition by live_id,live_nm order by action_time) as each_num
from data2
)a
group by live_id,live_nm
with data1 as(
select
ks_live_t1.live_id,
ks_live_t2.live_type,
ks_live_t2.live_nm,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as enter_duration,
count(distinct ks_live_t1.usr_id) as enter_cnt
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
where enter_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59'
group by live_type,live_id,live_nm
),
data2 as(
select
live_type,
live_id,
live_nm,
enter_duration,
enter_cnt,
enter_duration / enter_cnt as avg_duration
from data1
),
data3 as(
select
live_type,
live_id,
live_nm,
enter_duration,
enter_cnt,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc) as rn
from data2
)
select
live_id,
live_nm,
live_type,
enter_duration as total_duration,
enter_cnt as total_users,
avg_duration
from data3
where rn = 1
order by live_id;
with data1 as(
select
ks_live_t1.live_id,
live_type,
live_nm,
count(*) as enter_cnt,
row_number()over(partition by ks_live_t2.live_type order by count(*) desc)as rn
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id = ks_live_t2.live_id
where date_format(ks_live_t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by ks_live_t1.live_id,ks_live_t2.live_type,ks_live_t2.live_nm
)
select
live_id,
live_nm,
live_type,
enter_cnt
from
data1
where rn = 1
order by live_id;
select
order_date,
goods_id,
total_gmv,
ranking
from(
select
order_date,
goods_id,
total_gmv,
row_number()over(partition by order_date order by total_gmv) as ranking
from(
select
date_format(order_time,'%Y-%m-%d') as order_date,
goods_id,
sum(order_gmv) as total_gmv
from order_info
wheredate_format(order_time,'%Y-%m')='2024-10'
group by date_format(order_time,'%Y-%m-%d'),goods_id
)a
)b
where ranking <= 3
with data0 as (
select
usr_id,
trx_time,
trx_amt,
mch_nm,
lag(trx_time, 3) over (partition by usr_id order by trx_time) as prev_3_trx_time
from
cmb_usr_trx_rcd
where
mch_nm = '红玫瑰按摩保健休闲'
),
data1 as (
select
usr_id,
trx_time,
trx_amt,
mch_nm,
datediff(trx_time,prev_3_trx_time) as diff
from
data0
)
select distinct usr_id
from data1
where diff <= 3
order by usr_id
select
usr_id,
trx_time,
trx_amt,
mch_nm,
pre_trx_time,
datediff(trx_time,pre_trx_time) as days_since_last_fvck
from(
select
usr_id,
trx_time,
trx_amt,
mch_nm,
lag(trx_time,1) over(partition by mch_nm order by trx_time) as pre_trx_time
from cmb_usr_trx_rcd
where usr_id=5201314520
and mch_nm = '红玫瑰按摩保健休闲'
)a
select
usr_id,
trx_time,
trx_amt,
mch_nm,
lag(trx_time,1) over(partition by mch_nm order by trx_amt) as pre_trx_time,
datediff(trx_time,lag(1) over(partition by mch_nm order by trx_amt)) as days_since_last_fvck
from cmb_usr_trx_rcd
where usr_id=5201314520
and mch_nm = '红玫瑰按摩保健休闲'
order by trx_time
with data0 as (
select
cmb_usr_trx_rcd.mch_nm,
trx_time,
trx_amt
from cmb_usr_trx_rcd
left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
where cmb_mch_typ.mch_typ = '休闲娱乐' and usr_id=5201314520
),
data1 as (
select date_format(trx_time,'%Y-%m') as trx_mon,
trx_amt
from data0
where date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
)
,data2 as (
select
trx_mon,
sum(trx_amt) as monthly_amt
from data1
group by trx_mon
)
select
trx_mon,
sum(monthly_amt) over(order by trx_mon) as trx_amt
from data2
with data1 as (
select date_format(trx_time,'%Y-%m') as trx_mon,
trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520
and date_format(trx_time,'%Y-%m') between '2023-01' and '2024-12'
)
,data2 as (
select
trx_mon,
sum(trx_amt) as monthly_amt
from data1
group by trx_mon
)
select
trx_mon,
sum(monthly_amt) over(order by trx_mon) as trx_amt
from data2
with data0 as (
select usr_id,
cmb_usr_trx_rcd.mch_nm,
trx_time,
trx_amt,
mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
mch_nm,
count(*) as cnt,
row_number()over(partition by mch_typ order by count(*) desc) as rn
from data0
where usr_id = '5201314520'
and mch_typ is not null
and mch_typ in('交通出行','休闲娱乐','咖啡奶茶')
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
count(*) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt desc
limit 1
)
select
'all' as mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data2
union all
select mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data1
where rn = 1;
with data0 as (
select usr_id,
cmb_usr_trx_rcd.mch_nm,
trx_time,
trx_amt,
mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
mch_nm,
count(*) as cnt,
row_number()over(partition by mch_typ order by count(*) desc) as rn
from data0
where usr_id = '5201314520'
and mch_typ is not null
and mch_typ in('交通出行','休闲娱乐','咖啡奶茶')
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
count(*) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt
limit 1
)
select
'all' as mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data2
union all
select mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data1
where rn = 1;
with data0 as (
select usr_id,
cmb_usr_trx_rcd.mch_nm,
trx_time,
trx_amt,
mch_typ
from cmb_usr_trx_rcd left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
)
,data1 as (
select mch_typ,
mch_nm,
count(1) as cnt,
row_number()over(partition by mch_typ,mch_nm order by count(1) desc) as rn
from data0
where usr_id = '5201314520'
group by mch_typ,mch_nm
)
,data2 as (
select mch_nm,
count(1) as cnt
from data0
where usr_id = '5201314520'
group by mch_nm
order by cnt
limit 1
)
select
'all' as mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data2
union all
select mch_typ,
mch_nm,
cnt as trx_cnt,
1 as rnk
from data1
where rn = 1;
with data1 as (
select mch_nm,
sum(trx_amt) as sum_trx_amt
from
cmb_usr_trx_rcd
where
usr_id = '5201314520'
and year(trx_time) = '2024'
group by mch_nm
order by sum_trx_amt desc
limit 3
),
data2 as (
select mch_nm,
date_format(trx_time,'%Y-%m') as trx_month,
sum(trx_amt) as sum_trx_amt,
row_number()over(partition by date_format(trx_time,'%Y-%m') order by sum(trx_amt) desc) as rn
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = '2024'
group by mch_nm,trx_month
),
data3 as (
select trx_month,
mch_nm,
sum_trx_amt
from
data2
where rn <= 3
)
select
'2024' as trx_mon,
mch_nm,
sum_trx_amt
from data1
union all
select
trx_month as trx_mon,
mch_nm,
sum_trx_amt
from data3
order by trx_mon,
sum_trx_amt desc;
select
mch_nm as merchant_name,
case
when mch_nm rlike '.*-.*(多多|寻梦).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟|优酷|阿里).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条|巨量|飞书).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a
select
mch_nm as merchant_name,
case
when mch_nm rlike '.*-.*(多多|寻梦信息).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟|优酷|阿里).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a
select
mch_nm as merchant_name,
case
when mch_nm rlike '.*-.*(多多|寻梦信息).*' then '拼多多'
when mch_nm rlike '.*-.*(京东|德邦|达达).*' then '京东'
when mch_nm rlike '.*-.*(淘宝|天猫|阿里妈妈|1688|咸鱼|聚好省|菜鸟).*' then '淘系'
when mch_nm rlike '.*-.*(小红书|行吟).*' then '小红书'
when mch_nm rlike '.*-.*(抖音|字节|火山|头条).*' then '抖音'
else '其他'
end as platform
from (
select distinct mch_nm
from ccb_trx_rcd
)a