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

with days as (
select usr_id, count(distinct date(login_time)) login_days
from user_login_log
where login_time>= date_sub(current_date, interval 180 day)
group by usr_id)
select 
sum(case whenlogin_days between 1 and 5 then 1 else 0 end )as '1-5',
sum(case whenlogin_days between 6 and 10 then 1 else 0 end) as '5-10',
sum(case whenlogin_days between 11 and 20 then 1 else 0 end) as '10-20',
sum(case whenlogin_days >20 then 1 else 0 end) as'20天以上'
from days

with days as (
select distinct usr_id, login_time login_days
from user_login_log
where login_time>= date_sub(current_date, interval 180 day))
select 
sum(case whenlogin_days between 1 and 5 then 1 else 0 end )as '1-5',
sum(case whenlogin_days between 6 and 10 then 1 else 0 end ) '5-10',
sum(case whenlogin_days between 11 and 20 then 1 else 0 end ) '10-20',
sum(case whenlogin_days >20 then 1 else 0 end ) '20天以上'
from days

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 commute,
count(distinct case when time(login_time) between '11:30:00' and '14:00:00' then usr_id
 else null end) as lunch,
 count(distinct case when time(login_time) between '22:30:00' and '23:59:59'
 or time(login_time) between '00:00:00' and '01:00:00'
 then usr_id
 else null end) as sleep
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(current_date, '%Y-%m-01 00:00:00')

select 
sum(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 1
 else 0
 end) as commute,
sum(case when time(login_time) between '11:30:00' and '14:00:00' then 1
 else 0 end) as lunch,
 sum(case when time(login_time) between '22:30:00' and '23:59:59'
 or time(login_time) between '00:00:00' and '01:00:00'
 then 1
 else 0 end) as sleep
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(current_date, '%Y-%m-01 00:00:00')

select 
sum(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 1
 else 0
 end) as commute,
sum(case when time(login_time) between '11:30:00' and '14:00:00' then 1
 else 0 end) as lunch,
 sum(case when time(login_time) between '23:30:00' and '23:59:59'
 or time(login_time) between '00:00:00' and '01:00:00'
 then 1
 else 0 end) as sleep
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(current_date, '%Y-%m-01 00:00:00')

select 
count(distinct usr_id) 
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(current_date,'%Y-%m-01 00:00:00')

select count(distinct usr_id) 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(current_date,'%Y-%M-01 00:00:00')

select count(distinct usr_id) from user_login_log 
 where month(login_time)=month(current_date)-1

select count(distinct usr_id) from user_login_log
where month(current_date)=month(login_time )+1

select count(usr_id) from user_login_log
where month(current_date)=month(login_time )+1

select * from user_login_log

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

select year(list_date),count(distinct ts_code) 
from stock_info
where industry = '银行' 
and ts_code like '%SZ%'
group by year(list_date)
order by year(list_date)

select year(list_date),count(distinct name) from stock_info
group by year(list_date)
order by year(list_date)

select year(list_date),count(1) from stock_info
group by year(list_date)
order by year(list_date)

select year(dt),
cast(avg(case when city='beijing' then tmp_h else null end ) as decimal (4,2))as '北京',
cast(avg(case when city='shanghai' then tmp_h else null end ) as decimal (4,2)) as '上海',
cast(avg(case when city='shenzhen' then tmp_h else null end ) as decimal (4,2)) as '深圳',
cast(avg(case when city='guangzhou' then tmp_h else null end ) as decimal (4,2)) as '广州'
from weather_rcd_china 
where year(dt) between 2011 and 2022
group by year(dt)

select city,
sum(case when con like '%雪%' then 1 else 0 end) snowy_days
from weather_rcd_china
where month(dt) in (12,1,2)
group by city

select city,
count(1)
from weather_rcd_china
where month(dt) in (12,1,2)
and con like '%雪%'
group by city

select city, 
sum(case when con like '%多云%'then 1
else 0 end) as cloudy_days,
concat(cast(sum(case when con like '%多云%'then 1
else 0 end)/count(1)*100 as decimal (10,2)),'%') p
from weather_rcd_china 
where year(dt)=2021
group by city