with wubufa as (
select
t1.live_id,
t2.live_nm,
t2.live_type,
count(*) as enter_cnt
from ks_live_t1 t1
join ks_live_t2 t2 on t1.live_id=t2.live_id
where date_format(enter_time ,'%Y-%m-%d %H')='2021-09-12 23'
group by t1.live_id,
t2.live_nm,
t2.live_type),
chuankou as (
select
*,
row_number()over(partition by live_type order by enter_cnt desc) as rnk
from wubufa
)
select live_id,live_nm,live_type,enter_cnt
from chuankou
where rnk=1
select
lpad(hour(enter_time),2,"0") as hour_entered,
count(*) as enter_count
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
group by hour_entered
order by hour_entered asc
select
v.video_id,
v.title,
count(u.uid) as view_count,
sum(u.if_like) as like_count,
sum(u.if_retweet) as retweet_count,
sum(u.if_fav)as fav_count,
(SUM(u.if_like) + SUM(u.if_retweet) + SUM(u.if_fav)) AS total_interactions
from ks_video_inf v
join ks_video_wat_log u on
v.video_id=u.video_id
where
u.start_time>=date_sub(curdate(),INTERVAL 1 MONTH)
group by v.video_id,v.title
order by total_interactions desc
limit 3
select
v.video_id,
v.title,
count(u.uid) as view_count,
sum(u.if_like) as like_count,
sum(u.if_retweet) as retweet_count,
sum(u.if_fav)as fav_count,
(SUM(u.if_like) + SUM(u.if_retweet) + SUM(u.if_fav)) AS total_interactions
from ks_video_inf v
join ks_video_wat_log u on
v.video_id=u.video_id
where
u.start_time>=date_sub(curdate(),INTERVAL 1 MONTH)
group by v.video_id,v.title
order by total_interactions
limit 3
with b as (
select usr_id,live_id,enter_time as event_time,1 as flag
from ks_live_t1
union all
select usr_id,live_id,leave_time as event_time,-1 as flag
from ks_live_t1
),
b1 as (
select live_id,event_time,sum(flag)over(partition by live_id order by event_time) as online_users
from b
),
b2 as (
select live_id,event_time,online_users,max(online_users)over(partition by live_id) as max_online_users
from b1
),
b3 as (
select live_id,max_online_users,min(event_time) as first_peak_time,max(event_time) as last_peak_time
from b2
WHERE online_users = max_online_users
group by live_id,max_online_users
),
b4 as (
select b3.live_id,t2.live_nm,max_online_users,first_peak_time,last_peak_time
from b3
left join ks_live_t2 t2 on b3.live_id = t2.live_id
order by max_online_users desc
)
select * from b4;
with events as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time as event ,
1 as flag
from ks_live_t1
union all
select
ks_live_t1.live_id,
ks_live_t1.leave_time as event,
-1 as flag
from ks_live_t1),
seconds_agg
AS (
SELECT
live_id,
event,
SUM(flag) AS net_change
FROM events
GROUP BY live_id, event
),
kaisuan as(
select
live_id,
sum(net_change) over(partition by live_id order by event asc) as current_num
from seconds_agg
)
select
kaisuan.live_id,
ks_live_t2.live_nm,
max(current_num) as max_online_users
from kaisuan join ks_live_t2 on kaisuan.live_id=ks_live_t2.live_id
group by kaisuan.live_id,ks_live_t2.live_nm
order by max_online_users desc
with events as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time as event ,
1 as flag
from ks_live_t1
union all
select
ks_live_t1.live_id,
ks_live_t1.leave_time as event,
-1 as flag
from ks_live_t1),
kaisuan as(
select
live_id,
sum(flag) over(partition by live_id order by event asc, flag desc) as current_num
from events
)
select
kaisuan.live_id,
ks_live_t2.live_nm,
max(current_num) as max_online_users
from kaisuan join ks_live_t2 on kaisuan.live_id=ks_live_t2.live_id
group by kaisuan.live_id,ks_live_t2.live_nm
order by max_online_users desc
with events as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time as event ,
1 as flag
from ks_live_t1
union
select
ks_live_t1.live_id,
ks_live_t1.leave_time as event,
-1 as flag
from ks_live_t1),
kaisuan as(
select
live_id,
sum(flag) over(partition by live_id order by event asc, flag desc) as current_num
from events
)
select
kaisuan.live_id,
ks_live_t2.live_nm,
max(current_num) as max_online_users
from kaisuan join ks_live_t2 on kaisuan.live_id=ks_live_t2.live_id
group by kaisuan.live_id,ks_live_t2.live_nm
order by max_online_users desc
with events as(
select
ks_live_t1.live_id,
ks_live_t1.enter_time as event ,
1 as flag
from ks_live_t1
union
select
ks_live_t1.live_id,
ks_live_t1.leave_time as event,
-1 as flag
from ks_live_t1),
kaisuan as(
select
live_id,
sum(flag) over(partition by live_id order by event asc, flag desc) as current_num
from events
)
select
kaisuan.live_id,
ks_live_t2.live_nm,
max(current_num) as max_online_users
from kaisuan join ks_live_t2 on kaisuan.live_id=ks_live_t2.live_id
group by kaisuan.live_id,ks_live_t2.live_nm
order by max_online_users
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(distinct usr_id) as online_users
from ks_live_t1
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where "2021-09-12 23:48:38" between ks_live_t1.enter_time and ks_live_t1.leave_time
group by ks_live_t1.live_id,
ks_live_t2.live_nm
order by online_users desc
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(distinct usr_id) as online_users
from ks_live_t1
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where "2021-09-12 11:48:38" between ks_live_t1.enter_time and ks_live_t1.leave_time
group by ks_live_t1.live_id,
ks_live_t2.live_nm
order by online_users desc
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(distinct ks_live_t1.usr_id) as online_users
from ks_live_t1
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where "2021-09-12 11:48:38" between ks_live_t1.enter_time and ks_live_t1.leave_time
group by ks_live_t1.live_id,
ks_live_t2.live_nm
order by online_users desc
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(*) as online_users
from ks_live_t1
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where "2021-09-12 11:48:38" between ks_live_t1.enter_time and ks_live_t1.leave_time
group by ks_live_t1.live_id,
ks_live_t2.live_nm
order by online_users desc
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
count(*) as online_users
from ks_live_t1
join ks_live_t2
on ks_live_t1.live_id=ks_live_t2.live_id
where "2021-09-12 11:48:38" between ks_live_t1.enter_time and ks_live_t1.leave_time
group by ks_live_t1.live_id,
ks_live_t2.live_nm
with first as (
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as total_duration,
count(distinct ks_live_t1.usr_id) as total_users,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) / count(distinct ks_live_t1.usr_id) as avg_duration
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
where ks_live_t1.enter_time >= '2021-09-12 23:00:00' and ks_live_t1.enter_time <='2021-09-13 03:59:59'
group byks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type
),
rnk1 as(
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc)as rnk
from first)
select live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from rnk1
where rnk <=1
order by live_id
with first as (
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as total_duration,
count(distinct ks_live_t1.usr_id) as total_users,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) / count(distinct ks_live_t1.usr_id) as avg_duration
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
where ks_live_t1.enter_time >= '2021-09-12 23:00:00' and enter_time <='2021-09-12 03:59:59'
group byks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type
),
rnk1 as(
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc)as rnk
from first)
select live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from rnk1
where rnk <=1
order by live_id
with first as (
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as total_duration,
count(distinct ks_live_t1.usr_id) as total_users,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) / count(distinct ks_live_t1.usr_id) as avg_duration
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
group byks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type
),
rnk1 as(
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_type order by avg_duration desc)as rnk
from first)
select live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from rnk1
where rnk <=1
order by live_id
with first as (
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) as total_duration,
count(distinct ks_live_t1.usr_id) as total_users,
sum(timestampdiff(second,ks_live_t1.enter_time,ks_live_t1.leave_time)) / count(distinct ks_live_t1.usr_id) as avg_duration
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
group byks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type
),
rnk1 as(
select
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration,
row_number()over(partition by live_nm order by avg_duration desc)as rnk
from first)
select live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
from rnk1
where rnk <=1
order by live_id
with first as(
select
ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type,
count(*) as enter_cnt,
row_number()over(partition by ks_live_t2.live_type order by count(*) desc) as rnk
from ks_live_t1
join ks_live_t2 on ks_live_t1.live_id=ks_live_t2.live_id
where date_format(ks_live_t1.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by ks_live_t1.live_id,
ks_live_t2.live_nm,
ks_live_t2.live_type)
select
live_id,
live_nm,
live_type,
enter_cnt
from first
where rnk <=1
order by live_id