with a as 
(select usr_id,date_format(login_time, '%Y-%m') as month ,count(usr_id)as cnt
 from 
user_login_log
 where login_time<='2024-07-31'
group by usr_id,date_format(login_time, '%Y-%m')
having cnt>=10 ) 
,a2 as (
select usr_id
,date_format(login_time, '%Y-%m')as month
,count(usr_id)as cnt
 from 
user_login_log
 where login_time>'2024-07-31'
group by usr_id,date_format(login_time, '%Y-%m')
 having cnt>=10)
selectcount(distinct a.usr_id) as inactive_user_count
from a left join a2
on a.usr_id=a2.usr_id
where a2.usr_id is null

with a as 
(select usr_id,month(login_time) month ,count(usr_id)as cnt 
 from 
user_login_log 
 where date(login_time)<='2024-07-31'
group by usr_id,month(login_time)
having cnt>=10 ) 
,a2 as (
select usr_id
,month(login_time) month 
,count(usr_id)as cnt 
 from 
user_login_log 
 where date(login_time)>'2024-07-31'
group by usr_id,month(login_time)
 having cnt>=10)
selectcount(distinct a.usr_id) as inactive_user_count
from a left join a2
on a.usr_id=a2.usr_id
where a2.usr_id is null

with a as 
(select uid,date(start_time)as date,
 sum(timestampdiff(second,start_time,end_time)) as watch_duration
 from ks_video_wat_log 
group by uid,date(start_time)),
b as (
select uid,round(avg(watch_duration),0) daily_avg_watch_time
from a 
group by uid
)
select * from b
order by daily_avg_watch_time desc
limit 5

with a as 
(select uid,date(start_time)as date,
 sum(timestampdiff(second,start_time,end_time)) as watch_duration
 from ks_video_wat_log 
group by uid,date(start_time)),
b as (
select uid,round(avg(watch_duration),0) daily_avg_watch_time
from a 
group by uid
)
select * from b
order by daily_avg_watch_time desc

with v as 
(select a.video_id,author_id,sum(if_like+case when comment_id IS NOT NULL then 1 else 0 end +if_retweet+if_fav )as hd from ks_video_wat_log a 
 join ks_video_inf b on a.video_id=b.video_id
 group by video_id,author_id),
 a as (select 
 author_id,round(avg(hd),2)as avg_interaction_index
 from v 
 group by author_id
)
select * from a
order by avg_interaction_index desc

with v as 
(select a.video_id,author_id,sum(if_like+case when comment_id IS NOT NULL then 1 else 0 end +if_retweet+if_fav )as hd from ks_video_wat_log a 
 join ks_video_inf b on a.video_id=b.video_id
 group by video_id,author_id),
 a as (select 
 author_id,avg(hd)as avg_interaction_index
 from v 
 group by author_id
)
select * from a
order by avg_interaction_index desc

with a as(select uid 
,video_id
,sum(timestampdiff(SECOND,start_time,end_time)) as duration
from ks_video_wat_log 
group by uid,video_id
 )
select uid,sum(duration)/count(video_id) as avg_watch_duration
 from a 
 group by uid
 order by avg_watch_duration desc

select author_id, count(case when if_AI_talking=1 then 1 else null end)ai_video_count
from ks_video_inf 
group by author_id
having ai_video_count>0
order by author_id
;

with a as
(select rx_month
 ,case WHEN trx_amt BETWEEN 0 AND 100 THEN '0-100'
            WHEN trx_amt BETWEEN 101 AND 1000 THEN '100-1k'
            WHEN trx_amt BETWEEN 1001 AND 10000 THEN '1k-1w'
 else '>1w'
end as amt_range
, usr_id
,trx_amt
from
(select usr_id
 ,date_format(trx_time,'%Y-%m') as rx_month
 ,sum(trx_amt) trx_amt
 from cmb_usr_trx_rcd 
group by usr_id,date_format(trx_time,'%Y-%m') 
)as a
 ),
total as (select 
 rx_month,count(usr_id) as amt
from a 
group by rx_month
 ) ,
c as (select 
 rx_month,amt_range,count(usr_id) as amt
from a 
group by rx_month,amt_range)
select
c.rx_month,c.amt_range,round(c.amt/total.amt*100,2) as percentage
 from c join total on c.rx_month=total.rx_month
 order by c.rx_month,c.amt_range

select live_id,live_nm,live_type,enter_cnt
from (
select *
,row_number()over(partition by live_type order by enter_cnt desc) as rn
from
(select a.live_id,live_nm,live_type
 ,count(distinct usr_id)as enter_cnt
from 
ks_live_t1 a left join ks_live_t2 b 
 on a.live_id=b.live_id
where date_format(enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by a.live_id,live_nm,live_type
) as a 
)as b 
where
rn=1
 order by live_id

select a.live_id,live_nm,count(distinct usr_id) as enter_cnt
from ks_live_t1a
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,live_nm
 order by enter_cnt desc
 limit 5

select lpad(hour_entered,2,'0') as hour_entered,enter_count
from 
(
select hour(enter_time)hour_entered, count( distinct usr_id) as enter_count
from ks_live_t1 
group by hour(enter_time)) as a
order by hour_entered

select lpad(hour_entered,2,'0') as hour_entered,enter_count
from 
(
select hour(enter_time)hour_entered, count( 1) as enter_count
from ks_live_t1 
group by hour(enter_time)) as a
order by hour_entered
limit 5;

select lpad(hour_entered,2,'0') as hour_entered,enter_count
from 
(
select hour(enter_time)hour_entered, count( distinct usr_id) as enter_count
from ks_live_t1 
group by hour(enter_time)) as a
order by hour_entered
limit 5;

select lpad(hour_entered,2,'0') as hour_entered,enter_count
from 
(
select hour(enter_time)hour_entered, count(distinct usr_id) as enter_count
from ks_live_t1 
group by hour(enter_time)) as a
limit 5;

select lpad(hour_entered, 2, '0') as hour_entered, enter_count
from(
  select 
hour(enter_time) as hour_entered,
  count(1) as enter_count
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time))
as new_table
order by hour_entered asc

select gd_id, 
gd_nm,
count(fav_trq) as fav_count
fromxhs_fav_rcd as a
join 
gd_inf as b 
on a.mch_id=b.gd_id
group by gd_id, gd_nm
order by 
fav_count desc
limit 1;