select
m_flg,
round(avg(num),2)as num_1
from (
select
m_flg,
bilibili_t100.usr_id,
date(v_tm) as date1,
count(v_id) as num
from bilibili_t100
left join bilibili_t20
on bilibili_t20.usr_id = bilibili_t100.usr_id
where date(v_tm) between '2021-02-01' and '2021-02-28'
group by 1,2,3
) aa
group by 1

select
m_flg,
round(avg(num),2)as num_1
from (
select
m_flg,
bilibili_t100.usr_id,
date(v_tm) as date1,
count(distinct v_id) as num
from bilibili_t100
left join bilibili_t20
on bilibili_t20.usr_id = bilibili_t100.usr_id
where date(v_tm) between '2021-02-01' and '2021-02-28'
group by 1,2,3
) aa
group by 1

select
m_flg,
round(avg(num_1),2) as num_2
from (
select
m_flg,
usr_id,
avg(num)as num_1
from (
select
m_flg,
bilibili_t100.usr_id,
date(v_tm) as date1,
count(distinct v_id) as num
from bilibili_t100
left join bilibili_t20
on bilibili_t20.usr_id = bilibili_t100.usr_id
where date(v_tm) between '2021-02-01' and '2021-02-28'
group by 1,2,3
) aa
group by 1,2
) bb
group by 1

select
m_flg,
avg(num_1) as num_2
from (
select
m_flg,
usr_id,
avg(num)as num_1
from (
select
m_flg,
bilibili_t100.usr_id,
date(v_tm) as date1,
count(distinct v_id) as num
from bilibili_t100
left join bilibili_t20
on bilibili_t20.usr_id = bilibili_t100.usr_id
where date(v_tm) between '2021-02-01' and '2021-02-28'
group by 1,2,3
) aa
group by 1,2
) bb
group by 1

select
distinct
left(trx_time,7)as month,
sum(trx_amt) over (partition by mch_typ,left(trx_time,7))
from cmb_usr_trx_rcd 
left join cmb_mch_typ
on cmb_usr_trx_rcd.mch_nm=cmb_mch_typ.mch_nm
where
mch_typ='休闲娱乐'
and trx_time like ('2023%')
and usr_id=5201314520

select 
students.student_id,
students.name,
scores.score,
row_number()over (partition by subject order by score desc)
from students
left join scores
on students.student_id=scores.student_id
where 
subject='物理'
and grade_code='S1'
limit 10

select 
students.student_id,
students.name,
scores.score,
rank() over (partition by subject order by score desc)as '排名'
from students
left join scores
on students.student_id=scores.student_id
where 
subject='物理'
and grade_code='S1'
limit 10

select 
students.student_id,
students.name,
scores.score,
rank() over (partition by subject order by score desc)
from students
left join scores
on students.student_id=scores.student_id
where 
subject='物理'
and grade_code='S1'
limit 10

select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(ks_live_t1.usr_id) as 进入人次
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where ks_live_t1.enter_time between'2021-09-12 23:00:00'and'2021-09-12 23:59:59' 
group by 1,2
order by count(ks_live_t1.usr_id) desc
limit 5

select 
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(ks_live_t1.usr_id)as'进入人次'
from ks_live_t1
left join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where 
ks_live_t1.enter_time between'2021-09-12 23:00:00' and '2021-09-12 23:59:59'
group by 1,2
order by count(ks_live_t1.usr_id) desc
limit 5

select 
usr_id,
min(trx_time)as first_time,
mch_nm
from cmb_usr_trx_rcd
where
usr_id=5201314520
and mch_nm='红玫瑰按摩保健休闲'

select
mch_nm,
sum(trx_amt)as sum_trx_amt
from cmb_usr_trx_rcd
where date(trx_time) between '2024-01-01'and'2024-12-31'
and usr_id=5201314520
group by 1

select
*
from tb_cst_bas_inf
where
age between 35 and 45
and gdr='M'

