with quchong as(
select distinct
usr_id,
date(login_time) as login_date
from user_login_log
where login_time > date_sub(curdate(),interval 30 day)
),
jihao as(
select
usr_id,
login_date,
row_number() over(partition by usr_id order by login_date ) as rn
from quchong ),
zj as(
select
usr_id,
login_date,
date_sub(login_date,interval rn day) as zhengju
from jihao
)
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from zj
group by usr_id,zhengju
having count(*) >=3
ORDER BY consecutive_days DESC, usr_id
select snd_usr_id from(
select
snd_usr_id,
count(*) as cishu
from(
select
snd_usr_id,
pkt_amt
from tx_red_pkt_rcd
where pkt_amt ="520" or pkt_amt ="200") f
group by snd_usr_id
having cishu >=5) s
select snd_usr_id as User_id,Total_Sent_Amount,Total_Received_Amount,
(
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
where (
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 between 80 and 99
order by Clown_Index desc
select snd_usr_id as User_id,Total_Sent_Amount,Total_Received_Amount,
(
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
where (
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 between 88 and 99
order by Clown_Index desc
select snd_usr_id,Total_Sent_Amount,Total_Received_Amount,
(
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
where (
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100 between 88 and 99
order by Clown_Index desc
select snd_usr_id,Total_Sent_Amount,Total_Received_Amount,
ROUND((
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100, 2) as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
where ROUND((
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100, 2) between 88 and 99
order by Clown_Index desc
select *
from(
select snd_usr_id,Total_Sent_Amount,Total_Received_Amount,
ROUND((
1 - Total_Received_Amount / NULLIF(Total_Sent_Amount, 0)) * 100, 2) as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
order by Clown_Index desc) s
where Clown_Index between 88 and 99
select *
from(
select snd_usr_id,Total_Sent_Amount,Total_Received_Amount,
(1-(Total_Received_Amount/Total_Sent_Amount))*100 as Clown_Index
from
(select
snd_usr_id,
sum(pkt_amt) as Total_Sent_Amount,
sum(case when rcv_datetime>"1900-01-01 00:00:00" then pkt_amt else 0 end) as Total_Received_Amount
from tx_red_pkt_rcd
group by 1) as t
order by Clown_Index desc) s
where Clown_Index between 88 and 99
select
r.snd_usr_id,
sum(r.pkt_amt) as sum_trx_amt
from
tx_red_pkt_rcd r join tx_usr_bas_info i on r.snd_usr_id=i.usr_id
group by r.snd_usr_id
order by
sum_trx_amt desc
limit 10
WITH UserMonthly AS (
SELECT DISTINCT
usr_id,
DATE_FORMAT(login_time, '%Y-%m') AS login_month
FROM user_login_log
WHERE login_time >= '2024-01-01' AND login_time < '2025-02-01'
)
SELECT
t1.login_month AS current_month,
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS monthly_retention_rate
FROM
UserMonthly t1
LEFT JOIN
UserMonthly t2 ON t1.usr_id = t2.usr_id
AND t2.login_month = DATE_FORMAT(DATE_ADD(CONCAT(t1.login_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
WHERE
t1.login_month LIKE '2024%'
GROUP BY
t1.login_month
ORDER BY
current_month;
with data as(
select
distinct usr_id ,
date(login_time) as login_date
from user_login_log
where datediff(current_date,date(login_time))<=90
)
select
t1.login_date as first_login_date,
round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 3 then t2.usr_id end ) /count(distinct t1.usr_id)*100,2) as t_plus_3_retention_rate,
round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 7 then t2.usr_id end ) /count(distinct t1.usr_id)*100,2) as t_plus_7_retention_rate,
round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 14 then t2.usr_id end) /count(distinct t1.usr_id)*100,2) as t_plus_14_retention_rate
from data t1
left join data t2
on t1.usr_id=t2.usr_id and t1.login_date <t2.login_date
group by first_login_date
order by first_login_date
with shoushen as(
select
distinct usr_id ,
date(login_time) as login_date
from user_login_log
where datediff(current_date,date(login_time))<=30
)
select
t1.login_date as first_login_date,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 1 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
t_plus_1_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 3 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
t_plus_3_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 7 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
t_plus_7_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 14 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
t_plus_14_retention_rate
from shoushen t1
left join shoushen t2
on t1.usr_id=t2.usr_id and t2.login_date >t1.login_date
group by first_login_date
order by first_login_date
SELECT
t1.login_date,
CONCAT(ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2), "%") AS T1_retention_rate
FROM
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE DATE(login_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) t1
LEFT JOIN
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
) t2
ON t1.usr_id = t2.usr_id
AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
GROUP BY
t1.login_date
ORDER BY
t1.login_date ASC;
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
T.usr_id,
T.login_date as T_date,
T_1.login_date as T_1_date
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id
and datediff(T.login_date, T_1.login_date) = -1
)
select
T_date as first_login_date,
concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate
from
data2
group by
T_date
order by
T_date;
WITH user_login AS (
SELECT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
login_time >= CURDATE() - INTERVAL 30 DAY
GROUP BY
usr_id, login_date
),
t_day_users AS (
SELECT
login_date AS t_date,
COUNT(DISTINCT usr_id) AS A
FROM
user_login
GROUP BY
login_date
),
t_plus_one_users AS (
SELECT
ul1.login_date AS t_date,
COUNT(DISTINCT ul2.usr_id) AS B
FROM
user_login ul1
LEFT JOIN
user_login ul2
ON
ul1.usr_id = ul2.usr_id
AND ul1.login_date = DATE_SUB(ul2.login_date, INTERVAL 1 DAY)
GROUP BY
ul1.login_date
)
SELECT
t1.t_date AS login_date,
ROUND(IFNULL(t2.B / t1.A * 100, 0), 2) AS T1_retention_rate
FROM
t_day_users t1
LEFT JOIN
t_plus_one_users t2
ON
t1.t_date = t2.t_date
ORDER BY
t1.t_date DESC;
select
a.login_date,
concat(round(count(b.usr_id)/count(a.usr_id),2)*100,'%')as T1_retention_rate
from
(select
distinct usr_id,
date(login_time) as login_date
from user_login_log )a
left join
(select distinct usr_id,
date(login_time) as login_date
from user_login_log) b
ona.usr_id=b.usr_id
and b.login_date=date_add(a.login_date ,interval 1 day)
where a.login_date >= curdate() - interval 30 day
and a.login_date<= curdate()
group by a.login_date
order by a.login_date