select * 
from cmb_usr_trx_rcd 
where
date(trx_time)between'2024-09-01'and'2024-09-30'
and hour(trx_time)between 1 and 5
and usr_id='5201314520'

select user_id
from apple_pchs_rcd
group by user_id
having sum(case when product_type != 'iphone' then 1 else 0 end) =0
order by 1

select * 
from subject_score
where
(chinese>=110 and math >= 110)
or
(chinese>=110 and english >= 110)
or
(english >= 110 and math >= 110)
order by student_id

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')
),
new_users AS (
    SELECT
        usr_id,
        MIN(login_month) AS first_login_month
    FROM
        monthly_unique_logins
    GROUP BY
        usr_id
),
next_month_logins AS (
    SELECT
        nu.usr_id,
        nu.first_login_month AS current_month,
        mul.login_month AS next_month
    FROM
        new_users nu
    LEFT JOIN
        monthly_unique_logins mul
    ON
        nu.usr_id = mul.usr_id AND
        mul.login_month = DATE_ADD(nu.first_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 < '2025-01-01'
GROUP BY
    current_month
ORDER BY
    current_month;

select * 
from singer_info 
where birth_date ='1989-12-13'

select * 
from singer_info 
where type2='港台'
order by 
    1

select * 
from singer_info 
where type2='港台' AND type3='个人'

select * 
from singer_info
where type2='港台' and type3='乐队'
order by singer_id

select * 
from singer_info
where type2='港台'
order by singer_id

SELECT *
FROM numbers_for_fun
WHERE 
  CASE 
    WHEN ABS(a) >= ABS(b) AND ABS(a) >= ABS(c) THEN POW(ABS(a), 2) = POW(ABS(b), 2) + POW(ABS(c), 2)
    WHEN ABS(b) >= ABS(a) AND ABS(b) >= ABS(c) THEN POW(ABS(b), 2) = POW(ABS(a), 2) + POW(ABS(c), 2)
    ELSE POW(ABS(c), 2) = POW(ABS(a), 2) + POW(ABS(b), 2)
  END
  and a*b*c<>0
ORDER BY id ASC;

select * 
from numbers_for_fun 
where
a*b*c <> 0
and(
abs(a)+abs(b)>abs(c)
and abs(a)+abs(c)>abs(b)
 and abs(b)+abs(c)>abs(a)
)
 order by id;

select * 
from hand_permutations 
where card1 between 'J' and'K' and 
card2 between 'J' and 'K'
order by id
;

select * 
from subject_score
where chinese >100 or math >100 or english >100 
group by student_id
order by chinese asc

WITH VideoInteractions AS (
    SELECT 
        v.author_id,
        SUM(
            w.if_like +
            CASE WHEN w.comment_id IS NOT NULL THEN 1 ELSE 0 END +
            w.if_retweet +
            w.if_fav
        ) AS total_interactions,
        COUNT(DISTINCT v.video_id) AS video_count
    FROM ks_video_inf v
    JOIN ks_video_wat_log w ON v.video_id = w.video_id
    GROUP BY v.author_id
)
SELECT 
    author_id,
    round(total_interactions * 1.0 / video_count,2) AS avg_interaction_index
FROM VideoInteractions
ORDER BY avg_interaction_index DESC;

SELECT 
    v.video_id,
    v.title,
    COALESCE(AVG(TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) / v.duration), 0) AS avg_completion_rate
FROM ks_video_inf v
inner JOIN ks_video_wat_log w ON v.video_id = w.video_id
GROUP BY v.video_id, v.title
ORDER BY avg_completion_rate DESC;

select video_id,author_id,title
from ks_video_inf
wherescreen_type = 'p' and date(release_time) >= date_sub(curdate(),interval 7 day)
order by release_time desc

with B1 AS (
select student_id,
		chinese+math+english as cnt
from subject_score)
select t1.student_id
from B1 t1
where cnt >= 300

with B1 as (
select t1.cust_uid,t1.if_snd,t1.prd_id,t2.prd_nm,t2.price
from tb_pg_act_rcd t1
left join
tb_prd_map t2
on t1.prd_id=t2.prd_id
)
select prd_id,prd_nm,sum(if_snd)as cnt
from B1
group by prd_id,prd_nm
order by sum(if_snd) desc
limit 1;

with B1 as (select usr_id,
date(login_time) as day
from user_login_log
where login_time >= date_sub(curdate(),interval 180 day)
group by usr_id,date(login_time)
 ),
B2 as(select t1.usr_id,
 		count(distinct t1.day) as cnt
from B1 t1
group by t1.usr_id
 ),
B3 as (select t2.usr_id,
 		sum(case when t2.cnt BETWEEN 1 AND 5 then 1 else 0 end) as days_1_to_5,
sum(case when t2.cnt between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when t2.cnt between 11 and 20 then 1 else 0 end) as days_11_to_20,
SUM(CASE WHEN t2.cnt > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    B2 t2
group by 1)
select 
		sum(t3.days_1_to_5) as days_1_to_5_a,
sum(t3.days_6_to_10) as days_6_to_10_a,
sum(t3.days_11_to_20) as days_11_to_20_a,
sum(t3.days_over_20) as days_over_20_a
from B3 t3
 ;

with B1 as (select usr_id,
date(login_time) as day
from user_login_log
group by usr_id,date(login_time)
 ),
B2 as(select t1.usr_id,
 		count(distinct t1.day) as cnt
from B1 t1
group by t1.usr_id
 ),
B3 as (select t2.usr_id,
 		sum(case when t2.cnt <= 5 then 1 else 0 end) as days_1_to_5,
sum(case when t2.cnt between 6 and 10 then 1 else 0 end) as days_6_to_10,
sum(case when t2.cnt between 11 and 20 then 1 else 0 end) as days_11_to_20,
SUM(CASE WHEN t2.cnt > 20 THEN 1 ELSE 0 END) AS days_over_20
FROM 
    B2 t2
group by 1)
select 
		sum(t3.days_1_to_5) as days_1_to_5_a,
sum(t3.days_6_to_10) as days_6_to_10_a,
sum(t3.days_11_to_20) as days_11_to_20_a,
sum(t3.days_over_20) as days_over_20_a
from B3 t3
 ;