问统计用户购买前的访问次数。(每次购买后应重置计数)在Presto中

EN

WITH
-- your input, don't use in final query
visits(wk,visit_id,cust_id,has_purchased) AS (
            SELECT 1,1,1,FALSE
  UNION ALL SELECT 1,2,1,FALSE
  UNION ALL SELECT 1,3,1,TRUE
  UNION ALL SELECT 1,2,1,TRUE
)
-- real query starts here, replace following comma with "WITH"
,
with_counter AS (
  SELECT
    *
  , LAG(CASE WHEN has_purchased THEN 1 ELSE 0 END,1,0)
    OVER(PARTITION BY wk,cust_id ORDER BY visit_id)  AS grp_end
   FROM visits
)
SELECT
  wk
, cust_id
, grp_end
, COUNT(*) AS visits_before_purchase
FROM with_counter
GROUP BY
  wk
, cust_id
, grp_end
;
-- out  wk | cust_id | grp_end | visits_before_purchase 
-- out ----+---------+---------+------------------------
-- out   1 |       1 |       0 |                      3
-- out   1 |       1 |       1 |                      1

SELECT weeks, visit_id, customer_id, purchase_flag, 
CASE WHEN diff IS null then visit_id else diff 
end
FROM (
    SELECT *, visit_id - LAG(visit_id) over (partition by customer_id  order by 
    customer_id, visit_id)as diff 
    FROM customer 
    WHERE purchase_flag = 1
   ) as t ORDER BY customer_id, visit_id