with tt as (
select distinct usr_id, date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(current_date, interval 180 day))
, bb as (
select usr_id, count(login_date) as days
from tt
group by usr_id)
select
count(case when days between 1 and 5 then days end) as days_1_to_5,
count(case when days between 6 and 10 then days end) as days_6_to_10,
count(case when days between 11 and 20 then days end) as days_11_to_20,
count(case when days > 20 then days end) as days_over_20
from bb
select
count(distinct
case when (date_format(login_time, '%H:%i:%s') between '07:30:00' and '09:30:00') or (date_format(login_time, '%H:%i:%s') between '18:30:00' and '20:30:00') then usr_id end) as commute,
count(distinct
case when date_format(login_time, '%H:%i:%s') between '11:30:00' and '14:00:00' then usr_id end) as lunch_break,
count(distinct
case when (date_format(login_time, '%H:%i:%s') between '22:30:00' and '23:59:59') or (date_format(login_time, '%H:%i:%s') 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(current_date, interval 1 month), '%Y-%m-01 00:00:00') and login_time <= date_format(current_date, '%Y-%m-01 00:00:00')
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select distinct snd_usr_id
from tt
where number >= 5
order by snd_usr_id
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
order by snd_usr_id
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt IN (200, 520)
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
with tt as (
select snd_usr_id, pkt_amt
, row_number()over(partition by snd_usr_id order by pkt_amt) as number
from tx_red_pkt_rcd
where pkt_amt = 200 or pkt_amt = 520
order by snd_usr_id,number)
select snd_usr_id
from tt
where number >= 5
with tt as (
select user_id, dayname(start_time) as day_of_week
from listen_rcd
order by user_id, dayname(start_time))
select *, count(*) as listens_per_day
from tt
group by user_id, day_of_week
order by user_id, day_of_week
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select
lpad(enterhour, 2, '0') as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
with tt as (select usr_id, live_id, hour(enter_time) as enterhour
from ks_live_t1)
select enterhour as hour_entered,
count(enterhour) as enter_count
from tt
group by enterhour
order by enterhour
with user_next_day as (
select *,
lead(lgdate)over(partition by usr_id order by lgdate) as nextday
from(
select distinct usr_id, date(login_time) as lgdate
from user_login_log
where datediff (current_date, date(login_time)) <= 30) as tt)
select lgdate as login_date,
concat(round(
count(distinct case when datediff(nextday, lgdate)=1 then usr_id end)/
count(distinct usr_id) * 100 , 2), '%') as T1_retention_rate
from user_next_day
group by lgdate
order by lgdate
with tt as (
select score,
case
when score >= 110 then '[110, 120]'
when score >= 90 then '[90, 110)'
when score >= 60 then'[60, 90)'
else '[0, 60)'
end as score_range
from scores
where subject = '数学' and exam_date = '2024-06-30')
select score_range,
count(score_range) as num_students
from tt
group by score_range
ORDER BY
score_range DESC;