SELECT 
    MAX(score) AS max_score, 
    MIN(score) AS min_score, 
    AVG(score) AS avg_score
FROM scores
WHERE student_id = 735011 AND subject = '语文';

select name, class_code, subject, graduate_date
 from teachers
 where graduate_date between "1994-01-01" and "1997-12-31" and gender ="f"
 order by graduate_date asc;

with yearly_top_merchants 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
),
monthly_top_merchants as (
    select 
        date_format(trx_time, '%Y-%m') as trx_mon, 
        mch_nm, 
        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 
        trx_mon, mch_nm
),
filtered_monthly_top_merchants as (
    select 
        trx_mon, 
        mch_nm, 
        sum_trx_amt
    from 
        monthly_top_merchants
    where 
        rn <= 3
)
select 
    '2024' as trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
    yearly_top_merchants
union all
select 
    trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
    filtered_monthly_top_merchants
order by 
    trx_mon, 
    sum_trx_amt desc;

select * from user_login_log limit 5;

with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
group by
usr_id, login_date
),
ranked_logins as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as row_num
from
daily_login
),
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_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(date(login_time), current_date)
group by
usr_id, login_date
),
ranked_logins as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as row_num
from
daily_login
),
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_user_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
group by
usr_id,date(login_time)
),
rnk as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as rnm
from
daily_user_login
),
grp as (
select
usr_id,
login_date,
login_date - rnm as grp
from
rnk
),
consecutive_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as cd
from
grp
group by
usr_id, grp
having
count(*) >= 3
)
select * from consecutive_days

with daily_user_login as (
select
	usr_id,
date(login_time) as login_date
from
	user_login_log
where
	datediff(current_date , date(login_time)) <= 90
group by
usr_id, login_date
), retention as (
select
t1.usr_id,
t1.login_date as login_date,
t2.login_date as next_login_date
from
daily_user_login t1
left join
daily_user_login t2 on t1.usr_id = t2.usr_id and datediff(t2.login_date, t1.login_date) between 1 and 14
)
select
	login_date,
count(distinct case when datediff(next_login_date, login_date) <= 3 then usr_id end) / count(distinct usr_id),
count(distinct case when datediff(next_login_date, login_date) <= 7 then usr_id end) / count(distinct usr_id),
count(distinct case when datediff(next_login_date, login_date) <= 14 then usr_id end) / count(distinct usr_id)
from
	retention
group by
	login_date

SELECT 
    v.video_id, 
    v.title, 
    ROUND(
        (COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN u.uid END) / 
         COUNT(DISTINCT u.uid)) * 100, 
        4
    ) AS completion_rate
FROM 
    ks_video_inf v
JOIN 
    ks_video_wat_log u ON v.video_id = u.video_id
WHERE 
    u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    v.video_id, v.title
ORDER BY 
    completion_rate DESC
LIMIT 5;

with login_d as (
select
	usr_id,
date(login_time) as login_date
from
	user_login_log
where
datediff(current_date,date(login_time)) <= 30
group by
	usr_id, login_date
),
grouped as (
select
usr_id,
login_date,
login_date - interval row_number()over(partition by usr_id order by login_date) day as grp
from
login_d
)
select
usr_id,
min(login_date),
max(login_date),
count(*)
from
	grouped
group by
	usr_id, grp
having count(*) >= 3

with f_t 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 = '红玫瑰按摩保健休闲'
)
select
distinct usr_id
from
f_t
where
datediff(trx_time, prev_3_trx_time) <= 3

with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where 
datediff(current_date, date(login_time)) <= 30
group by
usr_id, date(login_time)
), 
t2 as (
select
t1.usr_id,
t1.login_date,
datediff(t2.login_date, t1.login_date) as date_diff
from
daily_login t1
left join daily_login t2 on t1. usr_id = t2.usr_id and t2.login_date > t1.login_date
),
retention as (
select
login_date,
round(count(distinct case when date_diff = 1 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_1_retention_rate,
round(count(distinct case when date_diff = 3 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_3_retention_rate,
round(count(distinct case when date_diff = 7 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_7_retention_rate,
round(count(distinct case when date_diff = 14 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_14_retention_rate
from
t2
group by
login_date
)
select * from retention

with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where 
datediff(current_date, date(login_time)) <= 30
group by
usr_id, date(login_time)
), 
t2 as (
select
t1.usr_id,
t1.login_date,
datediff(t2.login_date, t1.login_date) as date_diff
from
daily_login t1
join daily_login t2 on t1. usr_id = t2.usr_id and t2.login_date > t1.login_date
),
retention as (
select
login_date,
round(count(distinct case when date_diff = 1 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_1_retention_rate,
round(count(distinct case when date_diff = 3 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_3_retention_rate,
round(count(distinct case when date_diff = 7 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_7_retention_rate,
round(count(distinct case when date_diff = 14 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_14_retention_rate
from
t2
group by
login_date
)
select * from retention

with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), 
max_num as(
select 
	live_id,
act_time,
	cnt,
max(cnt) over (partition by live_id) as max_cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.max_cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
where t1.cnt = t1.max_cnt
group by
	t1.live_id, t2.live_nm, t1.max_cnt
order by
	t1.max_cnt desc

with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), max_num as(
select 
	live_id,
act_time,
max(cnt) over (partition by live_id) as cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	t1.live_id, t2.live_nm, t1.cnt
order by
	t1.cnt desc

with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), max_num as(
select 
	live_id,
act_time,
max(cnt) over (partition by live_id) as cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	t1.live_id, t2.live_nm, t1.cnt
order by
	t1.cnt

with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
)
select 
	num.live_id,
t2.live_nm,
max(num.cnt)
from 
	num
left join
	ks_live_t2 t2 on num.live_id = t2.live_id
group by
	num.live_id, t2.live_nm
order by
	max(num.cnt) desc

with user_login_date as (
select
usr_id,	
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
group by
usr_id, login_date
),
ranked as (
select
usr_id,	
login_date,
row_number()over(partition by usr_id order by login_date) as rnk
from
user_login_date
),
grouped as (
select
usr_id,	
login_date,
login_date - interval rnk day as grp
from
ranked
)
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from
grouped
group by
usr_id, grp
having 
	count(*)> 2
order by
	usr_id asc, start_date

with user_login_date as (
select
usr_id,	
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
ranked as (
select
usr_id,	
login_date,
row_number()over(partition by usr_id order by login_date) as rnk
from
user_login_date
),
grouped as (
select
usr_id,	
login_date,
login_date - interval rnk day as grp
from
ranked
)
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from
grouped
group by
usr_id,grp
having 
	count(1)> 2
order by
	usr_id asc, start_date

with user_login_date as (
select
usr_id,	
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
ranked as (
select
usr_id,	
login_date,
row_number()over(partition by usr_id order by login_date) as rnk
from
user_login_date
),
grouped as (
select
usr_id,	
login_date,
login_date - interval rnk day as grp
from
ranked
)
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from
grouped
group by
usr_id,grp
having 
	count(1)> 2