找到降幅大于2%的每个用户；降幅：(最后一次行为发生时候的价格-当前价格)/行为发生时候的价格

表schema： a表字段：用户浏览行为表：user_id log_time log_price goods_id date b表字段：商品价格维表：goods_id current_price price_date 1）找到每个用户每天最后一次浏览行为发生的时候的价格； 2）找到降幅大于2%的每个用户；降幅：(最后一次行为发生时候的价格-当前价格)/行为发生时候的价格

 

 

WITH last_views AS (
SELECT
user_id,
goods_id,
log_price AS last_view_price,
date AS view_date
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id, date ORDER BY log_time DESC) AS rn
FROM a
) ranked
WHERE rn = 1
),
price_comparison AS (
SELECT
lv.user_id,
lv.goods_id,
lv.last_view_price,
b.current_price,
((lv.last_view_price - b.current_price) / lv.last_view_price) AS price_drop_percentage
FROM last_views lv
JOIN b
ON lv.goods_id = b.goods_id
AND lv.view_date = b.price_date
)
SELECT
user_id,
goods_id,
last_view_price,
current_price,
price_drop_percentage
FROM price_comparison
WHERE price_drop_percentage > 0.02;