select 
cty,
status,
count(distinct order_id)
from hll_t1 
group by cty,status

select
cty,
driver_id,
cnt
from(
select 
driver_id,
cty,
count(distinct order_id) as cnt,
row_number()over(partition by cty order by count(distinct order_id) desc) rnk
from hll_t1 
group by driver_id,cty)t1
where( rnk=1 and cty='北京')or (cty='柳州' and rnk=1)

select
role,
count(distinct usr_id),
count(distinct case when banned=1 then usr_id end),
concat(round(count(distinct case when banned=1 then usr_id end)/count(distinct usr_id)*100,2),'%')
from hll_t2
group by role

select 
a.order_dt,
count(distinct a.order_id) cnt1,
count(distinct case when a.status='completed' and b.banned=0 and c.banned=0 then a.order_id end) as cnt2,
round(count(distinct case when a.status='completed' and b.banned=0 and c.banned=0 then a.order_id end)/count(distinct a.order_id),4)
from hll_t1 a
left join hll_t2 b on a.usr_id=b.usr_id and b.role='usr'
left join hll_t2 c on a.driver_id=c.usr_idand c.role='driver'
group by a.order_dt

select
cty,
total_order_count,
valid_order_count,
concat(round(cast(valid_order_count as float)/total_order_count*100,2),'%')
from(
select 
a.cty,
count(*) as total_order_count,
count(case when a.status='completed' and b.banned=0 and d.banned=0 then 1 else null end) valid_order_count
from hll_t1 a
left join hll_t2 b on a.usr_id=b.usr_id
left join hll_t2 d on a.driver_id=d.usr_id
group by a.cty)t1
order by 4 desc

select
cty,
total_order_count,
valid_order_count,
concat(round(valid_order_count/total_order_count*100,2),'%')
from(
select 
a.cty,
count(*) as total_order_count,
count(case when a.status='completed' and b.banned=0 and d.banned=0 then 1 else null end) valid_order_count
from hll_t1 a
left join hll_t2 b on a.usr_id=b.usr_id
left join hll_t2 d on a.driver_id=d.usr_id
group by a.cty)t1
order by 4 desc

select 
cty,
sum(case when status='completed' then 1 else 0 end),
sum(case when status='cancel_by_usr' then 1 else 0 end),
sum(case when status='cancel_by_driver' then 1 else 0 end)
from hll_t1 
group by cty
order by 1

select
live_id,
live_nm,
live_type,
cnt
from
(select 
a.live_id,
b.live_nm,
b.live_type,
count(*) cnt,
 row_number()over(partition by b.live_type order by count(*)desc) as rnk
from ks_live_t1 a
left 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,b.live_type
)t1
where rnk=1
order by cnt desc

select 
a.live_id,
b.live_nm,
b.live_type,
count(*)
from ks_live_t1 a
left 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,b.live_type

select 
a.live_id,
b.live_nm,
count(1)
from ks_live_t1 a
left 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 3 desc
limit 5

select 
count(1),
a.live_id,
b.live_nm
from ks_live_t1 a
left 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 1 desc
limit 5

select
case when hour_enter>=10 then hour_enter else concat('0',hour_enter) end,
count(*)
from(
select 
usr_id,
hour(enter_time) as hour_enter
from ks_live_t1 
) t1
group by hour_enter
order by hour_enter

select
case when hour_enter>10 then hour_enter else concat('0',hour_enter) end,
count(*)
from(
select 
usr_id,
hour(enter_time) as hour_enter
from ks_live_t1 
) t1
group by hour_enter
order by hour_enter

select
hour_enter,
count(*)
from(
select 
usr_id,
hour(enter_time) as hour_enter
from ks_live_t1 
) t1
group by hour_enter
order by 1

select
left(name,1) as first_name,
count(*) as cnt
from
(select
name
from students
where length(name)=6)t1
group by left(name,1) 
order by count(*) desc
limit 3

with a as(
select
usr_id,
date(login_time) as login_date
from user_login_log
where login_time>=date_sub(curdate(),interval 30 day)
group by usr_id,date(login_time)
),
b as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as row_num 
from a
),
c as (
select
usr_id,
login_date,
login_date - interval row_num day as grp
from b
),
d as (
select
usr_id,
min(login_date) start_date,
max(login_date) end_date,
count(*) as consecutive_days
from c
group by usr_id,grp
having count(*)>2
)
select
usr_id,
start_date,
end_date,
consecutive_days
from 
d 
order by usr_id asc,start_date

select
count(case when cnt between 1 and 5 then usr_id end),
count(case when cnt between 6 and 10 then usr_id end),
count(case when cnt between 11 and 20 then usr_id end),
count(case when cnt >20 then usr_id end)
from
(select
usr_id,
count(distinct login_date) as cnt
from
(select
usr_id,
date(login_time) as login_date
from user_login_log
where login_time>=date_sub(curdate(),interval 180 day))t1
group by usr_id)t2

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),
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 end)
from user_login_log 
where login_time>=date_format(date_sub(curdate(),interval 1 month),'%Y-%m-01 00:00:00') and login_time<date_format(curdate(),'%Y-%m-01 00:00:00')

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),
count(distinct case when time(login_time) between '11:30:00' and '14:00:00' then usr_id end),
count(distinct case when time(login_time) between '22:30:00' and '01:00:00' then usr_id end)
from user_login_log 
where login_time>=date_format(date_sub(curdate(),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)
from user_login_log 
where login_time>=date_format(date_sub(curdate(),interval 1 month),'%Y-%m-01 00:00:00') and login_time<date_format(curdate(),'%Y-%m-01 00:00:00')