with daily_unique_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
timestampdiff(day, date(login_time), current_date) < 30
group by
usr_id, login_date
),
retention_days as (
select
t1.usr_id,
t1.login_date as login,
t2.login_date,
datediff(t2.login_date,t1.login_date) as next_login
from
daily_unique_login t1
left join 
daily_unique_login t2 on t1.usr_id = t2.usr_id
where
t1.login_date < t2.login_date
)
select 
login,
round((sum(case when next_login = 1 then 1 else 0 end) / count(distinct usr_id) * 100),2),
round((sum(case when next_login = 1 then 1 else 0 end) / count(distinct usr_id) * 100),2),
round((sum(case when next_login = 1 then 1 else 0 end) / count(distinct usr_id) * 100),2),
round((sum(case when next_login = 1 then 1 else 0 end) / count(distinct usr_id) * 100),2)
from retention_days
group by login

WITH all_routes AS (
    SELECT r.start_loc, r.end_loc, l_start.loc_ctg AS start_ctg, l_end.loc_ctg AS end_ctg, COUNT(*) AS trip_count
    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
    GROUP BY r.start_loc, r.end_loc, l_start.loc_ctg, l_end.loc_ctg
),
ranked_routes AS (
    SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count,
           dense_rank() OVER (PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS route_rank
    FROM all_routes
)
SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;

SELECT car_cls, COUNT(*) AS trip_count
FROM didi_sht_rcd
GROUP BY car_cls
ORDER BY trip_count DESC;

SELECT *
FROM didi_sht_rcd
WHERE start_loc = '海底捞西丽店'
   OR end_loc = '海底捞西丽店'
ORDER BY start_tm ASC;

SELECT user_id
FROM apple_pchs_rcd
GROUP BY user_id
HAVING SUM(CASE WHEN product_type != 'iPhone' THEN 1 ELSE 0 END) = 0
order by 1

WITH user_activity AS (
    SELECT 
        pr.cust_uid,
        COUNT(DISTINCT CASE WHEN pr.if_vw = 1 THEN pr.prd_id END) AS browse_count,
        COUNT(DISTINCT CASE WHEN pr.if_buy = 1 THEN pr.prd_id END) AS buy_count
    FROM tb_pg_act_rcd pr
    GROUP BY pr.cust_uid
)
SELECT 
    ci.cust_uid,
    ci.age,
    ci.gdr,
    CASE 
        WHEN ci.age < 25 AND ci.gdr = 'F' THEN 1
        ELSE 0
    END AS young_lady,
    CASE 
        WHEN ua.browse_count >= 2 THEN 1
        ELSE 0
    END AS browsed_multiple_products,
    CASE 
        WHEN ua.buy_count >= 2 THEN 1
        ELSE 0
    END AS bought_multiple_products
FROM tb_cst_bas_inf ci
LEFT JOIN user_activity ua ON ci.cust_uid = ua.cust_uid
ORDER BY ci.cust_uid;

WITH product_actions AS (
    SELECT 
        prd_id,
        SUM(if_snd) AS exposure_count,
        SUM(if_vw) AS view_count,
        SUM(if_cart) AS cart_count,
        SUM(if_buy) AS buy_count
    FROM tb_pg_act_rcd
    GROUP BY prd_id
)
SELECT 
    pam.prd_id,
    pam.prd_nm,
    pa.exposure_count,
    pa.view_count,
    pa.cart_count,
    pa.buy_count,
    ROUND((pa.view_count / NULLIF(pa.exposure_count, 0)) * 100, 2) AS view_rate,
    ROUND((pa.cart_count / NULLIF(pa.exposure_count, 0)) * 100, 2) AS cart_rate,
    ROUND((pa.buy_count / NULLIF(pa.exposure_count, 0)) * 100, 2) AS buy_rate
FROM product_actions pa
JOIN tb_prd_map pam ON pa.prd_id = pam.prd_id
ORDER BY pam.prd_id;

WITH purchased_users AS (
    SELECT 
        prd_id,
        cust_uid
    FROM tb_pg_act_rcd
    WHERE if_buy = 1
)
SELECT 
    pm.prd_id,
    pm.prd_nm,
    COUNT(CASE WHEN ci.gdr = 'M' THEN 1 END) AS male_count,
    COUNT(CASE WHEN ci.gdr = 'F' THEN 1 END) AS female_count,
    COUNT(*) AS total_count
FROM purchased_users pu
JOIN tb_cst_bas_inf ci ON pu.cust_uid = ci.cust_uid
JOIN tb_prd_map pm ON pu.prd_id = pm.prd_id
GROUP BY pm.prd_id, pm.prd_nm
ORDER BY pm.prd_id;

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) / SUM(pr.if_snd) * 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;

