WITH user_category_counts AS (
SELECT
t20.usr_id,
COUNT(DISTINCT t3.v_typ) AS category_count
FROM
bilibili_t20 t20
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t20.usr_id
HAVING
COUNT(DISTINCT t3.v_typ) >= 3
),
video_total_viewers AS (
SELECT
t3.v_typ,
COUNT(DISTINCT t20.usr_id) AS total_viewers
FROM
bilibili_t20 t20
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t3.v_typ
),
video_multi_viewers AS (
SELECT
t3.v_typ,
COUNT(DISTINCT ucc.usr_id) AS multi_category_viewers
FROM
user_category_counts ucc
JOIN
bilibili_t20 t20 ON ucc.usr_id = t20.usr_id
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t3.v_typ
)
SELECT
vtv.v_typ,
vmv.multi_category_viewers,
vtv.total_viewers,
ROUND((vmv.multi_category_viewers * 100.0 / vtv.total_viewers), 2) AS mcv_index
FROM
video_total_viewers vtv
JOIN
video_multi_viewers vmv ON vtv.v_typ = vmv.v_typ
ORDER BY
mcv_index DESC;
with At AS
(select
usr_id,
date(login_time)aslogin_date
from
user_login_log
where
datediff(current_date(),login_time) <=30
)
select
At.login_date,
concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100,2),'%') AS USR_retention
from
At
left join
At Bt on At.usr_id = Bt.usr_id
and
datediff(Bt.login_date,At.login_date) = 1
group by
At.login_date;
with At as (
select distinct usr_id,
date(login_time) as login_date
from user_login_log
where datediff(curdate(), login_time) <= 30
)
select At.login_date,
concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
left join At Bt on At.usr_id = Bt.usr_id
and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date
WITH LiveRoomStats AS (
SELECT
t1.live_id,
t2.live_nm,
t2.live_type,
SUM(TIMESTAMPDIFF(SECOND, t1.enter_time, t1.leave_time)) AS total_duration,
COUNT(DISTINCT t1.usr_id) AS total_users
FROM
ks_live_t1 t1
JOIN
ks_live_t2 t2
ON
t1.live_id = t2.live_id
WHERE
t1.enter_time >= '2021-09-12 23:00:00' AND t1.enter_time < '2021-09-13 04:00:00'
GROUP BY
t1.live_id, t2.live_nm, t2.live_type
),
AverageDuration AS (
SELECT
live_id,
live_nm,
live_type,
total_duration,
total_users,
total_duration / total_users AS avg_duration
FROM
LiveRoomStats
),
RankedLiveRooms 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
AverageDuration
)
SELECT
live_id,
live_nm,
live_type,
total_duration,
total_users,
avg_duration
FROM
RankedLiveRooms
WHERE
rnk = 1
ORDER BY
live_id;
WITH avg_time AS (
SELECT
t1.live_id,
t2.live_nm,
t2.live_type,
avg(timestampdiff(SECOND, enter_time, leave_time)) AS averagetime,
ROW_NUMBER() OVER (PARTITION BY t2.live_type ORDER BY avg(timestampdiff(SECOND, enter_time, leave_time)) DESC) AS rk
FROM
ks_live_t1 t1
JOIN
ks_live_t2 t2 ON t1.live_id = t2.live_id
WHERE
enter_time >= '2021-09-12 23:00:00'
AND leave_time <= '2021-09-13 03:59:59'
GROUP BY
t1.live_id, t2.live_nm, t2.live_type
)
SELECT
avg_time.live_id,
avg_time.live_nm,
avg_time.live_type
FROM
avg_time
WHERE
rk = 1;
WITH LiveRoomStats AS (
SELECT
t1.live_id,
t2.live_nm,
t2.live_type,
COUNT(*) AS enter_cnt,
ROW_NUMBER() OVER (PARTITION BY t2.live_type ORDER BY COUNT(*) DESC) AS rnk
FROM
ks_live_t1 t1
JOIN
ks_live_t2 t2
ON
t1.live_id = t2.live_id
WHERE
DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY
t1.live_id, t2.live_nm, t2.live_type
)
SELECT
live_id,
live_nm,
live_type,
enter_cnt
FROM
LiveRoomStats
WHERE
rnk <= 1
ORDER BY
live_id;
SELECT
t1.live_id,
t2.live_nm,
t2.live_type,
COUNT(usr_id) AS count_usr,
ROW_NUMBER() OVER(partition by t2.live_type ORDER BY COUNT(usr_id) DESC) AS rk
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 ON t1.live_id = t2.live_id
WHERE DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY t1.live_id, t2.live_nm, t2.live_type;
SELECT
t1.live_id,
t2.live_nm,
t2.live_type,
COUNT(*) AS count_usr,
ROW_NUMBER() OVER(partition by t2.live_type ORDER BY COUNT(*) DESC) AS rk
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 ON t1.live_id = t2.live_id
WHERE DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY t1.live_id, t2.live_nm, t2.live_type;
select t1.live_id, live_nm , count(usr_id) as count_usr
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
where date_format(t1.enter_time , '%Y-%m-%d %H') = '2021-09-12 23'
group by t1.live_id , live_nm
order by count_usr desc
limit 5
select t1.live_id, live_nm , count(usr_id) as count_usr
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
where '2021-9-12 23:00:00' between enter_time and leave_time
group by t1.live_id , live_nm
order by count_usr desc
limit 5
select lpad(hour(t1.enter_time), 2, '0') as hour_entered, count(usr_id) as enter_count
from ks_live_t1 t1join ks_live_t2 t2 on t1.live_id = t2.live_id
group by lpad(hour(t1.enter_time), 2, '0')
order by hour_entered asc
select hour(t1.enter_time) as hour_entered , count(usr_id) as enter_count
from ks_live_t1 t1join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(t1.enter_time)
order by hour_entered asc
select
t1.live_id,live_nm,count(usr_id) as online_usr
from
ks_live_t1 t1
join
ks_live_t2 t2 on t1.live_id = t2.live_id
where
'2021-9-12 23:48:38' between enter_time and leave_time
group by
t1.live_id,live_nm
order by
online_usr desc
select t1.live_id,live_nm ,count(usr_id)asonline_users
from ks_live_t1 t1
join ks_live_t2t2 on t1.live_id = t2.live_id
where enter_time<='2021-9-12 23:48:38' and (leave_time >='2021-9-12 23:48:38' or leave_time>'2021-9-13 00:00:00' )
group by live_id,live_nm
order by online_users desc
select t1.live_id,live_nm ,count(usr_id)asonline_users
from ks_live_t1 t1
join ks_live_t2t2 on t1.live_id = t2.live_id
where enter_time<'2021-9-12 23:48:38' and (leave_time >'2021-9-12 23:48:38' or leave_time>'2021-9-13 00:00:00' )
group by live_id,live_nm
order by online_users desc
select
year(list_date) as year,
sum((case when industry like '%地产' then 1 else 0 end)) as '地产',
sum((case when industry = '软件服务' then 1 else 0 end)) as '软件服务'
from
stock_info
where
year(list_date) between 2000 and 2024
group by
year
select
year(list_date) as year,
sum((case when industry like '%地产' then 1 else null end)) as '地产',
sum((case when industry = '软件服务' then 1 else null end)) as '软件服务'
from
stock_info
where
year(list_date) between 2000 and 2024
group by
year
select year(dt) as Y
,cast(ifnull(avg(case when city='beijing' then tmp_h else null end), 0) as decimal(4,2)) as '北京'
,cast(ifnull(avg(case when city='shanghai' then tmp_h else null end), 0) as decimal(4,2)) as 上海
,cast(ifnull(avg(case when city='shenzhen' then tmp_h else null end), 0) as decimal(4,2)) as 深圳
,cast(ifnull(avg(case when city='guangzhou' then tmp_h else null end), 0) as decimal(4,2)) as 广州
from
weather_rcd_china
where
year(dt) between 2011 and 2022
group by
year(dt)
with highest_tmp as
(
select
year(dt) as y,
(case when city = 'beijing' then tmp_h else null end) as bj_high,
(case when city = 'shanghai' then tmp_h else null end) as sh_high,
(case when city = 'guangzhou' then tmp_h else null end) as gz_high,
(case when city = 'shenzhen' then tmp_h else null end) as sz_high
from
weather_rcd_china
where
year(dt) between 2011 and 2022
)
select
y,
round(avg(bj_high),2) as bj,
round(avg(sh_high),2) as sh,
round(avg(gz_high),2) as gz,
round(avg(sz_high),2) as sz
from
highest_tmp
group by
y
select
substr(trx_time,1,7) as trx_mon ,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time)) ) as day_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from
cmb_usr_trx_rcd a
left join
cmb_mch_typ mon a.mch_nm = m.mch_nm
where
a.usr_id=5201314520
and ((YEAR(trx_time) = 2023) OR (YEAR(trx_time) = 2024 AND MONTH(trx_time) BETWEEN 1 AND 6))
and (m.mch_typ='休闲娱乐' or m.mch_typ is null)
and trx_amt > 288
and (TIME(trx_time) BETWEEN '23:00:00' AND '23:59:59'OR TIME(trx_time) BETWEEN '00:00:00' AND '03:00:00')
group by
substr(a.trx_time,1,7)
order by
1
WITH user_category_counts AS ( SELECT t20.usr_id, COUNT(DISTINCT t3.v_typ) AS category_count FROM bilibili_t20 t20 JOIN bilibili_t3 t3 ON t20.v_id = t3.v_id GROUP BY t20.usr_id HAVING COUNT(DISTINCT t3.v_typ) >= 3 ), video_total_viewers AS ( SELECT t3.v_typ, COUNT(DISTINCT t20.usr_id) AS total_viewers FROM bilibili_t20 t20 JOIN bilibili_t3 t3 ON t20.v_id = t3.v_id GROUP BY t3.v_typ ), video_multi_viewers AS ( SELECT t3.v_typ, COUNT(DISTINCT ucc.usr_id) AS multi_category_viewers FROM user_category_counts ucc JOIN bilibili_t20 t20 ON ucc.usr_id = t20.usr_id JOIN bilibili_t3 t3 ON t20.v_id = t3.v_id GROUP BY t3.v_typ ) SELECT vtv.v_typ, vmv.multi_category_viewers, vtv.total_viewers, ROUND((vmv.multi_category_viewers * 100.0 / vtv.total_viewers), 2) AS mcv_index FROM video_total_viewers vtv JOIN video_multi_viewers vmv ON vtv.v_typ = vmv.v_typ ORDER BY mcv_index DESC;