SQL 代码语法检查：eBay 数据分析案例

本文提供四道 SQL 问题，涉及 eBay 数据分析，包括查询最低价格和最新日期、获取高 GMV 的买家信息、统计商品反馈评分以及计算卖家好评率。代码经过语法检查，无错误。

问题 1：获取历史最低价格的最新 listing_date

SELECT
	item_id,
	min_price,
	listing_date

FROM 
	  (SELECT
	    item_id,
	    MIN(price) OVER (PARTITION BY item_id) AS min_price,
	    price,
	    listing_date,
	    ROW_NUMBER() OVER (PARTITION BY item_id, price ORDER BY listing_date DESC) AS r 
		FROM p_cac_t.ebay_lstg) as t

WHERE 
  price = min_price 
  AND r = 1

问题 2：找到购买过至少两个卖家的前两位买家，按 GMV（价格 * 数量）排序并显示总 GMV

SELECT 
	buyer_id, 
	SUM(price * quantity) AS gmv

FROM (
	SELECT 
		l.*, 
		t.buyer_id, 
		t.seller_id, 
		t.quantity
	FROM (
		SELECT 
			item_id, 
			price, 
			listing_date,
			  CASE 
				WHEN LEAD(listing_date) OVER (PARTITION BY item_id ORDER BY listing_date) IS NULL THEN '9999-01-01'
				ELSE LEAD(listing_date) OVER (PARTITION BY item_id ORDER BY listing_date)
			END AS next_listing_date
		FROM p_cac_t.ebay_lstg
	) AS l
		JOIN p_cac_t.ebay_trans AS t
		ON t.item_id = l.item_id
			AND t.trans_timestamp >= l.listing_date
			AND t.trans_timestamp < l.next_listing_date
) t1

GROUP BY buyer_id
HAVING COUNT(DISTINCT seller_id) >= 2
ORDER BY gmv DESC
LIMIT 2

问题 3：统计所有商品的正面、中性和负面反馈数量（1 = 正面，0 = 中性，-1 = 负面）

SELECT t.item_id,
		SUM(CASE WHEN feedback_score = 1 THEN 1 ELSE 0 end) AS positive_count, 
			SUM(CASE WHEN feedback_score = 0 THEN 1 ELSE 0 end) AS neutral_count,
			SUM(CASE WHEN feedback_score = -1 THEN 1 ELSE 0 end) AS negative_count,

FROM p_cac_t.trans_fdback AS f

JOIN p_cac_t.ebay_trans AS t
ON f.trans_id = t.trans_id

GROUP BY t.item_id

问题 4：计算来自好评率大于等于 50% 的卖家的交易比例

WITH seller_positive_ratio AS (
  SELECT
    seller_id,
    SUM(CASE WHEN feedback_score = 1 THEN 1 ELSE 0 END) / COUNT(*) AS positive_ratio
  FROM
    p_cac_t.trans_fdback
  GROUP BY
    seller_id
)

SELECT
  (SELECT COUNT(*) / (SELECT COUNT(*) FROM p_cac_t.trans_fdback)
   FROM p_cac_t.trans_fdback
   WHERE seller_id IN (
     SELECT seller_id
     FROM seller_positive_ratio
     WHERE positive_ratio >= 0.5
   )
  )

结论

以上 SQL 代码经过语法检查，无错误。