with new_user as(
select *
from
(select usr_id as user_id,date_format(min(login_time),'%Y-%m-01') as min_log_month
from user_login_log
group BY usr_id) as t
where min_log_month>='2024-01-01' and min_log_month<'2025-01-01'),
login_info as(
select usr_id as user_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log group by usr_id,date_format(login_time,'%Y-%m-01')),
liucun_info as(
select a.user_id,min_log_month,login_month
from new_user as a left join
login_info as b on a.user_id=b.user_id and date_add(a.min_log_month,interval 1 month)=b.login_month
)
select min_log_month as current_month,
round(
count(distinct case when login_month is not null then user_id else nullend)*100/count(distinct user_id),2) as t_plus_1_month_retention_rate
from liucun_info
group by min_log_month
order bycurrent_month

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
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

with login_info as(
select usr_id,date(login_time) as login_date
from user_login_log
where date(login_time)>=date_sub(current_date(),interval 30 day)
group by usr_id,date(login_time)
)
select usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) AS consecutive_days
from
(
select 
usr_id,login_date,date_sub(login_date,interval rnk day) as base_date
from
(select 
 usr_id,
 login_date,
 row_number() over(partition by usr_id order by login_date) as rnk
 from login_info) as a) as b
 group by usr_id,base_date
having count(*)>=3
order by usr_id,start_date

with login_info as(
select usr_id,date(login_time) as login_date
from user_login_log
group by usr_id,date(login_time)
)
select usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) AS consecutive_days
from
(
select 
usr_id,login_date,date_sub(login_date,interval rnk day) as base_date
from
(select 
 usr_id,
 login_date,
 row_number() over(partition by usr_id order by login_date) as rnk
 from login_info) as a) as b
 group by usr_id,base_date
having count(*)>=3
order by usr_id,start_date

with login_info as(
select usr_id,date(login_time) as login_date
from user_login_log
group by usr_id,date(login_time)
)
select usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) AS consecutive_days
from
(
select 
usr_id,login_date,date_sub(login_date,interval rnk day) as base_date
from
(select 
 usr_id,
 login_date,
 row_number() over(partition by usr_id order by login_date) as rnk
 from login_info) as a) as b
 group by usr_id,base_date
having count(*)>=3

select *
from scores
where exam_date='2024-06-30' and subject in('政治','历史','地理') and score>=90
order by score desc ,student_id,subject

select *
from scores
where exam_date='2024-06-30' and subject='英语' and score between 100 and 110
order by score desc

select *
from scores
where exam_date='2024-06-30' and subject='英语' and score between 110 and 110
order by score desc

select student_id,sum(score) as total_score
from scores
where exam_date='2024-06-30' and subject in('语文','数学','英语')
group by student_id
having sum(score)>330

select student_id,sum(score) as total_score
from scores
where exam_date='2024-06-30' and subject in('语文','数学','英语')
group by student_id
having sum(score)>=300

select case when enter_date>='2010-01-01' then '青年教师'
when enter_date>='2000-01-01' then '中年教师'
when enter_date<'2000-01-01' then '资深教师'
end as teach_type,
count(distinct teacher_id) as teacher_count
from teachers
group by case when enter_date>='2010-01-01' then '青年教师'
when enter_date>='2000-01-01' then '中年教师'
when enter_date<'2000-01-01' then '资深教师'
end

select class_code,birth_date
from
(select class_code,birth_date,row_number()over(partition by class_code order by birth_date) as rnk
from students
) as t 
where rnk=1
order by class_code

select name,subject,class_code,enter_date
from teachers
where enter_date<'2010-01-01' and qualification is null
order by enter_date

select name,subject,class_code
from teachers
where length(class_code)-length(replace(class_code,'C',''))=5
order by name
;

SELECT name, subject, class_code, graduate_date
FROM teachers
WHERE substr(graduate_date,1,4) BETWEEN 1994 AND 1997 AND gender = 'f'
ORDER BY graduate_date ASC;

select student_id
from scores 
group by student_id
having sum(if(score is null,1,0))>5
order by student_id

select student_id
from
(SELECT student_id,min(if(score is not null,1,0)) as tag
FROM scores
GROUP BY student_id) as t 
where tag=1
ORDER  BY  student_id

select distinct student_id from scores
where coalesce(score,'999')<>'999'
order by student_id

select name,subject,class_code
from teachers
where length(class_code)-length(replace(class_code,'C',''))=5
order by name;

select sum(order_cnt) AS order_count
from
(select coupon_id,count(distinct order_id) as order_cnt
from
order_promotions
group by coupon_id) as a join coupons as b on a.coupon_id=b.coupon_id
where coupon_type='全场通用'