select
cust_uid
,date(trx_dt) as dt
from (
select
cust_uid
,trx_dt
,row_number()over(partition by cust_uid order by trx_dt desc) as rk
from mt_trx_rcd_f
) a
where rk=1
select
v_typ
,count(distinct if(flag>=3,t2.usr_id,null)) as multi_category_viewers
,count(distinct t2.usr_id) as total_viewers
,count(distinct if(flag>=3,t2.usr_id,null))*100/count(distinct t2.usr_id) as mcvi
from
bilibili_t3 t1
left join
bilibili_t20 t2
on t1.v_id=t2.v_id
left join (
select
a.usr_id
,count(distinct b.v_typ) as flag
from
bilibili_t20 a
join
bilibili_t3 b
on a.v_id=b.v_id
group by
a.usr_id
) t3
on t2.usr_id=t3.usr_id
group by
v_typ
order by
mcvi desc
WITH UserVideoTypes AS (
SELECT
t20.usr_id,
t3.v_typ
FROM
bilibili_t20 t20
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
),
UserViewCategories AS (
SELECT
usr_id,
COUNT(DISTINCT v_typ) AS view_category_count
FROM
UserVideoTypes
GROUP BY
usr_id
),
FilteredUsers AS (
SELECT
usr_id
FROM
UserViewCategories
WHERE
view_category_count >= 3
),
VideoCategoryCounts AS (
SELECT
v_typ,
COUNT(DISTINCT usr_id) AS total_viewers
FROM
UserVideoTypes
GROUP BY
v_typ
),
MultiCategoryViewers AS (
SELECT
v_typ,
COUNT(DISTINCT ut.usr_id) AS multi_category_viewers
FROM
UserVideoTypes ut
JOIN
FilteredUsers fu ON ut.usr_id = fu.usr_id
GROUP BY
v_typ
),
MCVI AS (
SELECT
vc.v_typ,
mc.multi_category_viewers,
vc.total_viewers,
(mc.multi_category_viewers * 100.0 / vc.total_viewers) AS mcv_index
FROM
VideoCategoryCounts vc
LEFT JOIN
MultiCategoryViewers mc ON vc.v_typ = mc.v_typ
)
SELECT
v_typ,
multi_category_viewers,
total_viewers,
mcv_index
FROM
MCVI
ORDER BY
mcv_index DESC;
select
v_typ
,count(distinct if(flag>=3,t2.usr_id,null)) as multi_category_viewers
,count(distinct t2.usr_id) as total_viewers
,round(count(distinct if(flag>=3,t2.usr_id,null))*100/count(distinct t2.usr_id),2) as mcvi
from
bilibili_t3 t1
left join
bilibili_t20 t2
on t1.v_id=t2.v_id
left join (
select
a.usr_id
,count(distinct b.v_typ) as flag
from
bilibili_t20 a
join
bilibili_t3 b
on a.v_id=b.v_id
group by
a.usr_id
) t3
on t2.usr_id=t3.usr_id
group by
v_typ
order by
mcvi desc
select
v_typ
,count(distinct if(flag>=3,t2.usr_id,null)) as multi_category_viewers
,count(distinct t2.usr_id) as total_viewers
,count(distinct if(flag>=3,t2.usr_id,null))/count(distinct t2.usr_id) as mcvi
from
bilibili_t3 t1
left join
bilibili_t20 t2
on t1.v_id=t2.v_id
left join (
select
a.usr_id
,count(distinct b.v_typ) as flag
from
bilibili_t20 a
join
bilibili_t3 b
on a.v_id=b.v_id
group by
a.usr_id
) t3
on t2.usr_id=t3.usr_id
group by
v_typ
order by
mcvi desc
with ord as (
select
trx_time
,mch_nm
,trx_amt
,lag(trx_amt,1) over(order by trx_time) as last_amt
,lag(trx_time,1) over(order by trx_time) as last_time
from
cmb_usr_trx_rcd
where
usr_id=5201314520
and mch_nm rlike '按摩|保健|休闲|会所'
and substr(trx_time,1,7)='2024-09'
)
select
t1.dt
,coalesce(FvckCnt,0) as FvckCnt
,coalesce(WithHand,0) as WithHand
,coalesce(WithBalls,0) as WithBalls
,coalesce(BlowJobbie,0) as BlowJobbie
,coalesce(Doi,0) as Doi
,coalesce(DoubleFly,0) as DoubleFly
,coalesce(Ohya,0) as Ohya
from (
select
substr(date_value,1,10) as dt
from
date_table
where
substr(date_value,1,7)='2024-09'
group by
dt
) t1
left join(
select
substr(trx_time,1,10) as dt
,count(1) as FvckCnt
,count(if(trx_amt=288,trx_time,null)) as WithHand
,count(if(trx_amt=388,trx_time,null)) as WithBalls
,count(if(trx_amt=588,trx_time,null)) as BlowJobbie
,count(if(trx_amt=888,trx_time,null)) as Doi
,count(if(trx_amt=1288,trx_time,null)) as DoubleFly
,count(if(trx_amt=1288 and last_amt=888 and datediff(trx_time,last_time)=0,trx_time,null)) as Ohya
from
ord
group by
dt
) t2
on
t1.dt=t2.dt
with base as(
select
t1.trx_mon
,coalesce(trx_amt,0) as trx_amt
from(
select
substr(date_value,1,7) as trx_mon
from date_table
where year(date_value)=2023
group by
trx_mon
) t1
left join(
select
substr(trx_time,1,7) as trx_mon
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where usr_id=5201314520
and year(trx_time)=2023
and mch_typ='休闲娱乐'
group by
trx_mon
) t2 on t1.trx_mon=t2.trx_mon
)
select
trx_mon
,sum(trx_amt) over(order by trx_mon)
from base
select
trx_mon
,sum(trx_amt) over(order by trx_mon)
from (
select
substr(trx_time,1,7) as trx_mon
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where usr_id=5201314520
and substr(trx_time,1,7) between '2023-01' and '2024-12'
and mch_typ='休闲娱乐'
group by
trx_mon
)a
SELECT
a.trx_mon,
COALESCE(b.last_day, '1900-01-01') AS last_day,
COALESCE(b.day_of_mon, 0) AS day_of_mon,
COALESCE(b.trx_amt, 0) AS trx_amt,
COALESCE(b.trx_cnt, 0) AS trx_cnt,
COALESCE(ROUND(b.avg_day_amt, 2), 0) AS avg_day_amt,
COALESCE(ROUND(b.avg_day_cnt, 2), 0) AS avg_day_cnt
FROM
(SELECT DISTINCT DATE_FORMAT(date_value, '%Y-%m') AS trx_mon
FROM date_table
WHERE DATE_FORMAT(date_value, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
) a
LEFT JOIN
(SELECT
DATE_FORMAT(a.trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(MAX(a.trx_time)) AS last_day,
DAY(LAST_DAY(MAX(a.trx_time))) AS day_of_mon,
SUM(a.trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(a.trx_amt) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_amt,
COUNT(*) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_cnt
FROM cmb_usr_trx_rcd a
LEFT JOIN cmb_mch_typ m ON a.mch_nm = m.mch_nm
WHERE a.usr_id = 5201314520
AND DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
AND (m.mch_typ = '休闲娱乐' OR m.mch_typ IS NULL)
AND HOUR(a.trx_time) IN (23, 0, 1, 2)
AND a.trx_amt >= 288
GROUP BY DATE_FORMAT(a.trx_time, '%Y-%m')
ORDER BY 1
) b
ON a.trx_mon = b.trx_mon
ORDER BY a.trx_mon;
with ord as (
select
date_format(t1.trx_time,'%Y-%m') as trx_mon
,last_day(max(t1.trx_time)) as last_day
,day(last_day(max(t1.trx_time))) as day_of_mon
,sum(trx_amt) as trx_amt
,count(*) as trx_cnt
,sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt
,count(*)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2 on t1.mch_nm=t2.mch_nm
where date_format(trx_time,'%Y-%m-%d') between '2023-01-01' and '2024-06-30'
and (t2.mch_typ='休闲娱乐' or t2.mch_typ is null)
and trx_amt>288
and hour(trx_time) in(23, 0, 1, 2)
and usr_id = '5201314520'
group by
date_format(t1.trx_time,'%Y-%m')
)
select
substr(date_value,1,7) as trx_mon
,coalesce(last_day,'1900-01-01') as last_day
,coalesce(day_of_mon,0) as day_of_mon
,coalesce(trx_amt,0) as trx_amt
,coalesce(trx_cnt,0) as trx_cnt
,round(coalesce(avg_day_amt,0),2) as avg_day_amt
,round(coalesce(avg_day_cnt,0),2) as avg_day_cnt
from date_table t1
left join ord t2
on date_format(date_value,'%Y-%m')=t2.trx_mon
where date_format(date_value,'%Y-%m') between ' 2023-01' and '2024-06'
order by
trx_mon
with ord as (
select
date_format(t1.trx_time,'%Y-%m') as trx_mon
,last_day(max(t1.trx_time)) as last_day
,day(last_day(max(t1.trx_time))) as day_of_mon
,sum(trx_amt) as trx_amt
,count(*) as trx_cnt
,sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt
,count(*)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2 on t1.mch_nm=t2.mch_nm
where date_format(trx_time,'%Y-%m-%d') between '2023-01-01' and '2024-06-30'
and (t2.mch_typ='休闲娱乐' or t2.mch_typ is null)
and trx_amt>288
and hour(trx_time) in(23, 0, 1, 2)
and usr_id = '5201314520'
group by
date_format(t1.trx_time,'%Y-%m')
)
select
substr(date_value,1,7) as trx_mon
,coalesce(last_day,'1900-01-01') as last_day
,coalesce(day_of_mon,0) as day_of_mon
,coalesce(trx_amt,0) as trx_amt
,coalesce(trx_cnt,0) as trx_cnt
,round(coalesce(avg_day_amt,0),2) as avg_day_amt
,round(coalesce(avg_day_cnt,0),2) as avg_day_cnt
from date_table t1
left join ord t2
on date_format(date_value,'%Y-%m')=t2.trx_mon
where date_format(date_value,'%Y-%m') between ' 2023-01' and '2024-06'
select
order_dt
,count(distinct order_id) as total
,count(distinct case when status='completed' and t2.banned=0 and t3.banned=0 then order_id else null end) as completed
,count(distinct case when status='completed' and t2.banned=0 and t3.banned=0 then order_id else null end)/count(distinct order_id) as rate
from hll_t1 t1
left join hll_t2 t2
on t1.usr_id=t2.usr_id
left join hll_t2 t3
on t1.driver_id=t3.usr_id
group by
order_dt
select
trx_mon
,last_day
,days_of_month
,trx_amt
,trx_cnt
,trx_amt/days_of_month
,trx_cnt/days_of_month
from(
select
substr(trx_time,1,7) as trx_mon
,last_day(trx_time) as last_day
,day(last_day(trx_time)) as days_of_month
,sum(t1.trx_amt) as trx_amt
,count(t1.usr_id) as trx_cnt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where usr_id=5201314520
and year(trx_time) in(2023,2024)
and mch_typ='休闲娱乐'
group by
trx_mon
,last_day
,days_of_month
)a
order by
trx_mon
select
substr(trx_time,1,7) as trx_mon
,last_day(trx_time) as last_day
,day(last_day(trx_time)) as days_of_mon
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where year(trx_time) in (2023,2024)
and usr_id=5201314520
and mch_typ='休闲娱乐'
group by
trx_mon
,last_day
,days_of_mon
select
substr(trx_time,1,7) as trx_mon
,last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024)
group by
trx_mon
,last_day
order by
1,2
select
t2.mch_typ
,t1.mch_nm
,count(t1.usr_id) as trx_cnt
,sum(t1.trx_amt) as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where t2.mch_typ is null and t1.usr_id = '5201314520' and year(trx_time)=2024
group by
t2.mch_typ
,t1.mch_nm
order by
trx_cnt desc
select
t2.mch_typ
,t1.mch_nm
,count(t1.usr_id) as trx_cnt
,sum(t1.trx_amt) as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
and t1.usr_id = '5201314520' and year(trx_time)=2024
where t2.mch_typ is null
group by
t2.mch_typ
,t1.mch_nm
order by
trx_cnt desc
select
t2.mch_typ
,t1.mch_nm
,count(t1.usr_id) as trx_cnt
,sum(t1.trx_amt) as trx_amt
from cmb_usr_trx_rcd t1
left join cmb_mch_typ t2
on t1.mch_nm=t2.mch_nm
where t2.mch_typ is null
group by
t2.mch_typ
,t1.mch_nm
order by
trx_cnt desc