SELECT 
    video_id, 
    title, 
    author_id,
    tag
FROM 
    ks_video_inf 
WHERE 
    LENGTH(tag) - LENGTH(REPLACE(tag, ' ', '')) + 1 > 3;

with a as(
select
	live_id,
	`enter_time` as action_time,
	1 as flag
from ks_live_t1
union all
select
	live_id,
	leave_time as action_time,
	-1 as flag
from ks_live_t1
),
b as (
select
	live_id,
	action_time,
	sum(flag) over(partition by live_id order by action_time) as prelive_pretime_cnt
from a
),
c as(
select
	live_id,
	action_time,
	prelive_pretime_cnt,
	max(prelive_pretime_cnt)over(partition by live_id) as target
from b
),
d as (
select
live_id,
target as max_online_users,
min(action_time) as first_peak_time,
max(action_time) as last_peak_time
from c
where prelive_pretime_cnt = target
group by live_id,target
)
select
	d.live_id,
ks_live_t2.live_nm,
max_online_users,
first_peak_time,
last_peak_time
from d
left join ks_live_t2 on d.live_id = ks_live_t2.live_id;

WITH UserActivity AS (
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.enter_time AS event_time,
        1 AS act
    FROM 
        ks_live_t1 t1
    UNION ALL
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.leave_time AS event_time,
        -1 AS act
    FROM 
        ks_live_t1 t1
),
CumulativeOnline AS (
    SELECT 
        live_id,
        event_time,
        SUM(act) OVER (PARTITION BY live_id ORDER BY event_time) AS online_users
    FROM 
        UserActivity
),
PeakOnline AS (
    SELECT 
        live_id,
        event_time,
        online_users,
        MAX(online_users) OVER (PARTITION BY live_id) AS max_online_users
    FROM 
        CumulativeOnline
),
PeakTimes AS (
    SELECT 
        live_id,
        event_time AS peak_time,
        max_online_users
    FROM 
        PeakOnline
    WHERE 
        online_users = max_online_users
)
SELECT 
    pt.live_id,
    t2.live_nm,
    pt.max_online_users
FROM 
    PeakTimes pt
JOIN 
    ks_live_t2 t2
ON 
    pt.live_id = t2.live_id
GROUP BY
    pt.live_id,
    t2.live_nm,
    pt.max_online_users
ORDER BY 
    pt.max_online_users DESC;

with a 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 rn
from ks_live_t1
left 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 a
where rn=1
order by live_id;

with a as(
select 
	ks_live_t1.live_id,
	ks_live_t1.usr_id
from ks_live_t1 
where enter_time <= '2021-9-12 23:48:38' and leave_time >= '2021-9-12 23:48:38'
)
select
	a.live_id,
ks_live_t2.live_nm,
count(distinct a.usr_id) as online_users
from a
left join ks_live_t2
on a.live_id = ks_live_t2.live_id
group bya.live_id,ks_live_t2.live_nm
order by online_users desc,live_id;

with a as(
select 
	ks_live_t1.live_id,
	ks_live_t1.usr_id
from ks_live_t1 
where enter_time < '2021-9-12 23:48:39' and leave_time > '2021-9-12 23:48:39'
)
select
	a.live_id,
ks_live_t2.live_nm,
count(distinct a.usr_id) as online_users
from a
left join ks_live_t2
on a.live_id = ks_live_t2.live_id
group bya.live_id,ks_live_t2.live_nm
order by online_users desc,live_id;

with a as(
select 
	ks_live_t1.live_id,
	ks_live_t1.usr_id
from ks_live_t1 
where enter_time <= '2021-9-12 23:48:39' and leave_time >= '2021-9-12 23:48:39'
)
select
	a.live_id,
ks_live_t2.live_nm,
count(distinct a.usr_id) as online_users
from a
left join ks_live_t2
on a.live_id = ks_live_t2.live_id
group bya.live_id,ks_live_t2.live_nm
order by online_users desc,live_id;

select 
	c.prd_id,
