select*from numbers_for_fun
where c=1 and a=0 and b!=0
order by id

SELECT 
exam_date,
MAX(CASE WHEN subject = '语文' THEN score END) as chinese_score,
MAX(CASE WHEN subject = '数学' THEN score END) as math_score,
MAX(CASE WHEN subject = '英语' THEN score END) as english_score
FROM scores
WHERE student_id = 460093
GROUP BY exam_date
ORDER BY exam_date;

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 
	end) as commute,
	count(distinct
 case 
	when time(login_time) between '11:30:00'and '14:00:00' then usr_id
end) as lunch_break,
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 bedtime
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 
	end) as commute,
	count(distinct
 case 
	when time(login_time) between '11:30:00'and '14:00:00' then usr_id
end) as lunch_break,
count(distinct
case 
	when time(login_time) between '22:30:00' and '01:00:00' then usr_id
end) as bedtime
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
	left(name,1) as surname,count(*) as cnt
from 
	students
group by surname 
order by cnt desc
limit 5

SELECT 
    SUM(CASE WHEN login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS days_1_to_5,
    SUM(CASE WHEN login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS days_6_to_10,
    SUM(CASE WHEN login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS days_11_to_20,
    SUM(CASE WHEN login_days > 20 THEN 1 ELSE 0 END) AS days_over_20
from(
select
usr_id,count(distinct login_date) as login_days
from(
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
login_time >= date_sub(current_date(),interval 180 day)
) as t1
group by usr_id
) t2

select a.live_id,b.live_nm, count(a.usr_id) as enter_cnt 
from ks_live_t1 a join ks_live_t2 b on a.live_id=b.live_id
where DATE_FORMAT(a.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by a.live_id,b.live_nm
order by enter_cnt desc
limit 5;

select a.live_id,b.live_nm, count(usr_id) as enter_cnt 
from ks_live_t1 a join ks_live_t2 b on a.live_id=b.live_id
where DATE_FORMAT(a.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by a.live_id,b.live_nm
order by enter_cnt desc
limit 5;

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

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

select * from singer_info 
where birth_date < '2000-01-01' and gender = 'm'

select * from singer_info 
where birth_date < '2000-01-01'

select 
	student_id
from
	subject_score
where
	(chinese+math+english) >= 300

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

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

select ts_code,	open_price,	close_price
from daily_stock_prices
where pct_change > 1

select * 
from hand_permutations 
where 
(card1 like'A%' and card2 like 'A%')or
 (card1 like 'A%' and card2 like 'K%')or
 (card1 like 'K%' and card2 like'K%')or
 (card1 like 'K%' and card2 like'A%')