with unique_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 1,2
),
retention as (
select
first.usr_id,
first.login_date,
second.login_date as rentention_date
from unique_login as first
left join unique_login as second
on first.usr_id = second.usr_id
and datediff(second.login_date,first.login_date) between 1 and 14 
)
 select
 first_login_date,
 round(rent_1/num*100,2) as t_plus_1_rentention_rate,
 round(rent_3/num*100,2) as t_plus_3_rentention_rate,
 round(rent_7/num*100,2) as t_plus_7_rentention_rate,
 round(rent_14/num*100,2) as t_plus_14_rentention_rate
 from (
 select
 login_date as first_login_date,
 count(distinct usr_id) num,
 count(distinct if(datediff(rentention_date,login_date)=1,usr_id,null)) rent_1,
 count(distinct if(datediff(rentention_date,login_date)=3,usr_id,null)) rent_3,
 count(distinct if(datediff(rentention_date,login_date)=7,usr_id,null)) rent_7,
 count(distinct if(datediff(rentention_date,login_date)=14,usr_id,null)) rent_14
 from retention
 group by 1
 ) total

select
first_login_date,
round(lead_1_num/num*100,2) as t_plus_1_retention_rate,
round(lead_3_num/num*100,2) as t_plus_3_retention_rate,
round(lead_7_num/num*100,2) as t_plus_7_retention_rate,
round(lead_14_num/num*100,2) as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 30
group by 1,2
) user
) total
group by 1
) final
order by first_login_date

select
first_login_date,
round(lead_1_num/num*100,2) as t_plus_1_retention_rate,
round(lead_3_num/num*100,2) as t_plus_3_retention_rate,
round(lead_7_num/num*100,2) as t_plus_7_retention_rate,
round(lead_14_num/num*100,2) as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 90
group by 1,2
) user
) total
group by 1
) final
order by first_login_date

select
first_login_date,
round(lead_1_num/num*100,2) as t_plus_1_retention_rate,
round(lead_3_num/num*100,2) as t_plus_3_retention_rate,
round(lead_7_num/num*100,2) as t_plus_7_retention_rate,
round(lead_14_num/num*100,2) as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 90
group by 1,2
) user
) total
group by 1
) final

select
first_login_date,
round(lead_1_num/num*100,2) as t_plus_1_retention_rate,
round(lead_3_num/num*100,2) as t_plus_3_retention_rate,
round(lead_7_num/num*100,2) as t_plus_7_retention_rate,
round(lead_14_num/num*100,2) as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 30
group by 1,2
) user
) total
group by 1
) final

select
first_login_date,
lead_1_num/num*100 as t_plus_1_retention_rate,
lead_3_num/num*100 as t_plus_3_retention_rate,
lead_7_num/num*100 as t_plus_7_retention_rate,
lead_14_num/num*100 as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 30
group by 1,2
) user
) total
group by 1
) final

select
first_login_date,
lead_1_num/num as t_plus_1_retention_rate,
lead_3_num/num as t_plus_3_retention_rate,
lead_7_num/num as t_plus_7_retention_rate,
lead_14_num/num as t_plus_14_retention_rate
from (
select
first_login_date,
count(distinct usr_id) num,
count(distinct if(datediff(lead_1,first_login_date)=1,usr_id,null)) lead_1_num,
count(distinct if(datediff(lead_3,first_login_date)=3,usr_id,null)) lead_3_num,
count(distinct if(datediff(lead_7,first_login_date)=7,usr_id,null)) lead_7_num,
count(distinct if(datediff(lead_14,first_login_date)=14,usr_id,null)) lead_14_num
from (
select
first_login_date,
usr_id,
lead(first_login_date,1) over (partition by usr_id order by first_login_date) as lead_1,
lead(first_login_date,3) over (partition by usr_id order by first_login_date) as lead_3,
lead(first_login_date,7) over (partition by usr_id order by first_login_date) as lead_7,
lead(first_login_date,14) over (partition by usr_id order by first_login_date) as lead_14
from (
select date(login_time) as first_login_date,
usr_id
from user_login_log 
where datediff(current_date,date(login_time)) <= 30
group by 1,2
) user
) total
group by 1
) final