with t1 as(
select
a.cust_uid,
count(distinct case when a.if_vw = 1 then a.prd_id end) as browse_count,
count(distinct case when a.if_buy = 1 then a.prd_id end) as buy_count
from
tb_pg_act_rcd a
group by
1
)
select
a.cust_uid,
a.age,
a.gdr,
case when
a.age < 25 and a.gdr = 'F' then 1
else 0 end as young_lady,
case when
b.browse_count >= 2 then 1
else 0
end as browsed_multiple_products,
case when
b.buy_count >= 2 then 1
else 0
end as bought_multiple_products
from
tb_cst_bas_inf a
left join
t1 b using(cust_uid)
order by
1
select
a.prd_id,
b.prd_nm,
sum(if_snd) as exposure_count,
sum(if_vw) as view_count,
sum(if_cart) as cart_count,
sum(if_buy) as buy_count,
round(100*sum(if_vw) / sum(if_snd),2) as view_rate,
round(100*sum(if_cart) / sum(if_snd),2) as cart_rate,
round(100*sum(if_buy) / sum(if_snd),2) as buy_rate
from
tb_pg_act_rcd a
join
tb_prd_map b using(prd_id)
group by
1,2
order by
1
select
a.prd_id,
b.prd_nm,
sum(if_snd) as exposure_count,
sum(if_vw) as view_count,
sum(if_cart) as cart_count,
sum(if_buy) as buy_count,
round(100*sum(if_vw) / sum(if_snd),2) as view_rate,
round(100*sum(if_cart) / sum(if_vw),2) as cart_rate,
round(100*sum(if_buy) / sum(if_cart),2) as buy_rate
from
tb_pg_act_rcd a
join
tb_prd_map b using(prd_id)
group by
1,2
order by
1
with t1 as(
select
prd_id,
cust_uid
from
tb_pg_act_rcd
where
if_buy = 1
)
select
a.prd_id,
c.prd_nm,
sum(case when b.gdr = 'M' then 1 else 0 end) as male_count,
sum(case when b.gdr = 'F' then 1 else 0 end) as female_count,
sum(case when b.gdr = 'M' then 1 else 0 end) + sum(case when b.gdr = 'F' then 1 else 0 end) as total_count
from
t1 a
join
tb_cst_bas_inf b using(cust_uid)
join
tb_prd_map c using(prd_id)
group by
1,2
order by
1
SELECT
pm.prd_id,
pm.prd_nm,
SUM(pr.if_snd) AS exposure_count,
SUM(pr.if_vw) AS view_count,
ROUND((SUM(pr.if_vw) / NULLIF(SUM(pr.if_snd), 0)) * 100, 2) AS view_rate
FROM tb_pg_act_rcd pr
JOIN tb_prd_map pm ON pr.prd_id = pm.prd_id
WHERE pm.prd_id = 'C'
GROUP BY pm.prd_id, pm.prd_nm
ORDER BY pm.prd_id;
select
t3.prd_id,
t3.prd_nm,
sum(t1.if_snd) as exposure_count
from
tb_prd_map t3
join
tb_pg_act_rcd t1 using(prd_id)
group by 1,2
order by 3 desc
limit 1
with t1 as(
select
mch_nm,
sum(trx_amt) as restaurant_revenue
from
mt_trx_rcd1
where
mch_typ2 = '面包甜点' and date_format(trx_dt, '%Y-%m') = '2021-03'
group by
1
),
t2 as(
select
sum(trx_amt) as category_revenue
from
mt_trx_rcd1
where
mch_typ2 = '面包甜点' and date_format(trx_dt,'%Y-%m') = '2021-03'
)
select
t1.mch_nm,
t1.restaurant_revenue,
t2.category_revenue,
round(100*t1.restaurant_revenue / t2.category_revenue , 2) as market_share
from
t1
cross join
t2
order by 1
WITH first_transactions AS (
SELECT
mch_nm,
cust_uid,
MIN(trx_dt) AS first_trx_dt
FROM mt_trx_rcd1
GROUP BY mch_nm, cust_uid
),
monthly_new_users AS (
SELECT
mch_nm,
DATE_FORMAT(first_trx_dt, '%Y-%m') AS month,
COUNT(DISTINCT cust_uid) AS new_users
FROM first_transactions
GROUP BY mch_nm, month
),
monthly_total_users AS (
SELECT
mch_nm,
DATE_FORMAT(trx_dt, '%Y-%m') AS month,
COUNT(DISTINCT cust_uid) AS total_users
FROM mt_trx_rcd1
GROUP BY mch_nm, month
)
SELECT
mnu.mch_nm,
mnu.month,
mtu.total_users,
mnu.new_users,
ROUND((mnu.new_users / NULLIF(mtu.total_users, 0)) * 100, 2) AS new_user_ratio
FROM monthly_new_users mnu
JOIN monthly_total_users mtu ON mnu.mch_nm = mtu.mch_nm AND mnu.month = mtu.month
ORDER BY mnu.mch_nm, mnu.month;
WITH user_transactions AS (
SELECT
mch_typ2,
cust_uid,
DATE_FORMAT(trx_dt, '%Y') AS year
FROM mt_trx_rcd1
),
user_transaction_counts AS (
SELECT
mch_typ2,
year,
cust_uid,
COUNT(*) AS transaction_count
FROM user_transactions
GROUP BY mch_typ2, year, cust_uid
),
total_users AS (
SELECT
mch_typ2,
year,
COUNT(DISTINCT cust_uid) AS total_users
FROM user_transactions
GROUP BY mch_typ2, year
),
repeat_users AS (
SELECT
mch_typ2,
year,
COUNT(DISTINCT cust_uid) AS repeat_users
FROM user_transaction_counts
WHERE transaction_count >= 2
GROUP BY mch_typ2, year
)
SELECT
tu.mch_typ2,
tu.year,
tu.total_users,
ru.repeat_users,
ROUND((ru.repeat_users / NULLIF(tu.total_users, 0)) * 100, 2) AS repurchase_rate
FROM total_users tu
LEFT JOIN repeat_users ru ON tu.mch_typ2 = ru.mch_typ2 AND tu.year = ru.year
ORDER BY tu.mch_typ2, tu.year;
with t1 as(
select
mch_nm,
cust_uid,
date_format(trx_dt, '%Y') as year
from
mt_trx_rcd1
),
t2 as(
select
mch_nm,
year,
cust_uid,
count(*) as user_transaction_count
from
t1
group by
1,2,3
),
t3 as(
select
mch_nm,
year,
count(distinct cust_uid) as total_user
from
t1
group by
1,2
),
t4 as(
select
mch_nm,
year,
count(distinct cust_uid) as repeat_users
from
t2
where
user_transaction_count >= 2
group by
1,2
)
select
t3.mch_nm,
t3.year,
t3.total_user,
t4.repeat_users,
round(100*t4.repeat_users / t3.total_user, 2) as repurchase_rate
from
t3 t3
left join t4 t4 using(mch_nm,year)
order by 1,2
WITH monthly_avg_spending AS (
SELECT
mch_nm,
DATE_FORMAT(trx_dt, '%Y-%m') AS month,
AVG(trx_amt) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm, month
),
lagged_avg_spending AS (
SELECT
mas.mch_nm,
mas.month,
mas.avg_spending,
LAG(mas.avg_spending) OVER (PARTITION BY mas.mch_nm ORDER BY mas.month) AS prev_avg_spending
FROM monthly_avg_spending mas
)
SELECT
las.mch_nm,
las.month,
las.avg_spending,
ROUND(((las.avg_spending - COALESCE(las.prev_avg_spending, 0)) / NULLIF(las.prev_avg_spending, 0)) * 100, 2) AS growth_rate
FROM lagged_avg_spending las
ORDER BY las.mch_nm, las.month;
WITH t1 AS (
SELECT
mch_nm,
DATE_FORMAT(trx_dt, '%Y-%m') AS month,
AVG(trx_amt) AS avg_spending
FROM mt_trx_rcd1
GROUP BY 1,2
),
t2 as(
select
a.mch_nm,
a.month,
a.avg_spending as base_avg,
b.avg_spending as next_avg
from
t1 a
left join
t1 b on a.mch_nm = b.mch_nm and a.mch_nm = date_sub(b.mch_nm , interval 1 day)
)
select
c.mch_nm,
c.month,
c.base_avg as avg_spending,
ROUND(((c.next_avg - COALESCE(c.base_avg, 0)) / NULLIF(c.base_avg, 0)) * 100, 2) AS growth_rate
from
t2 c
order by
1,2
WITH restaurant_avg_spending AS (
SELECT
mch_nm,
sum(trx_amt)/count(distinct cust_uid) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm
)
SELECT
ras.mch_nm,
ras.avg_spending,
CASE
WHEN ras.avg_spending < 100 THEN '低档'
WHEN ras.avg_spending >= 100 AND ras.avg_spending < 300 THEN '中档'
WHEN ras.avg_spending >= 300 THEN '高档'
END AS label
FROM restaurant_avg_spending ras
ORDER BY 2;
WITH user_mch_typ2_count AS (
SELECT
cust_uid,
COUNT(DISTINCT mch_typ2) AS mch_typ2_count
FROM mt_trx_rcd1
GROUP BY cust_uid
)
SELECT
umtc.cust_uid,
CASE
WHEN umtc.mch_typ2_count >= 7 THEN 1
ELSE 0
END AS label
FROM user_mch_typ2_count umtc
ORDER BY umtc.cust_uid;
SELECT
o.cty,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN o.status = 'cancel_by_usr' THEN 1 ELSE 0 END) AS cancelled_by_usr_orders,
SUM(CASE WHEN o.status = 'cancel_by_driver' THEN 1 ELSE 0 END) AS cancelled_by_driver_orders
FROM hll_t1 o
GROUP BY o.cty
ORDER BY o.cty;
select
cty,
sum(case when status = 'cancel_by_driver' then 1 else 0 end) as cancel_by_driver,
sum(case when status = 'completed' then 1 else 0 end) as completed,
sum(case when status = 'cancel_by_usr' then 1 else 0 end) as cancel_by_usr
from
hll_t1
group by
1
order by
1
select
cty,
sum(case when status = 'cancel_by_driver' then 1 else 0 end) as cancel_by_driver,
sum(case when status = 'completed' then 1 else 0 end) as completed,
sum(case when status = 'cancel_by_usr' then 1 else 0 end) as cancel_by_usr
from
hll_t1
group by
1
SELECT
o.cty,
o.status,
COUNT(*) AS order_count
FROM hll_t1 o
WHERE o.status IN ('completed', 'cancel_by_usr', 'cancel_by_driver')
GROUP BY o.cty, o.status
ORDER BY o.cty, o.status;