selecta.live_id,ks_live_t2.live_nm,a.count
from
(selectks_live_t1.live_id,count(ks_live_t1.usr_id) as count from ks_live_t1
where date_format(ks_live_t1.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by ks_live_t1.live_id
order by count desc
limit 5) as a
left join ks_live_t2 on a.live_id=ks_live_t2.live_id;
selecta.*,ks_live_t2.live_nm
from
(selectks_live_t1.live_id,count(ks_live_t1.usr_id) as count from ks_live_t1
where date_format(ks_live_t1.enter_time,'%Y-%m-%d %H')='2021-09-12 23'
group by ks_live_t1.live_id
order by count desc
limit 5) as a
left join ks_live_t2 on a.live_id=ks_live_t2.live_id;
selecta.*,ks_live_t2.live_nm
from
(selectks_live_t1.live_id,count(ks_live_t1.usr_id) as count from ks_live_t1
where ks_live_t1.enter_time<='2021-09-12 23:00:00'
and ks_live_t1.leave_time>'2021-09-12 23:00:00'
group by ks_live_t1.live_id
order by count desc
limit 5) as a
left join ks_live_t2 on a.live_id=ks_live_t2.live_id;
with data1 as (
select distinct
date(DATE_ADD(login_time,interval 1 day)) as T_before1,
date(login_time) as T_org,
usr_id
from user_login_log
where datediff(current_date, date(login_time)) <= 30),
data2 as (
select distinct
date(login_time) as T_org,
usr_id
from user_login_log
),
data3 as (
select
data1.T_org,
data1.usr_id,
case
when data2.T_org is null
then 0
else 1
end as count_T
from
data1
left join data2 on data1.T_before1=data2.T_org and data1.usr_id=data2.usr_id)
select
data3.T_org,
concat(round((sum(data3.count_T)/count(data3.usr_id))*100,2),"%") as T1_retention_rate
from data3
group by data3.T_org
order by data3.T_org asc
select *
from hand_permutations
where
concat(card1, card2) like '%A%A%' or
concat(card1, card2) like '%A%K%' or
concat(card1, card2) like '%K%K%' or
concat(card1, card2) like '%K%A%'
order by id;
select students.name,students.class_code,students.grade_code,teachers.name from students join teachers on students.class_code=teachers.head_teacher
order by students.student_id;
select a.prd_id,b.prd_nm,a.exposure_count
from
(select prd_id, sum(if_snd) as exposure_count from tb_pg_act_rcd
group by prd_id ) a
join tb_prd_map b on a.prd_id=b.prd_id
order by exposure_count desc limit 1