c.prd_nm,
sum(if(if_snd=1,1,0)) as exposure_count,
sum(if(if_vw=1,1,0 )) as view_count,
sum(if(if_cart=1,1,0)) as cart_count,
sum(if(if_buy=1,1,0)) as buy_count,
round(avg(if(if_vw=1,1,0 ))*100,2) as view_rate,
round(avg(if(if_cart=1,1,0))*100,2) as cart_rate,
round(avg(if(if_buy=1,1,0))*100,2) as buy_rate
from tb_pg_act_rcd a
left join tb_cst_bas_inf b on a.cust_uid = b.cust_uid
left join tb_prd_map c on a.prd_id = c.prd_id
group by c.prd_id,c.prd_nm;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancel_by_driver' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
left join hll_t2 on hll_t1.driver_id = hll_t2.usr_id
where hll_t2.role = 'driver'
group by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancel_by_driver' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
group by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancelled_by_driver' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
left join hll_t2 on hll_t1.driver_id = hll_t2.usr_id
where hll_t2.role = 'driver'
group by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancelled_by_driver' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
group by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancelled_by_driver_orders' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
left join hll_t2 on hll_t1.driver_id = hll_t2.usr_id
where hll_t2.role = 'driver' 
group by cty
order by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancelled_by_driver_orders' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
left join hll_t2 on hll_t1.driver_id = hll_t2.usr_id
where hll_t2.role = 'driver' 
group by cty;

select
	cty,
sum(case status when 'completed' then 1 else 0 end) as completed_orders,
sum(case status when 'cancel_by_usr' then 1 else 0 end ) as cancelled_by_usr_orders,
sum(case status when 'cancelled_by_driver_orders' then 1 else 0 end) as cancelled_by_driver_orders
from hll_t1
group by cty;

SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ,
    MAX(CASE WHEN fav.mch_id IS NOT NULL AND pchs.mch_id IS NOT NULL THEN 1 ELSE 0 END) AS both_collected_and_purchased,
    MAX(CASE WHEN fav.mch_id IS NOT NULL AND pchs.mch_id IS NULL THEN 1 ELSE 0 END) AS only_collected_not_purchased,
    MAX(CASE WHEN fav.mch_id IS NULL AND pchs.mch_id IS NOT NULL THEN 1 ELSE 0 END) AS only_purchased_not_collected,
    MAX(CASE WHEN fav.mch_id IS NULL AND pchs.mch_id IS NULL THEN 1 ELSE 0 END) AS neither_collected_nor_purchased,
    COUNT(pchs.mch_id) AS purchase_count
FROM 
    gd_inf gd
LEFT JOIN 
    (SELECT DISTINCT mch_id FROM xhs_fav_rcd) fav ON gd.gd_id = fav.mch_id
LEFT JOIN 
    xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
GROUP BY 
    gd.gd_id, gd.gd_nm, gd.gd_typ
ORDER BY 
    purchase_count DESC;

select 
	gd.gd_id,
gd.gd_nm,
gd.gd_typ,
case 
	when fav.mch_id is null and pchs.mch_id is null then '不收藏不购买'
when fav.mch_id is not null and pchs.mch_id is null then '收藏不购买'
when fav.mch_id is null and pchs.mch_id is not null then '购买不收藏'
else '收藏且购买'
end as category
from gd_inf as gd
left join (select distinct mch_id from xhs_fav_rcd) fav on gd.gd_id = fav.mch_id
left join (select distinct mch_id from xhs_pchs_rcd) pchs on gd.gd_id = pchs.mch_id
order by gd.gd_id;

select 
	year(dt) as Y,
cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京',
cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as '上海',
cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as '深圳',
cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as '广州'
from weather_rcd_china
where year(dt) between '2011' and '2022'
group by year(dt)

WITH data1 AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
data2 as(
select
	a.usr_id,
	a.login_date as first_date,
	b.login_date as next_date,
	datediff(b.login_date,a.login_date) as diff
from data1 a
left join data1 b
on a.usr_id = b.usr_id and b.login_date between date_add(a.login_date,interval 1 day) and date_add(a.login_date,interval 14 day)
)
select
	first_date as first_login_date,
round(count(distinct case when diff between 1 and 3 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when diff between 1 and 7 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when diff between 1 and 14 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_14_retention_rate
from data2
group by first_date
order by first_date;

WITH data1 AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
data2 as(
select
	a.usr_id,
	a.login_date as first_date,
	b.login_date as next_date,
	datediff(a.login_date,b.login_date) as diff
from data1 a
left join data1 b
on a.usr_id = b.usr_id and b.login_date between date_add(a.login_date,interval 1 day) and date_add(a.login_date,interval 14 day)
)
select
	first_date as first_login_date,
round(count(distinct case when diff between 1 and 3 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when diff between 1 and 7 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_7_retention_rate,
round(count(distinct case when diff between 1 and 14 then usr_id end)*100 / count(distinct usr_id),2) as t_plus_14_retention_rate
from data2
group by first_date
order by first_date;