select substring(name,1,1) as surname,count(substring(name,1,1)) as cnt
from students
group by substring(name,1,1)
order by cnt desc
limit 5

select substring(name,1,1) as surname,count(substring(name,1,1)) as cnt
from students
group by substring(name,1,1)
order by cnt desc

select t1.prd_id,prd_nm,sum(if_snd) exposure_count from tb_pg_act_rcd t1
left join tb_prd_map t2 on t1.prd_id=t2.prd_id
group by t1.prd_id,prd_nm
order by exposure_count desc
limit 1

select ta.prd_id,prd_nm,exposure_count from(
select prd_id,sum(if_snd) exposure_count from tb_pg_act_rcd t1
group by prd_id
order by exposure_count desc
limit 1
)ta
left join tb_prd_map t2 on ta.prd_id=t2.prd_id

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

select count(case when cnt between 1 and 5 then usr_id end) as days_1_to_5,
count(case when cnt between 6 and 10 then usr_id end) as days_6_to_10,
count(case when cnt between 11 and 20 then usr_id end) as days_11_to_20,
count(case when cnt>20 then usr_id end) as days_over_20
from(
select usr_id,count(distinct date(login_time)) as cnt from user_login_log
where date(login_time)>=date(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select count(case when cnt between 1 and 5 then usr_id end) as days_1_to_5,
count(case when cnt between 6 and 10 then usr_id end) as days_6_to_10,
count(case when cnt between 10 and 20 then usr_id end) as days_11_to_20,
count(case when cnt>20 then usr_id end) as days_over_20
from(
select usr_id,count(distinct date(login_time)) as cnt from user_login_log
where date(login_time)>=date(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select count(case when cnt between 1 and 5 then usr_id end) as days_1_to_5,
count(case when cnt between 6 and 10 then usr_id end) as days_6_to_10,
count(case when cnt between 10 and 20 then usr_id end) as days_11_to_20,
count(case when cnt>20 then usr_id end) as days_over_20
from(
select usr_id,count(distinct day(login_time)) as cnt from user_login_log
where day(login_time)>=day(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select count(case when cnt between 1 and 5 then usr_id end) as days_1_to_5,
count(case when cnt between 6 and 11 then usr_id end) as days_6_to_10,
count(case when cnt between 11 and 21 then usr_id end) as days_11_to_20,
count(case when cnt>20 then usr_id end) as days_over_20
from(
select usr_id,count(distinct day(login_time)) as cnt from user_login_log
where day(login_time)>=day(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select count(case when cnt between 1 and 5 then usr_id end) as days_1_to_5,
count(case when cnt between 6 and 11 then usr_id end) as days_6_to_10,
count(case when cnt between 11 and 21 then usr_id end) as days_11_to_20,
count(case when cnt>20 then usr_id end) as days_over_20
from(
select usr_id,count(1) as cnt from user_login_log
where day(login_time)>=day(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select sum(case when cnt between 1 and 5 then cnt end) as days_1_to_5,
sum(case when cnt between 6 and 11 then cnt end) as days_6_to_10,
sum(case when cnt between 11 and 21 then cnt end) as days_11_to_20,
sum(case when cnt>20 then cnt end) as days_over_20
from(
select usr_id,count(1) as cnt from user_login_log
where day(login_time)>=day(date_sub(current_date,interval 180 day))
group by usr_id
 )ta

select count(distinct case when time(login_time)between "07:30:00" and "09:30:00" 
 or time(login_time) between "18:30:00" and "20:30:00" then usr_id else null end) as "通勤",
 count(distinct case when time(login_time) between "11:30:00" and "14:00:00" then usr_id else null end)as "午休",
 count(distinct case WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id end)as "临睡"
from user_login_log
 WHERE
    login_time>=date_format(date_sub(current_date, interval 1 month),'%Y-%m-01')
and login_time<=date_format(current_date,"%Y-%m-01")

select count(distinct case when time(login_time)between "07:30:00" and "09:30:00" 
 or time(login_time) between "18:30:00" and "20:30:00" then usr_id else null end) as "通勤",
 count(distinct case when time(login_time) between "11:30:00" and "14:00:00" then usr_id else null end)as "午休",
 count(distinct case WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id end)as "临睡"
from user_login_log
 WHERE
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');

select count(distinct case when time(login_time)between "07:30:00" and "09:30:00" 
 or time(login_time) between "18:30:00" and "20:30:00" then usr_id else null end) as "通勤",
 count(distinct case when time(login_time) between "11:30:00" and "14:00:00" then usr_id else null end)as "午休",
 count(distinct case WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id end)as "临睡"
from user_login_log

select count(distinct usr_id) as active_users from user_login_log
where login_time >= date_format(date_sub(current_date,interval 1 month),'%Y-%m-01 00:00:00')
and login_time<=DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00')

select count(distinct usr_id) as active_users from user_login_log
where date_format(login_time,"%Y-%m")="2025-02"

select count(distinct usr_id) as active_users from user_login_log
where date_format(login_time,"%Y-%m")="2024-02"

select ts_code,open_price,close_price from daily_stock_prices
where trade_date='2023-10-09' and pct_change >1

select ts_code,open_price,close_price from daily_stock_prices
where trade_date='2023-10-09' and pct_change >0.01

select ts_code,open_price,close_price from daily_stock_prices
where trade_date='2023-10-09' and pct_change >=0.1