WITH user_transactions AS (
    SELECT 
        mch_nm,
        cust_uid,
        DATE_FORMAT(trx_dt, '%Y') AS year
    FROM mt_trx_rcd1
),
user_transaction_counts AS (
    SELECT 
        mch_nm,
        year,
        cust_uid,
        COUNT(*) AS transaction_count
    FROM user_transactions
    GROUP BY mch_nm, year, cust_uid
),
total_users AS (
    SELECT 
        mch_nm,
        year,
        COUNT(DISTINCT cust_uid) AS total_users
    FROM user_transactions
    GROUP BY mch_nm, year
),
repeat_users AS (
    SELECT 
        mch_nm,
        year,
        COUNT(DISTINCT cust_uid) AS repeat_users
    FROM user_transaction_counts
    WHERE transaction_count >= 2
    GROUP BY mch_nm, year
)
SELECT 
    tu.mch_nm,
    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_nm = ru.mch_nm AND tu.year = ru.year
ORDER BY tu.mch_nm, tu.year;

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,1) 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 favorite_users AS (
    SELECT mch_id, cust_uid
    FROM xhs_fav_rcd
    GROUP BY mch_id, cust_uid
),
purchase_users AS (
    SELECT mch_id, cust_uid, pchs_tm
    FROM xhs_pchs_rcd
    GROUP BY mch_id, cust_uid, pchs_tm
),
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 user_conversions AS (
    SELECT DISTINCT f.cust_uid
    FROM xhs_fav_rcd f
    JOIN xhs_pchs_rcd p ON f.cust_uid = p.cust_uid AND f.mch_id = p.mch_id
    WHERE p.pchs_tm > f.fav_tm
)
SELECT COUNT(DISTINCT cust_uid) AS conversion_user_count
FROM user_conversions;

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

WITH fav_goods AS (
SELECT DISTINCT mch_id 
FROM xhs_fav_rcd
),
buy_goods AS (
SELECT DISTINCT mch_id 
FROM xhs_pchs_rcd
)
SELECT 
g.gd_id, 
g.gd_nm, 
g.gd_typ,
CASE 
WHEN f.mch_id IS NULL AND b.mch_id IS NULL THEN '未被收藏也未被购买'
WHEN f.mch_id IS NOT NULL AND b.mch_id IS NULL THEN '只被收藏未被购买'
WHEN f.mch_id IS NULL AND b.mch_id IS NOT NULL THEN '只被购买未被收藏'
WHEN f.mch_id IS NOT NULL AND b.mch_id IS NOT NULL THEN '既被收藏也被购买'
END AS goods_category
FROM 
gd_inf g
LEFT JOIN fav_goods f ON g.gd_id = f.mch_id
LEFT JOIN buy_goods b ON g.gd_id = b.mch_id;

SELECT 
    distinct gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_pchs_rcd pchs
JOIN 
    gd_inf gd ON pchs.mch_id = gd.gd_id
LEFT JOIN 
   xhs_fav_rcd fav ON gd.gd_id = fav.mch_id
WHERE 
    fav.mch_id IS NULL

SELECT DISTINCT
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_pchs_rcd pchs
JOIN 
    gd_inf gd ON pchs.mch_id = gd.gd_id
LEFT JOIN 
    xhs_fav_rcd fav ON gd.gd_id = fav.mch_id
WHERE 
    fav.mch_id IS NULL;

SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_fav_rcd fav
JOIN 
    gd_inf gd ON fav.mch_id = gd.gd_id
right JOIN 
    xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE 
    fav.mch_id IS NULL

SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_fav_rcd fav
JOIN 
    gd_inf gd ON fav.mch_id = gd.gd_id
right JOIN 
    xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE 
    fav.mch_id IS NULL
group by
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ

select
	distinct t3.gd_id,
t3.gd_nm,
t3.gd_typ
from
	xhs_fav_rcd t1
join 
	gd_inf t3 on t1.mch_id = t3.gd_id
left join
	xhs_pchs_rcd t2 on t1.mch_id = t2.mch_id
where
	t2.pchs_tm is null