WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
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 BETWEEN 1 AND 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 BETWEEN 1 AND 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 BETWEEN 1 AND 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

WITH daily_unique_logins 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)
),
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
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

with data1 as (
	select	
		usr_id
		,date(login_time) as login_date
	from
		user_login_log
	where
		datediff(current_date,date(login_time))<=30
	group by
		1,2
),
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 friend_steps as (
	select
		t1.user1 as user_id
		,t1.user2 as friend_id
		,t2.steps
	from
		friend_relationships as t1
		inner join 
		user_steps as t2
		on 	t1.user2=t2.user_id
	union
	select
		user_id
		,user_id as friend_id
		,steps
	from
		user_steps
),
ranked_steps as (
	select
		fs.user_id
		,fs.friend_id
		,fs.steps
		,rank()over(partition by fs.user_id order by fs.steps desc) as row_num
	from
		friend_steps fs
)
select
	rs.user_id
,rs.row_num
from
	ranked_steps rs
where
	rs.user_id=rs.friend_id

withfriend_summary1 as (
	select
		f.user1
		,f.user2
		,u.steps as steps2
	from
		friend_relationships as f
		inner join
		user_steps as u
		on f.user2=u.user_id
),
friend_summary2 as (
	select
		f.user1
		,f.user2
		,f.steps2
		,u.steps as steps1
	from
		friend_summary1 as f
		inner join
		user_steps as u
		on f.user1=u.user_id
)
select
	user1
	,sum(case when steps2<steps1 then 0 else 1 end)+1 as num
from
	friend_summary2
group by
	user1

select
	cust_uid
,count(distinct trx_dt) as transaction_count
from
	mt_trx_rcd_f
group by
	cust_uid
order by
	transaction_count desc

select
	cust_uid
	,count(cust_uid) as transaction_count
from
	mt_trx_rcd_f
group by
	cust_uid
order by
	transaction_count desc

select 
    * 
from
    numbers_for_fun
where
    a = 0 and b<>0 and c<>0
order by 1

select
	*
from
	numbers_for_fun
where
	a=0
and b!=0

select
	*
from
	numbers_for_fun
where
	a=0
and abs(-(c/b))*abs(c)>=10

select
	*
from
	numbers_for_fun
where
	a=0
and abs(-(c/b))*abs(c)>=5

select 
    * 
from 
    numbers_for_fun
where 
    a=0 and b in (1,-1)
order by 
    id

select
	*
from
	numbers_for_fun
where
	a=0
and c!=0
and (b=1 or b=-1)
order by	
	id

select
	t2.live_id
,t2.live_nm
,count(usr_id) as enter_cnt 
from
	ks_live_t2 as t2
left join
ks_live_t1 as t1
using(live_id)
where
	(substr(enter_time,1,10)='2021-09-12' and hour(enter_time)=23)
group by
	t2.live_id
,t2.live_nm
order by
	enter_cnt desc
limit
	5

select
	t2.live_id
,t2.live_nm
,count(usr_id) as enter_cnt 
from
	ks_live_t2 as t2
left join
ks_live_t1 as t1
using(live_id)
where
	(substr(enter_time,1,10)='2021-09-12' and hour(enter_time)=23)
group by
	t2.live_id
,t2.live_nm
order by
	enter_cnt desc

with monthly_trx as (
    select 
        date_format(t.trx_time, '%Y-%m') as trx_mon, 
        sum(t.trx_amt) as monthly_trx_amt
    from 
        cmb_usr_trx_rcd t
    join 
        cmb_mch_typ m on t.mch_nm = m.mch_nm
    where 
        t.usr_id = 5201314520 
        and m.mch_typ = '休闲娱乐'
        and year(t.trx_time) between 2023 and 2024
    group by 
        trx_mon
    order by 
        trx_mon
),
cumulative_trx as (
    select 
        trx_mon, 
        monthly_trx_amt,
        sum(monthly_trx_amt) over ( order by trx_mon) as cumulative_trx_amt
    from 
        monthly_trx
)
select 
    trx_mon, 
    cumulative_trx_amt as trx_amt
