基于Window函数(lead,lag)的同分区数据聚合及客户状态计算问询

• 按groupid、buid、packageid分区获取前后行数据，同一分区内客户可能拥有两个套餐，且不能分组（需保留额外行）。
• 最终目标：计算上月与当月、上月与下月的数值差值，统计流失（churn）、**复购（reactivation）**等客户状态。
• 当前需基于invoice date、groupid、buid、item_id分区的金额总和调整查询，已标记需基于总和计算的数值。

数据示例 #

现有查询代码 #

WITH cte_period AS (
   SELECT
     *
   , "lag"(amount_usd, 1) OVER (PARTITION BY invoice_item_customer_id, invoice_item_related_customer_id, item_id ORDER BY "date_trunc"('month', date_invoice), package_name ASC) prev_amount
   , "lead"(amount_usd, 1) OVER (PARTITION BY invoice_item_customer_id, invoice_item_related_customer_id, item_id ORDER BY "date_trunc"('month', date_invoice), package_name ASC) next_amount
   , "lag"(date_invoice, 1) OVER (PARTITION BY invoice_item_customer_id, invoice_item_related_customer_id, item_id ORDER BY "date_trunc"('month', date_invoice), package_name ASC) prev_invoice_date
   , "lead"(date_invoice, 1) OVER (PARTITION BY invoice_item_customer_id, invoice_item_related_customer_id, item_id ORDER BY "date_trunc"('month', date_invoice), package_name ASC) next_invoice_date
   FROM
     group_cust
) 
, cte_status AS (
   SELECT
     *
   , (CASE WHEN (((amount_usd >= 0) AND (next_amount IS NULL)) AND ((date_invoice < "date_add"('month', -1, current_date)) OR ("date_trunc"('month', next_invoice_date) > "date_add"('month', 1, "date_trunc"('month', date_invoice))))) THEN 'churn_next_month' ELSE null END) churn
   , (CASE WHEN ((CAST("date_trunc"('month', first_invoice_date_package) AS date) = "date_trunc"('month', date_invoice)) AND (amount_usd >= 0)) THEN 'new' WHEN (((CAST("date_trunc"('month', first_invoice_date) AS date) < "date_trunc"('month', date_invoice)) AND (amount_usd >= 0)) AND ("date_trunc"('month', prev_invoice_date) < "date_add"('month', -1, "date_trunc"('month', date_invoice)))) THEN 'reactivation' WHEN ((first_invoice_date_buid > first_invoice_date) AND ("date_trunc"('month', date_invoice) = first_invoice_date_buid)) THEN 'group expansion' WHEN (((amount_usd >= 0) AND (prev_amount >= 0)) AND (amount_usd > prev_amount)) THEN 'upsell' WHEN (((amount_usd >= 0) AND (prev_amount >= 0)) AND (amount_usd < prev_amount)) THEN 'downsell'  WHEN (((amount_usd >= 0) AND (prev_amount IS NOT NULL)) AND (amount_usd = prev_amount)) THEN 'same' ELSE null END) donor_status
   FROM
     cte_period
) 

内容的提问来源于stack exchange，提问作者Caroline Ferreira