WITH VideoInteractions AS (
SELECT
v.author_id,
SUM(
w.if_like +
CASE WHEN w.comment_id IS NOT NULL THEN 1 ELSE 0 END +
w.if_retweet +
w.if_fav
) AS total_interactions,
COUNT(DISTINCT v.video_id) AS video_count
FROM ks_video_inf v
JOIN ks_video_wat_log w ON v.video_id = w.video_id
GROUP BY v.author_id
)
SELECT
author_id,
round(total_interactions * 1.0 / video_count,2) AS avg_interaction_index
FROM VideoInteractions
ORDER BY avg_interaction_index DESC;
with BrandPurchases as (
select
p.usr_id
,p.good_id
,p.session_id
,m.brnd_nm
fromjx_pchs_rcd p
join jx_gd_page_map m on p.good_id = m.gd_id
),
BrandSearches as (
select
q.usr_id
,q.search_tm
,q.key_word
,q.session_id
,b.brnd_nm
from jx_query_rcd q
joinBrandPurchasesb on q.session_id = b.session_id
),
key_wordCounts as (
select
brnd_nm
,key_word
,count(*) as keyword_count
from BrandSearches
group by brnd_nm, key_word
),
RankedKeywords as (
select
brnd_nm
,key_word
,keyword_count
,row_number() over(partition by brnd_nm order by key_word DESC) AS keyword_rank
from key_wordCounts
)
select
brnd_nm
,key_word
from RankedKeywords
where keyword_rank <= 3
order by brnd_nm, keyword_rank
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 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'
select
live_id
,live_nm
,enter_count enter_cnt
from (
select
t2.live_id live_id
,t2.live_nm live_nm
,count(*)enter_count
fromks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
where enter_time between '2021-09-12 23:00:00' and '2021-09-13 00:00:00'
group by live_id,live_nm
) as new_table
order by enter_cnt desc 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
fromks_live_t1t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time)
)as ew_table
order by hour_enteredasc
select
trx_amt,
count(1) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(1) / count(distinct usr_id) avg_trx_per_user
from cmb_usr_trx_rcd where mch_nm = '红玫瑰按摩保健休闲'
and (
(year(trx_time) = 2023 and month(trx_time) between 1 and 12)
or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by trx_amt
order by avg_trx_per_user desc
limit 5;
with data1 as (
select distinct
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
data2 as (
select
T.usr_id,
T.login_date as T_date,
T_1.login_date as T_1_date
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id
and datediff(T.login_date, T_1.login_date) = -1
)
select
T_date as first_login_date,
concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate
from
data2
group by
T_date
order by
T_date;
with MemberPurchases as(
select
user_id
,begin_date
,end_date
,pay_amount
,datediff(end_date, begin_date) + 1 as duration_days
from bilibili_m1
),
HolidayDates as (
select
m_date
from bilibili_m2 where m_date between '2022-01-31' and '2022-02-06'
),
DailyIncome as (
select
hd.m_date
,mp.user_id
,mp.begin_date
,mp.end_date
,mp.pay_amount
,mp.duration_days
,mp.pay_amount / mp.duration_days as daily_income
from
HolidayDates hd
join
MemberPurchases mp
on
hd.m_date between mp.begin_date and mp.end_date
)
select
round(sum(daily_income), 2) as total_holiday_income
from
DailyIncome
select
trx_amt trx_amt
,count(trx_amt) trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and year(trx_time) = '2024'
and month(trx_time) in (1, 2, 3, 4, 5,6, 7)
group by trx_amt order bytrx_cnt desc limit 5;
select
trx_amt trx_amt
,count(trx_amt) trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) >= '2024-01-01'
and date(trx_time) <= '2024-07-31'
group by trx_amt order bytrx_cnt desc limit 5;
select
date(trx_time) trx_date
,max(trx_amt) max_trx_amt
,min(trx_amt) min_trx_amt
,avg(trx_amt) avg_trx_amt
,sum(trx_amt) total_trx_amt
from cmb_usr_trx_rcd
where date(trx_time) >= '2024-09-01' and date(trx_time) < '2024-10-01'
and mch_nm = '红玫瑰按摩保健休闲' group by date(trx_time);
select
*
from cmb_usr_trx_rcd
where usr_id = '5201314520' and date(trx_time) between '2024-09-01' and '2024-09-30'
and (
(hour(trx_time) between '0' and '5')
or
(hour(trx_time) >= 22)
)
order by trx_time
select
gd.gd_id
,gd.gd_nm
,gd.gd_typ
,case
whenfav.mch_id is not null and pchs.mch_id is not 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 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
g.gd_id
,g.gd_nm
,g.gd_typ
from gd_inf g
left join xhs_fav_rcd f on g.gd_id = f.mch_id
joinxhs_pchs_rcd o on g.gd_id = o.mch_id
where f.cust_uid is null group by g.gd_id
select
g.gd_id
,g.gd_nm
,g.gd_typ
from gd_inf g
left join xhs_fav_rcd f on g.gd_id = f.mch_id
joinxhs_pchs_rcd o on g.gd_id = o.mch_id
where f.cust_uid is null
select
g.gd_id
,g.gd_nm
,g.gd_typ
from gd_inf g
left join xhs_fav_rcd f on g.gd_id = f.mch_id
joinxhs_pchs_rcd o on g.gd_id = o.mch_id
where f.cust_uid is null group by gd_id
select
*
from
cmb_usr_trx_rcd
where
date(trx_time)
between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id = '5201314520'
order by trx_time