from 
    cumulative_trx
order by 
    trx_mon;

WITH date_summary AS (
SELECT DISTINCT DATE_FORMAT(date_value, '%Y-%m') AS trx_mon
FROM date_table
WHERE DATE_FORMAT(date_value, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
),
mch_summary AS (
SELECT 
u.usr_id,
u.mch_nm,
u.trx_time,
DATE_FORMAT(u.trx_time, '%Y-%m-%d') AS trx_date,
u.trx_amt,
m.mch_typ
FROM 
cmb_usr_trx_rcd AS u
LEFT JOIN 
cmb_mch_typ AS m ON u.mch_nm = m.mch_nm
WHERE 
u.usr_id = 5201314520
AND DATE_FORMAT(u.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
AND (m.mch_typ = '休闲娱乐' OR m.mch_typ IS NULL)
AND u.trx_amt >= 288
AND HOUR(u.trx_time) IN (23, 0, 1, 2)
),
total_summary AS (
SELECT 
d.trx_mon,
COALESCE(LAST_DAY(MAX(m1.trx_time)), '1900-01-01') AS last_day,
COALESCE(DAY(LAST_DAY(MAX(m1.trx_time))), 0) AS day_of_mon,
COALESCE(SUM(m1.trx_amt), 0) AS trx_amt,
COALESCE(COUNT(m1.trx_amt), 0) AS trx_cnt,
COALESCE(ROUND(SUM(m1.trx_amt) / DAY(LAST_DAY(MAX(m1.trx_time))), 2), 0) AS avg_day_amt,
COALESCE(ROUND(COUNT(m1.trx_amt) / DAY(LAST_DAY(MAX(m1.trx_time))), 2), 0) AS avg_day_cnt
FROM 
date_summary AS d
LEFT JOIN 
mch_summary AS m1 ON d.trx_mon = DATE_FORMAT(m1.trx_time, '%Y-%m')
GROUP BY 
d.trx_mon
)
SELECT 
trx_mon,
last_day,
day_of_mon,
trx_amt,
trx_cnt,
avg_day_amt,
avg_day_cnt
FROM 
total_summary
ORDER BY 
trx_mon;

select
	substr(u.trx_time,1,7) as trx_mon
,max(last_day(u.trx_time)) as last_day
,day(max(last_day(u.trx_time))) as days_of_mon
,sum(u.trx_amt) as trx_amt
,count(u.trx_amt) as trx_cnt
,sum(u.trx_amt)/day(max(last_day(u.trx_time))) as avg_day_amt
,count(u.trx_amt)/day(max(last_day(u.trx_time))) as avg_day_cnt
from
	cmb_usr_trx_rcd as u
inner join
cmb_mch_typ as m
using (mch_nm)
where
	u.usr_id=5201314520
and year(u.trx_time) in('2023','2024')
and m.mch_typ='休闲娱乐'
group by
	substr(u.trx_time,1,7)
order by	
	trx_mon

select
	substr(u.trx_time,1,7) as trx_mon
,max(last_day(u.trx_time)) as last_day
,day(max(last_day(u.trx_time))) as days_of_mon
from
	cmb_usr_trx_rcd as u
inner join
cmb_mch_typ as m
using (mch_nm)
where
	u.usr_id=5201314520
and year(u.trx_time) in('2023','2024')
and m.mch_typ='休闲娱乐'
group by
	substr(u.trx_time,1,7)
order by	
	trx_mon

SELECT 
m.mch_typ, 
u.mch_nm,
COUNT(u.trx_amt) AS trx_cnt, 
SUM(u.trx_amt) AS trx_amt
FROM 
cmb_usr_trx_rcd u
LEFT JOIN 
cmb_mch_typ m ON u.mch_nm = m.mch_nm
WHERE 
u.usr_id = '5201314520' and left(trx_time,4)=2024
and m.mch_typ is null
GROUP BY 
	m.mch_typ, 
u.mch_nm
ORDER BY
3 DESC