with t1 as(
select
cust_uid,
count(*) as transaction_count,
count(distinct trx_dt) as active_days_count
from
mt_trx_rcd_f
group by
cust_uid
),
t2 as(
select
cust_uid,
transaction_count,
CAST(rank() over(order by transaction_count desc) as signed) as transaction_rank,
active_days_count,
CAST(rank() over(order by active_days_count desc) as signed) as active_days_rank
from
t1)
select
cust_uid,
transaction_count,
transaction_rank,
active_days_count,
active_days_rank,
abs(transaction_rank - active_days_rank) as rank_difference
from
t2
order by
rank_difference DESC
with t1 as(
select
a.start_loc,
a.end_loc,
count(*) as trip_count
from
didi_sht_rcd a
join
loc_nm_ctg bstart on bstart.loc_nm = a.start_loc
join
loc_nm_ctg bend on bend.loc_nm = a.end_loc
where
bstart.loc_ctg = '酒店' and bend.loc_ctg = '餐饮'
group by
1,2
)
select
start_loc,end_loc,trip_count
from
t1
order by
trip_count desc
limit
1;
with t1 as(
select
a.start_loc,
a.end_loc,
count(*) as trip_count
from
didi_sht_rcd a
join
loc_nm_ctg bstart on bstart.loc_ctg = a.start_loc
join
loc_nm_ctg bend on bend.loc_ctg = a.end_loc
where
bstart.loc_ctg = '酒店' and bend.loc_ctg = '餐饮'
group by
1,2
)
select
start_loc,end_loc,trip_count
from
t1
order by
trip_count desc
limit
1;
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '住宅'
AND l_end.loc_ctg = '写字楼'
ORDER BY r.start_tm ASC;
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '餐饮'
AND l_end.loc_ctg = '餐饮'
ORDER BY r.start_tm ASC;
WITH ConvertedTime 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
),
CallDates AS (
SELECT
order_id,
cust_uid,
local_call_time,
DATE(local_call_time) AS call_date
FROM
ConvertedTime
),
NextDayCalls AS (
SELECT
c1.order_id AS order_id_1,
c1.call_date AS call_date_1,
c2.order_id AS order_id_2,
c2.call_date AS call_date_2
FROM
CallDates c1
JOIN
CallDates c2
ON
c2.call_date = DATE_ADD(c1.call_date, INTERVAL 1 DAY)
AND
c1.cust_uid = c2.cust_uid
AND
c1.order_id = c2.order_id
),
NextDayCallCount AS (
SELECT
COUNT(DISTINCT order_id_1) AS next_day_call_count
FROM
NextDayCalls
),
TotalOrderCount AS (
SELECT
COUNT(order_id) AS total_order_count
FROM
didi_order_rcd
where finish_time = '1970-01-01 00:00:00'
)
SELECT
ncc.next_day_call_count,
toc.total_order_count,
CONCAT(FORMAT((ncc.next_day_call_count * 1.0 / toc.total_order_count) * 100, 2), '%') AS next_day_call_ratio
FROM
NextDayCallCount ncc,
TotalOrderCount toc;
WITH ConvertedOrders AS (
SELECT
order_id,
cust_uid,
call_time AS utc_call_time,
DATE_ADD(call_time, INTERVAL -3 HOUR) AS br_call_time,
DATE(DATE_ADD(call_time, INTERVAL -3 HOUR)) AS br_call_date,
finish_time AS utc_finish_time,
CASE WHEN finish_time = '1970-01-01 00:00:00' THEN 1 ELSE 0 END AS is_unfinished
FROM didi_order_rcd
),
UnfinishedOrders AS (
SELECT
order_id,
cust_uid,
br_call_date
FROM ConvertedOrders
WHERE is_unfinished = 1
),
UserNextDayCalls AS (
SELECT
uo.order_id AS unfinished_order_id,
uo.cust_uid,
uo.br_call_date AS unfinished_date,
CASE
WHEN MAX(CASE WHEN co.br_call_date = DATE_ADD(uo.br_call_date, INTERVAL 1 DAY) THEN 1 ELSE 0 END) = 1
THEN 1
ELSE 0
END AS has_next_day_call
FROM UnfinishedOrders uo
LEFT JOIN ConvertedOrders co
ON uo.cust_uid = co.cust_uid
AND co.br_call_date >= uo.br_call_date
GROUP BY
uo.order_id, uo.cust_uid, uo.br_call_date
)
SELECT
COUNT(DISTINCT unfinished_order_id) AS total_unfinished_orders,
SUM(has_next_day_call) AS next_day_call_orders,
CONCAT(
FORMAT(
(SUM(has_next_day_call) * 1.0 / COUNT(DISTINCT unfinished_order_id)) * 100,
2
),
'%'
) AS next_day_call_ratio
FROM UserNextDayCalls;
select hour(local_call_time) as local_hour, count(1) as cnt
from
(
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
)t
group by hour(local_call_time) order by 2 desc
SELECT
COUNT(order_id) AS total_orders,
SUM(CASE WHEN grab_time != '1970-01-01 00:00:00' THEN 1 ELSE 0 END) AS answered_orders,
CONCAT(FORMAT((SUM(CASE WHEN grab_time != '1970-01-01 00:00:00' THEN 1 ELSE 0 END) * 1.0 / COUNT(order_id)) * 100, 2), '%') AS answer_rate
FROM
didi_order_rcd
WHERE
DATE(call_time) = '2021-05-03';
WITH favorite_users AS (
SELECT mch_id, cust_uid
FROM xhs_fav_rcd
GROUP BY mch_id, cust_uid
),
favorite_counts AS (
SELECT mch_id, COUNT(DISTINCT cust_uid) AS fav_user_count
FROM favorite_users
GROUP BY mch_id
),
conversion_counts AS (
SELECT f.mch_id, COUNT(DISTINCT f.cust_uid) AS conv_user_count
FROM favorite_users f
JOIN xhs_fav_rcd fv ON f.mch_id = fv.mch_id AND f.cust_uid = fv.cust_uid
JOIN xhs_pchs_rcd p ON f.mch_id = p.mch_id AND f.cust_uid = p.cust_uid
WHERE p.pchs_tm > fv.fav_tm
GROUP BY f.mch_id
)
SELECT
fc.mch_id,
fc.fav_user_count,
COALESCE(cc.conv_user_count, 0) AS conv_user_count,
COALESCE(ROUND((cast(cc.conv_user_count as float) / fc.fav_user_count) * 100, 2), 0) AS conversion_rate
FROM favorite_counts fc
LEFT JOIN conversion_counts cc ON fc.mch_id = cc.mch_id
ORDER BY conversion_rate DESC
with t1 as(
select distinct a.cust_uid
from xhs_fav_rcd a
join xhs_pchs_rcd b on a.cust_uid = b.cust_uid and a.mch_id = b.mch_id and a.fav_tm < b.pchs_tm
)
SELECT COUNT(DISTINCT cust_uid) AS conversion_user_count
FROM t1;
SELECT
gd.gd_id,
gd.gd_nm,
gd.gd_typ,
MAX(CASE WHEN fav.mch_id IS NOT NULL AND pchs.mch_id IS NOT NULL THEN 1 ELSE 0 END) AS both_collected_and_purchased,
MAX(CASE WHEN fav.mch_id IS NOT NULL AND pchs.mch_id IS NULL THEN 1 ELSE 0 END) AS only_collected_not_purchased,
MAX(CASE WHEN fav.mch_id IS NULL AND pchs.mch_id IS NOT NULL THEN 1 ELSE 0 END) AS only_purchased_not_collected,
MAX(CASE WHEN fav.mch_id IS NULL AND pchs.mch_id IS NULL THEN 1 ELSE 0 END) AS neither_collected_nor_purchased,
COUNT(pchs.mch_id) AS purchase_count
FROM
gd_inf gd
LEFT JOIN
(SELECT DISTINCT mch_id FROM xhs_fav_rcd) fav ON gd.gd_id = fav.mch_id
LEFT JOIN
xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
GROUP BY
gd.gd_id, gd.gd_nm, gd.gd_typ
ORDER BY
purchase_count DESC;
with t1 as (
select
q.usr_id
,c.click_page_id
,q.search_tm
,q.session_id
from
jx_query_rcd q
left join
jx_click_rcd c on q.usr_id = c.usr_id and q.session_id = c.session_id
)
select
count(search_tm) as total_searches
,count(click_page_id) as total_clicks
,round(100*count(click_page_id) / nullif(count(search_tm),0) ,2) as click_rate
from
t1
with t1 as (
select
q.usr_id
,c.click_page_id
,q.search_tm
,q.session_id
from
jx_query_rcd q
left join
jx_click_rcd c on q.usr_id = c.usr_id and q.session_id = c.session_id
)
select
count(search_tm) as total_searches
,count(click_page_id) as total_clicks
,round(100*count(search_tm) / nullif(count(click_page_id),0) ,2) as click_rate
from
t1
SELECT
gd.gd_id,
gd.gd_nm,
gd.gd_typ,
CASE
WHEN fav.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
gd_id,
gd_nm,
gd_typ
from
gd_inf a
join
xhs_pchs_rcd b on a.gd_id = b.mch_id
left join
xhs_fav_rcd c on a.gd_id = c.mch_id
where
c.cust_uid is null
group by
1,2,3
select distinct
a.gd_id,
a.gd_nm,
a.gd_typ
from
gd_inf a
inner join
xhs_fav_rcd b on a.gd_id = b.mch_id
left join
xhs_pchs_rcd c on a.gd_id = c.mch_id
where
c.mch_id is null
select
a.gd_id,
a.gd_nm,
a.gd_typ
from
gd_inf a
inner join
xhs_fav_rcd b on a.gd_id = b.mch_id
left join
xhs_pchs_rcd c on a.gd_id = c.mch_id
where
c.mch_id is null
select
a.gd_id,
a.gd_nm,
a.gd_typ
from
gd_inf a
inner join
xhs_fav_rcd b on a.gd_id = b.mch_id
left join
xhs_pchs_rcd c on a.gd_id = c.mch_id
where
c.mch_id = null
group by
a.gd_id,
a.gd_nm,
a.gd_typ
SELECT
gd.gd_id,
gd.gd_nm,
gd.gd_typ
FROM
xhs_fav_rcd fav
left join
gd_inf gd ON fav.mch_id = gd.gd_id
LEFT JOIN
xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE
pchs.mch_id IS NULL
group by
gd.gd_id,
gd.gd_nm,
gd.gd_typ
select
a.gd_typ,
count(distinct b.cust_uid) as buyer_count
from
gd_inf a
left join
xhs_pchs_rcd b
on
a.gd_id = b.mch_id
group by
a.gd_typ
order by
buyer_count desc
limit
1