with results as
(
select
live_id as live_id
,max(online_users) as max_online_users
,min(signal_time) as first_peak_time
,max(signal_time) as last_peak_time
from
(
select
live_id as live_id
,rank() over(partition by live_id order by online_users desc) as rnk
,signal_time
,online_users
from
(
select
live_id as live_id
,sum(in_or_out) over(partition by live_id order by signal_time asc) as online_users
,signal_time as signal_time
from
(
select
live_id as live_id
,enter_time as signal_time
,1 as in_or_out
from
ks_live_t1
union all
select
live_id as live_id
,leave_time as signal_time
,-1 as in_or_out
from
ks_live_t1
) as s1
) as s2
) as s3
where
rnk = 1
group by
live_id
)
select
s1.live_id as live_id
,s2.live_nm as live_nm
,s1.max_online_users as max_online_users
,s1.first_peak_time as first_peak_time
,s1.last_peak_time as last_peak_time
from
results as s1
left join
ks_live_t2 as s2
on
s1.live_id = s2.live_id
order by
max_online_users desc
with signal_table as
(
select
live_id as live_id
,enter_time as signal_time
,1 as enter_or_leave
from
ks_live_t1
union all
select
live_id as live_id
,leave_time as signal_time
,-1 as enter_or_leave
from
ks_live_t1
),
results as
(
select
live_id
,max(online_users) as max_online_users
from
(
select
live_id
,sum(enter_or_leave) over(partition by live_id order by signal_time asc) as online_users
from
signal_table
) as s1
group by
live_id
order by
max_online_users desc
)
select
s1.live_id as live_id
,s2.live_nm as live_nm
,s1.max_online_users as max_online_users
from
results as s1
left join
ks_live_t2 as s2
on
s1.live_id = s2.live_id
select
s1.live_id as live_id
,max(s2.live_nm) as live_nm
,count(*) as online_users
from
ks_live_t1 as s1
left join
ks_live_t2 as s2
on
s1.live_id = s2.live_id
where
'2021-09-12 23:48:38' between s1.enter_time and s1.leave_time
group by
s1.live_id
order by
online_users desc
WITH ranked_dates AS (
SELECT
user_id,
listen_date,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY listen_date) AS rn
FROM (
SELECT DISTINCT
user_id,
DATE(start_time) AS listen_date
FROM listen_rcd
) AS unique_dates
)
SELECT
user_id,
MAX(consecutive_days) AS max_consecutive_days
FROM (
SELECT
user_id,
COUNT(*) AS consecutive_days
FROM (
SELECT
user_id,
listen_date,
DATE_SUB(listen_date, INTERVAL rn DAY) AS date_group
FROM ranked_dates
) AS grouped_dates
GROUP BY
user_id,
date_group
) AS consecutive_counts
GROUP BY
user_id;
with temp as
(
select
user_id as user_id
,listen_date as listen_date
,row_number() over(partition by user_id order by listen_date asc) - 1 as rnk
,date_sub(listen_date, INTERVAL row_number() over(partition by user_id order by listen_date asc) - 1 DAY) as begin_date
from
(
select
distinct
user_id as user_id
,date(start_time) as listen_date
from
listen_rcd
) as s1
),
temp1 as
(
select
user_id
,begin_date
,count(*) as consecutive_days
from
temp
group by
user_id
,begin_date
)
select
user_id as user_id
,max(consecutive_days) as max_consecutive_days
from
temp1
group by
user_id
with temp as
(
select
cust_uid as cust_uid
,datediff(now(), max(trx_dt)) as recency
,count(*) as frequency
,sum(trx_amt) as monetary
from
mt_trx_rcd_f
group by
cust_uid
)
select
cust_uid as user_id
,case when recency > 30 then 1
when recency >= 10 then 2
else 3
end as recency_score
,case when frequency <= 5 then 1
when frequency <= 15 then 2
else 3
end as frequency_score
,case when monetary < 2000 then 1
when monetary <=5000 then 2
else 3
end as monetary_score
from
temp
order by
user_id
with temp as
(
select
cust_uid as cust_uid
,datediff(now(), max(trx_dt)) as recency
,count(*) as frequency
,sum(trx_amt) as monetary
from
mt_trx_rcd_f
group by
cust_uid
)
select
cust_uid as user_id
,case when recency > 30 then 1
when recency >= 10 then 2
else 3
end as recency_score
,case when recency <= 5 then 1
when recency <= 15 then 2
else 3
end as frequency_score
,case when recency < 2000 then 1
when recency <=5000 then 2
else 3
end as monetary_score
from
temp
order by
user_id
with rfm as
(
select
cust_uid as cust_uid
,datediff(now(), max(trx_dt)) as recency
,count(distinct trx_dt) as frequency
,round(avg(trx_amt), 2) as monetary
from
mt_trx_rcd_f
group by
cust_uid
)
select
cust_uid as user_id
,case when recency > 30 then 1
when recency >= 10 then 2
else 3
end as recency_score
,case when frequency <= 10 then 1
when frequency <= 20 then 2
else 3
end as frequency_score
,case when monetary < 150 then 1
when monetary <= 250 then 2
else 3
end as monetary_score
from
rfm
order by
user_id asc
with rfm as
(
select
cust_uid as cust_uid
,datediff(now(), max(trx_dt)) as recency
,count(distinct trx_dt) as frequency
,round(avg(trx_amt), 2) as monetary
from
mt_trx_rcd_f
group by
cust_uid
)
select
cust_uid as user_id
,case when recency > 30 then 1
when recency > 10 then 2
else 3
end as recency_score
,case when frequency <= 10 then 1
when frequency <= 20 then 2
else 3
end as frequency_score
,case when monetary < 150 then 1
when monetary <= 250 then 2
else 3
end as monetary_score
from
rfm
order by
user_id asc
select
cust_uid as cust_uid
,datediff(now(), max(trx_dt)) as recency
,count(*) as frequency
,sum(trx_amt) as monetary
from
mt_trx_rcd_f
group by
cust_uid
order by
cust_uid asc
with temp as
(
select
cust_uid as cust_uid
,count(*) as transaction_count
,count(distinct trx_dt) as active_days_count
from
mt_trx_rcd_f
group by
cust_uid
),
temp1 as
(
select
cust_uid as cust_uid
,transaction_count as transaction_count
,rank() over(order by transaction_count desc) as transaction_rank
,active_days_count as active_days_count
,rank() over(order by active_days_count desc) as active_days_rank
from
temp
)
select
*
,round(abs(transaction_rank * 1.0 - active_days_rank * 1.0), 0) as rank_difference
from
temp1
order by
rank_difference desc
,cust_uid asc
with temp as
(
select
cust_uid as cust_uid
,count(*) as transaction_count
,count(distinct trx_dt) as active_days_count
from
mt_trx_rcd_f
group by
cust_uid
),
temp1 as
(
select
cust_uid as cust_uid
,transaction_count as transaction_count
,rank() over(order by transaction_count desc) as transaction_rank
,active_days_count as active_days_count
,rank() over(order by active_days_count desc) as active_days_rank
from
temp
)
select
*
,abs(transaction_rank * 1.0 - active_days_rank * 1.0) as rank_difference
from
temp1
order by
rank_difference desc
,cust_uid asc
with temp as
(
select
cust_uid as cust_uid
,count(*) as transaction_count
,count(distinct trx_dt) as active_days_count
,abs(count(*) - count(distinct trx_dt)) as rank_difference
from
mt_trx_rcd_f
group by
cust_uid
)
select
cust_uid as cust_uid
,transaction_count as transaction_count
,rank() over(order by transaction_count desc) as transaction_rank
,active_days_count as active_days_count
,rank() over(order by active_days_count desc) as active_days_rank
,rank_difference as rank_difference
from
temp
order by
rank_difference desc
,cust_uid asc
select
s3.name as teacher_name
,count(distinct s1.student_id) as total_students
,count(distinct case when s2.score < 60 then s2.student_id else null end) as failed_students
,round(count(distinct case when s2.score < 60 then s2.student_id else null end)
/ count(distinct s1.student_id) * 100, 2) as failure_rate
from
students as s1
left join
scores as s2
on
s1.student_id = s2.student_id
left join
teachers as s3
on
s3.subject = s2.subject
and
s3.class_code like concat("%", s1.class_code, "%")
where
1 = 1
and s2.subject = '化学'
group by
s3.name
select
s1.class_code as class_code
,count(distinct s1.student_id) as num_students_90_plus
,round(avg(s3.score), 2) as avg_score_90_plus
,max(s2.name) as physics_teacher
from
students as s1
left join
teachers as s2
on
locate(s1.class_code, s2.class_code) > 0
left join
scores as s3
on
1 = 1
and s1.student_id = s3.student_id
and s2.subject = s3.subject
where
1 = 1
and s3.subject = '物理'
and s3.score >= 90
and s3.exam_date = '2024-6-30'
group by
s1.class_code
order by
avg_score_90_plus desc