with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<= 30
),
ranked_daily_unique_logins as (
select
distinct usr_id,
login_date,
row_number()over(partition by usr_id order by login_date asc) as rn
from
daily_unique_logins
where
datediff(current_date,login_date)<= 30
),
date_groups as (
select
usr_id,
login_date,
date_sub(login_date, interval rn day) as date_group
from
ranked_daily_unique_logins
),
continuous_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(date_group) as consecutive_days
from
date_groups
group by
usr_id,
date_group
)
select
usr_id,
start_date,
end_date,
consecutive_days
from
continuous_days
where
consecutive_days >=3
order by
usr_id,
start_date
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date,
row_number()over(partition by usr_id order by login_time asc) as rn
from
user_login_log
where
datediff(current_date,login_time)<= 30
),
date_groups as (
select
usr_id,
login_date,
date_sub(login_date, interval rn day) as date_group
from
daily_unique_logins
),
continuous_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(date_group) as consecutive_days
from
date_groups
group by
usr_id,
date_group
)
select
usr_id,
start_date,
end_date,
consecutive_days
from
continuous_days
where
consecutive_days >=3
order by
usr_id,
start_date
WITH user_login_days AS (
SELECT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
usr_id, DATE(login_time)
),
ranked_logins AS (
SELECT
usr_id,
login_date,
ROW_NUMBER() OVER (PARTITION BY usr_id ORDER BY login_date) AS row_num
FROM
user_login_days
),
grouped_logins AS (
SELECT
usr_id,
login_date,
login_date - INTERVAL row_num DAY AS grp
FROM
ranked_logins
),
consecutive_logins AS (
SELECT
usr_id,
MIN(login_date) AS start_date,
MAX(login_date) AS end_date,
COUNT(*) AS consecutive_days
FROM
grouped_logins
GROUP BY
usr_id, grp
HAVING
COUNT(*) > 2
)
SELECT
usr_id,
start_date,
end_date,
consecutive_days
FROM
consecutive_logins
ORDER BY
usr_id ASC,
start_date ;
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date,
row_number()over(partition by usr_id order by login_time asc) as rn
from
user_login_log
where
datediff(current_date,login_time)<= 30
),
date_groups as (
select
usr_id,
login_date,
date_sub(login_date, interval rn day) as date_group
from
daily_unique_logins
),
continuous_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(date_group) as consecutive_days
from
date_groups
group by
usr_id,
date_group
)
select
usr_id,
start_date,
end_date,
consecutive_days
from
continuous_days
where
consecutive_days >=3
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date,
row_number()over(partition by usr_id order by login_time asc) as rn
from
user_login_log
where
datediff(current_date,login_time)<= 30
),
date_groups as (
select
usr_id,
login_date,
date_sub(login_date, interval rn day) as date_group
from
daily_unique_logins
),
continuous_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(date_group) as consecutive_days
from
date_groups
group by
usr_id,
login_date
)
select
usr_id,
start_date,
end_date,
consecutive_days
from
continuous_days
where
consecutive_days >=3
WITH monthly_unique_logins AS (
SELECT
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM
user_login_log
WHERE
login_time >= '2024-01-01' AND login_time < '2025-01-01'
GROUP BY
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01')
),
next_month_logins AS (
SELECT
mul1.usr_id,
mul1.login_month AS current_month,
mul2.login_month AS next_month
FROM
monthly_unique_logins mul1
LEFT JOIN
monthly_unique_logins mul2
ON
mul1.usr_id = mul2.usr_id AND
mul2.login_month = DATE_ADD(mul1.login_month, INTERVAL 1 MONTH)
)
SELECT
current_month,
ROUND(COUNT(DISTINCT CASE WHEN next_month IS NOT NULL THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_month_retention_rate
FROM
next_month_logins
WHERE
current_month >= '2024-01-01' AND current_month < '2024-12-01'
GROUP BY
current_month
ORDER BY
current_month;
with monthly_unique_logins as (
select
distinct usr_id,
date_format(login_time,'%Y-%m-01') as login_month
from
user_login_log
where
login_time >= '2024-01-01' and login_time < '2025-01-01'
),
retention_days as (
select
mul1.usr_id,
mul1.login_month as current_month,
mul2.login_month as next_login_month
from
monthly_unique_logins mul1
left join
monthly_unique_logins mul2
on
mul1.usr_id = mul2.usr_id
and
mul2.login_month = date_add(mul1.login_month, interval 1 month)
)
select
current_month,
ROUND(COUNT(DISTINCT CASE WHEN next_login_month IS NOT NULL THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_month_retention_rate
from
retention_days
group by
current_month
order by
current_month
with monthly_unique_logins as (
select
distinct usr_id,
date_format(login_time,'%Y-%m-01') as login_month
from
user_login_log
where
login_time >= '2024-01-01' and login_time < '2024-12-01'
),
retention_days as (
select
mul1.usr_id,
mul1.login_month as current_month,
mul2.login_month as next_login_month
from
monthly_unique_logins mul1
left join
monthly_unique_logins mul2
on
mul1.usr_id = mul2.usr_id
and
mul2.login_month = date_add(mul1.login_month, interval 1 month)
)
select
current_month,
round(avg(next_login_month is not null)*100.0,2) as t_plus_1_month_retention_rate
from
retention_days
group by
current_month
order by
current_month
with monthly_unique_logins as (
select
distinct usr_id,
date_format(login_time,'%Y-%m-01') as login_month
from
user_login_log
where
login_time >= '2024-01-01' and login_time < '2025-01-01'
),
retention_days as (
select
mul1.usr_id,
mul1.login_month as current_month,
mul2.login_month as next_login_month
from
monthly_unique_logins mul1
left join
monthly_unique_logins mul2
on
mul1.usr_id = mul2.usr_id
and
mul2.login_month = date_add(mul1.login_month, interval 1 month)
)
select
current_month,
round(avg(next_login_month is not null)*100.0,2) as t_plus_1_month_retention_rate
from
retention_days
group by
current_month
order by
current_month
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<=90
),
retention_days as (
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
on
dul1.usr_id = dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff <= 3 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff <= 7 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when days_diff <= 14 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_14_retention_rate
from
retention_days
group by
first_login_date
order by
first_login_date
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<=30
),
retention_days as (
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
on
dul1.usr_id = dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff <= 3 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff <= 7 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when days_diff <= 14 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_14_retention_rate
from
retention_days
group by
first_login_date
order by
first_login_date
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<=30
),
retention_days as (
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
on
dul1.usr_id = dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff <= 1 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_1_retention_rate,
round(count(distinct case when days_diff <= 3 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff <= 7 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when days_diff <= 14 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_14_retention_rate
from
retention_days
group by
first_login_date
order by
first_login_date
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<=30
),
retention_days as (
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
on
dul1.usr_id = dul2.usr_id and
dul2.login_date = dul1.login_date + interval 1 day
)
select
first_login_date,
concat(round(count(distinct case when days_diff = 1 then usr_id end) *100.0 / count(distinct usr_id),2),'%') as t_plus_1_retention_rate
from
retention_days
group by
first_login_date
order by
first_login_date
with daily_unique_logins as (
select
distinct usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date,login_time)<=30
),
retention_days as (
select
dul1.usr_id,
dul1.login_date as first_login_date,
dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from
daily_unique_logins dul1
left join
daily_unique_logins dul2
on
dul1.usr_id = dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff = 1 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_1_retention_rate,
round(count(distinct case when days_diff = 3 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff = 7 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when days_diff = 14 then usr_id end) *100.0 / count(distinct usr_id),2) as t_plus_14_retention_rate
from
retention_days
group by
first_login_date
order by
first_login_date
with specific_cust_visited as (
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
cust_uid = 'MT10000' and
mch_nm = '兰州李晓明拉面馆'
),
all_cust_visited as (
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
mch_nm = '兰州李晓明拉面馆'
)
select
distinct scv.cust_uid,
acv.cust_uid as cust_uid_1,
scv.mch_nm
from
specific_cust_visited scv
left join
all_cust_visited acv
on scv.cust_uid != acv.cust_uid
order by
acv.cust_uid asc
select *
from
(select
cust_uid
from
mt_trx_rcd1
where
cust_uid='MT10000'
group by
cust_uid) a
left join
(select
cust_uid, mch_nm
from
mt_trx_rcd1
where
mch_nm='兰州李晓明拉面馆'
group by
cust_uid, mch_nm
order by 1) b
on a.cust_uid <> b.cust_uid
select
distinct m1.cust_uid as cust_uid,
m2.cust_uid as cust_uid_1,
m1.mch_nm
from
mt_trx_rcd1 m1
join
mt_trx_rcd1 m2
on m1.mch_nm = m2.mch_nm
where
m1.cust_uid = 'MT10000'
and m1.mch_nm = '兰州李晓明拉面馆'
and m2.cust_uid != 'MT10000'
order by
m2.cust_uid asc
select
m1.cust_uid as cust_uid,
m2.cust_uid as cust_uid_1,
m1.mch_nm
from
mt_trx_rcd1 m1
join
mt_trx_rcd1 m2
on m1.mch_nm = m2.mch_nm
where
m1.cust_uid = 'MT10000'
and m1.mch_nm = '兰州李晓明拉面馆'
and m2.cust_uid != 'MT10000'
order by
m2.cust_uid asc