select
round(sum(case when '2020-11-20' between begin_date and end_date then pay_amount/(datediff(end_date,begin_date)) else 0 end),2) as total_daily_income
from bilibili_m1
select
date_format(enter_time,'%h') as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered desc
select
date_format(enter_time,'%h') as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered
select
date_format(enter_time,'%h') as hour_entered,
count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,'%h')
order by hour_entered
select
*
from
(select
date_format(order_time,'%Y-%m-%d') as order_date,
goods_id,
sum(order_gmv) as total_gmv,
row_number()over(partition by date_format(order_time,'%Y-%m-%d') order by sum(order_gmv)) as ranking
from order_info
where date_format(order_time,'%Y-%m') = '2024-10'
group by date_format(order_time,'%Y-%m-%d'),goods_id) a
where ranking <=3
select
count(distinct usr_id)
from user_login_log
where date_format(login_time,'%Y-%m-%d') <='2024-10-01'
and usr_id not in(
select
distinct
usr_id
from user_login_log
where date_format(login_time,'%Y-%m-%d') >'2024-10-01'
)
select
distinct
usr_id
from user_login_log
where date_format(login_time,'%Y-%m-%d') <='2024-10-01'
and usr_id not in(
select
distinct
usr_id
from user_login_log
where date_format(login_time,'%Y-%m-%d') >'2024-10-01'
)
select
snd_usr_id as snd_usr_id
from tx_red_pkt_rcd
group by snd_usr_id
having count(case when pkt_amt in (200.00,520.00) then 1 else Null end) >=5
order by snd_usr_id
select
rcv_usr_id as snd_usr_id
from tx_red_pkt_rcd
group by rcv_usr_id
having count(case when pkt_amt in (200.00,520.00) then 1 else Null end) >=5
order by snd_usr_id
select
score_range,
count(*) as num_students
from
(
select
case when score >=90 and score<110 then '[90, 110)'
when score >=60 and score<90 then '[60, 90)'
when score >=110 and score<=120 then '[110, 120]'
when score >=0 and score<60 then '[0, 60)'
end as score_range
from scores
where exam_date = '2024-06-30' and
subject = '数学') a
group by score_range
order by score_range desc
select
score_range,
count(*) as num_students
from
(
select
case when score >=90 and score<110 then '[90, 110)'
when score >=60 and score<90 then '[60, 90)'
when score >=110 and score<=120 then '[110, 120)'
when score >=0 and score<60 then '[0, 60)'
end as score_range
from scores
where exam_date = '2024-06-30' and
subject = '数学') a
group by score_range
order by num_students desc
select
score_range,
count(*) as num_students
from
(
select
case when score >=90 and score<110 then '[90, 110)'
when score >=60 and score<90 then '[60, 90)'
when score >=110 and score<=120 then '[110, 120)'
when score >=0 and score<60 then '[0, 60)'
end as score_range
from scores
where exam_date = '2024-06-30' and
subject = '数学') a
group by score_range
order by score_range desc
select
score_range,
count(*) as num_students
from
(
select
case when score >=90 and score<110 then '[90, 110)'
when score >=60 and score<90 then '[60, 90)'
when score >=110 and score<=120 then '[110, 120)'
when score >=0 and score<60 then '[0, 60)'
end as score_range
from scores
where exam_date = '2024-06-30' and
subject = '数学') a
group by score_range
select
score_range,
count(*) as num_students
from
(
select
case when score >=90 and score<100 then '[90, 110)'
when score >=60 and score<90 then '[60, 90)'
when score >=110 and score<=120 then '[110, 120)'
when score >=0 and score<60 then '[0, 60)'
end as score_range
from scores
where exam_date = '2024-06-30' and
subject = '数学') a
group by score_range