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

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

select s.name ,s.class_code, s.grade_code,
t.name as head_teacher_name
from students as s
join teachers as t on s.class_code = t.head_teacher

select 
p.prd_id,
m.prd_nm,
count(if_snd) exposure_count
from tb_pg_act_rcd as p
join tb_prd_map as m on p.prd_id=m.prd_id
group by p.prd_id,m.prd_nm
order by exposure_count desc
limit 1;

select cust_uid,count(distinct car_cls) unique_car_classes from didi_sht_rcd 
group by cust_uid

select dsr.* from didi_sht_rcd as dsr
join loc_nm_ctg as lnc on dsr.end_loc = lnc.loc_nm
where lnc.loc_ctg = '餐饮'

select car_cls,count(*) as trip_count from didi_sht_rcd 
group by car_cls

select * from didi_sht_rcd 
where start_loc = "海底捞西丽店"
or end_loc = "海底捞西丽店";

with t as 
(select 
 usr_id,
 count(distinct date(login_time)) as f
from user_login_log 
where date(login_time) >= date_add(curdate(), interval -180 DAY)
group by usr_id
order by f desc
 )
select 
count(case when t.f between 1 and 5 then 1 end) as days_1_to_5,
count(case when t.f between 6 and 10 then 1 end) as days_6_to_10,
count(case when t.f between 11 and 20 then 1 end) as days_11_to_20,
count(case when t.f > 20 then 1 end) asdays_over_20
from t

with t as 
(select 
 usr_id,
 count(distinct date(login_time)) as f
from user_login_log 
where date(login_time) > date_add(curdate(), interval -180 DAY)
group by usr_id
order by f desc
 )
select 
count(case when t.f between 1 and 5 then 1 end) as days_1_to_5,
count(case when t.f between 6 and 10 then 1 end) as days_6_to_10,
count(case when t.f between 11 and 20 then 1 end) as days_11_to_20,
count(case when t.f > 20 then 1 end) asdays_over_20
from t

with t as 
(select 
 usr_id,
count(case 
when date(login_time) >= date_add(curdate(), interval -180 DAY)then 1 end) as f
from user_login_log 
group by usr_id
order by f desc)
select 
count(case when t.f between 1 and 5 then 1 end) as days_1_to_5,
count(case when t.f between 6 and 10 then 1 end) as days_6_to_10,
count(case when t.f between 11 and 20 then 1 end) as days_11_to_20,
count(case when t.f > 20 then 1 end) asdays_over_20
from t

with t as 
(select usr_id,count(case 
when date(login_time) between date_add(current_date, interval -180 DAY) and current_date
then 1
end) as f
from user_login_log 
group by usr_id
order by f desc)
select 
count(case when t.f between 1 and 5 then 1 end) as days_1_to_5,
count(case when t.f between 6 and 10 then 1 end) as days_6_to_10,
count(case when t.f between 11 and 20 then 1 end) as days_11_to_20,
count(case when t.f > 20 then 1 end) asdays_over_20
from t

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) = '2023-10-9'
and pct_change > 1
and close_price > open_price
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-8'
and pct_change > 1
and close_price > open_price
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-8'
and pct_change >0.01
and close_price > open_price
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-9'
and pct_change >0.01
and close_price > open_price
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-8'
and open_price/close_price >0.01
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-8'
and pct_change >0.01
;

select ts_code,open_price,close_price from daily_stock_prices 
where date(trade_date) > '2023-10-8'
and price_change >0.01
;

select year(list_date) as Y,count(*) as cnt from stock_info 
where ts_code rlike '.*SZ' and industry = '银行'
group by year(list_date)
;