with specific_cust_visited as (
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
cust_uid = 'MT10000' and
mch_nm = '兰州李晓明拉面馆'
),
all_cust_visited as (
select
cust_uid,
mch_nm
from
mt_trx_rcd1
where
mch_nm = '兰州李晓明拉面馆'
)
select
distinct scv.cust_uid,
acv.cust_uid as cust_uid_1,
scv.mch_nm
from
specific_cust_visited scv
left join
all_cust_visited acv
on scv.cust_uid != acv.cust_uid
order by
acv.cust_uid asc
select *
from
(select
cust_uid
from
mt_trx_rcd1
where
cust_uid='MT10000'
group by
cust_uid) a
left join
(select
cust_uid, mch_nm
from
mt_trx_rcd1
where
mch_nm='兰州李晓明拉面馆'
group by
cust_uid, mch_nm
order by 1) b
on a.cust_uid <> b.cust_uid
select
distinct m1.cust_uid as cust_uid,
m2.cust_uid as cust_uid_1,
m1.mch_nm
from
mt_trx_rcd1 m1
join
mt_trx_rcd1 m2
on m1.mch_nm = m2.mch_nm
where
m1.cust_uid = 'MT10000'
and m1.mch_nm = '兰州李晓明拉面馆'
and m2.cust_uid != 'MT10000'
order by
m2.cust_uid asc
select
m1.cust_uid as cust_uid,
m2.cust_uid as cust_uid_1,
m1.mch_nm
from
mt_trx_rcd1 m1
join
mt_trx_rcd1 m2
on m1.mch_nm = m2.mch_nm
where
m1.cust_uid = 'MT10000'
and m1.mch_nm = '兰州李晓明拉面馆'
and m2.cust_uid != 'MT10000'
order by
m2.cust_uid asc
with ctg_mapping as (
select
dsr.cust_uid,
start_loc,
end_loc,
start_tm,
car_cls,
l1.loc_ctg as loc_ctg_1,
l2.loc_ctg as loc_ctg_2
from
didi_sht_rcd dsr
left join
loc_nm_ctg l1
on
dsr.start_loc = l1.loc_nm
left join
loc_nm_ctg l2
on
dsr.end_loc = l2.loc_nm
)
select
cust_uid,
start_loc,end_loc,start_tm,car_cls
from
ctg_mapping
where
loc_ctg_1= '餐饮' andloc_ctg_2 = '餐饮'
with ctg_mapping as (
select
dsr.cust_uid,
start_loc,
end_loc,
start_tm,
car_cls,
l1.loc_ctg as loc_ctg_1,
l2.loc_ctg as loc_ctg_2
from
didi_sht_rcd dsr
left join
loc_nm_ctg l1
on
dsr.start_loc = l1.loc_nm
left join
loc_nm_ctg l2
on
dsr.end_loc = l2.loc_nm
)
select
cust_uid,
start_loc,end_loc,start_tm,car_cls
from
ctg_mapping
where
loc_ctg_1 = loc_ctg_2 = '餐饮'
with converted_time as (
select
order_id,
cust_uid,
date_add(call_time,interval -3 hour) as local_call_time,
grab_time,
cancel_time,
finish_time
from
didi_order_rcd
),
call_date as (
select
order_id,
cust_uid,
date(local_call_time) as call_date,
finish_time
from
converted_time
),
next_day_call as (
select
cd1.order_id as order_id_1,
cd1.call_date as call_date_1,
cd2.order_id as order_id_2,
cd2.call_date as call_date_2
from
call_date cd1
left join
call_date cd2
on cd1.order_id = cd2.order_id
and cd1.cust_uid = cd2.cust_uid
and datediff(cd2.call_date,cd1.call_date) = 1
),
next_day_call_counts as(
select
count(distinct order_id_2) as next_day_call_count
from
next_day_call
),
uncomplete_orders as (
select
count(distinct order_id) as uncomplete_order
from
didi_order_rcd
where
finish_time = '1970-01-01 00:00:00'
)
select
ndcc.next_day_call_count,
uo.uncomplete_order as total_order_count,
concat(format(ndcc.next_day_call_count/uo.uncomplete_order*100.0,2),'%') as next_day_call_ratio
from
uncomplete_orders uo,
next_day_call_counts ndcc
with local_call_time as (
select
order_id,
cust_uid,
date_add(call_time, interval -3 hour) as call_time,
grab_time,
cancel_time,
finish_time
from
didi_order_rcd
)
select
hour(call_time) as local_hour,
count(order_id) as cnt
from
local_call_time
group by
hour(call_time)
order by
count(order_id) desc
select
count(order_id) as total_orders,
sum(case when date(grab_time) <> '1970-01-01' then 1 else 0 end ) as answered_orders,
concat(format(sum(case when date(grab_time) <> '1970-01-01' then 1 else 0 end )/ count(order_id)*100,2),'%') as answered_rate
from
didi_order_rcd
where
date(call_time) = '2021-5-3'
select
gd_id,
gd_nm,
gd_typ
from
gd_inf gd
join
xhs_pchs_rcd pchs
on gd.gd_id = pchs.mch_id
left join
xhs_fav_rcd fav
on gd.gd_id = fav.mch_id
where
fav.mch_id is null
group by
gd_id,
gd_nm,
gd_typ
select
gd_id,
gd_nm,
gd_typ
from
gd_inf as gi
join
xhs_fav_rcd as xfr
on
xfr.mch_id = gi.gd_id
left join
xhs_pchs_rcd as xpr
on
gi.gd_id = xpr.mch_id
where
xpr.mch_id is null
group by
gd_id,
gd_nm,
gd_typ
select
gi.gd_typ,
count(distinct xpr.cust_uid) as buyer_count
from
gd_inf as gi
join
xhs_pchs_rcd as xpr
on
gi.gd_id = xpr.mch_id
group by
gi.gd_typ
order by
buyer_count desc
limit 1
select
gd_id,
gd_nm,
count(cust_uid) as fav_count
from
xhs_fav_rcd xfr
right join
gd_inf gi
on
xfr.mch_id = gi.gd_id
group by
gd_id,
gd_nm
order by
fav_count desc
limit 1
with monthly_trx_amt as (
select date_format(u.trx_time, '%Y-%m') as trx_mon,
sum(u.trx_amt) as monthly_trx_amt
from cmb_usr_trx_rcd u
join
cmb_mch_typ t on u.mch_nm = t.mch_nm
where
u.usr_id = 5201314520
and left(u.trx_time,7) between '2023-01' and '2024-12'
and t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon
),
cumulative_trx as(
select trx_mon,
monthly_trx_amt,
sum(monthly_trx_amt)over (order by trx_mon) as cumulative_trx_amt
from
monthly_trx_amt
)
select trx_mon,
cumulative_trx_amt as trx_amt
from
cumulative_trx
order by
trx_mon
with all_top1_trx as (
select
'all' as mch_typ,
mch_nm,
count(*) as trx_cnt,
dense_rank()over(order by count(*) desc) as rnk
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by mch_nm
),
category_top1_trx as(
select
t.mch_typ,
t.mch_nm,
count(u.trx_amt) as trx_cnt,
dense_rank()over(partition by t.mch_typ order by count(u.trx_amt) desc) as rnk
from cmb_mch_typ t
join cmb_usr_trx_rcd u on t.mch_nm = u.mch_nm
where u.usr_id = 5201314520
and t.mch_typ in ('交通出行', '休闲娱乐','咖啡奶茶')
group by t.mch_typ, t.mch_nm
),
filtered_all_top1_trx as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
all_top1_trx
where
rnk <= 1
),
filtered_category_top1_trx as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from category_top1_trx
where rnk <= 1
)
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from filtered_all_top1_trx
union all
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
filtered_category_top1_trx
order by
mch_typ