with newp as (select usr_id,min(date(login_time)) as dd
from user_login_log
where year(login_time)=2024 and month(login_time)=9
group by usr_id)
select dd,count(distinct usr_id)
from newp
group by dd
order by 1
with newp as (select usr_id,min(date(login_time)) as dd
from user_login_log
where year(login_time)=2024 and month(login_time)=9
group by usr_id
order by min(date(login_time)))
select dd,count(distinct usr_id)
from newp
group by dd
select year(list_date) as Y,count(distinct symbol) as cnt
from stock_info
where ts_code like '%BJ' and list_date<='2024-11-07'
group by year(list_date)
select year(list_date) as Y,
sum(case when industry like '%地产' then 1 else 0 end) as 地产,
sum(case when industry like '%软件服务%' then 1 else 0 end) as 软件服务
from stock_info
where year(list_date) between 2000 and 2024
group by year(list_date)
with basic as (select year(dt) as Y,cast(avg(tmp_h) as decimal(4,2)) as t
from weather_rcd_china
where city='shenzhen' and year(dt) between 2011 and 2022
group by year(dt)),
twobasic as (select Y,t,lag(t,1) over(order by Y) as prey
from basic)
select Y as year ,t as avg_tmp_h,case when abs(t-prey)>1 then 'yes' else 'no' endas significant_change
from twobasic
with basic as (select year(dt) as Y,cast(avg(tmp_h) as decimal(4,2)) as t
from weather_rcd_china
where city='shenzhen'
group by year(dt)),
twobasic as (select Y,t,lag(t,1) over(order by Y) as prey
from basic)
select Y as year ,t as avg_tmp_h,case when abs(t-prey)>1 then 'yes' else 'no' endas significant_change
from twobasic
select year(dt) as Y,
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) as snow_days
from weather_rcd_china
where
month(dt) in (12,1,2)
group by
city
order by
2
desc
select city,sum(case when wnd like "%雪%" then 1 else 0 end) as snow_days
from weather_rcd_china
where
month(dt) in (12,1,2)
group by
city
order by
2
desc
select city,sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
CONCAT(
ROUND(
(SUM(CASE WHEN con LIKE '%多云%' THEN 1 ELSE 0 END) / COUNT(1)) * 100,
2
),
'%'
) as p
from weather_rcd_china
where year(dt) = 2021
group by city
order by p desc
select city,sum(case when con like '%多云%' then 1 else 0 end) as cloudy_days,
round(sum(case when con like '%多云%' then 1 else 0 end)/count(1),2) as p
from weather_rcd_china
where year(dt) = 2021
group by city
order by p desc
select sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
from didi_order_rcd
where grab_time != "1970-01-01 00:00:00"
select sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
from didi_order_rcd
where cancel_time != "1970-01-01 00:00:00"
select t2.live_id,t2.live_nm,count(*) as enter_cnt
from ks_live_t1 t1
JOIN
ks_live_t2 t2
ON
t1.live_id = t2.live_id
WHERE
DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY
t1.live_id, t2.live_nm
ORDER BY
enter_cnt DESC
LIMIT 5;
select date_format(enter_time,'%h') as hour_entered,count(distinct usr_id) as enter_count
from ks_live_t1
group by hour_entered
order by hour_entered asc