select a.live_id,live_nm
,count(*)
from ks_live_t1 a
left join ks_live_t2 b
on a.live_id = b.live_id
where substr(enter_time,1,13) = '2021-09-12 23'
group by a.live_id,live_nm
order by count(*) desc limit 5 ;

select a.live_id,live_nm
,count(*)
from ks_live_t1 a
left join ks_live_t2 b
on a.live_id = b.live_id
where enter_time<= '2021-09-12 23:00:00'
and leave_time>='2021-09-12 23:00:00'
group by a.live_id,live_nm
order by count(*) desc limit 5 ;

WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY s.grade_code ORDER BY sc.score DESC) AS rnk
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        s.grade_code = 'S1' 
        AND sc.subject = '物理'
)
SELECT 
    student_id, 
    name, 
    score, 
    rnk
FROM 
    ranked_scores
WHERE 
    rnk <= 10
ORDER BY 
    rnk,student_id

select
	st.student_id
,st.name
,sc.score
,row_number()over(order by sc.score desc) as rnk
from
	students as st
inner join 
scores	as sc
on st.student_id=sc.student_id
where
	st.grade_code='S1'
and sc.subject='物理'
ORDER BY 
rnk,st.student_id
limit
	10

select * from (
select 
a.student_id,
name,score
,rank()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
 ORDER BY 
    rn,student_id
;

select * from (
select 
a.student_id,
name,score
,row_number()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
 ORDER BY 
    rn,student_id
;

select * from (
select 
a.student_id,
name,score
,row_number()over( order by score desc) rn 
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
 )a1 where rn <= 10
;

select 
a.student_id,
name,score
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
and grade_code ='S1'
order by score desc limit 10
;

select 
a.student_id,
name,score
from students a
left join scores b
on a.student_id=b.student_id
where subject = '物理' 
order by score desc limit 10
;

select
substr(name,1,1),COUNT(*) 
from students 
group by substr(name,1,1 ) 
order by count(*) desc 
limit 5;

select
substr(name,1,1)
from students 
group by substr(name,1,1 ) 
order by count(*) desc 
limit 5;

select 
a.name,
a.class_code,grade_code,
b.name
from students a
left join teachers b
on a.class_code=b.head_teacher
order by student_id asc

select 
a.name,a.class_code,
b.name
from students a
left join teachers b
on a.class_code=b.head_teacher
order by student_id asc

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站'when loc_nm	 ='凯德广场'then '购物广场'when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站'when loc_nm	 ='凯德广场'then '购物广场'when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id, count( distinct substr(start_time,1,10))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站'when loc_nm	 ='凯德广场'then '	购物广场'when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站'when loc_nm	 ='凯德广场'then '	购物广场'when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( distinct substr(start_time,1,10)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcdgroup by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count( *) from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( *) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcd where substr(start_time,1,4) between '2020' and '2024' group by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
(replace(start_time,'-','')-replace(lead(start_time,1,0)over(partition by user_id order by start_time),'-','') ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =-1
group by user_idhaving count(minusc) >= 2) a2
on a3.user_id = a2.user_id
;

select a3.user_id user_id ,case whena2.user_id is null then 0 else 1 end as active_tag 
from (selectuser_id fromhello_bike_riding_rcd group by user_id) a3
left join 
(
select user_idfrom 
(
select user_id,
( start_time-lead(start_time,1,0)over(partition by user_id order by start_time) ) minusc from (
select substr(start_time,1,7) start_time ,user_id,count(distinct substr(start_time,1,7))from hello_bike_riding_rcda
left join ( select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) b 
on a.start_loc = b.loc_nm
left join(select loc_nm,case when loc_nm	= '将台西' then '地铁站' when loc_nm	='北京机床研究所' 
then '写字楼' 
else loc_typeend loc_type from gd_loc_map ) c
on a.end_loc = c.loc_nm
where
concat(b.loc_type,c.loc_type) in ('写字楼地铁站','地铁站写字楼')
 andsubstr(start_time,1,4) between '2020' and '2024'
group by substr(start_time,1,7),user_idhaving count( substr(start_time,1,7)) >=5
) a1) a2
where minusc =0
group by user_idhaving count(minusc) >= 3) a2
on a3.user_id = a2.user_id
;