1with t1 as (
2    SELECT *,
3  CONCAT('$ ', ROUND(SUM(purchase_revenue_in_usd) OVER () / COUNT(user_pseudo_id) OVER (), 2)) as AOV,
4  ROUND(COUNT(user_pseudo_id) OVER () / COUNT(DISTINCT user_pseudo_id) OVER (), 2) AS Purchase_Frequency,
5  ROUND((SUM(purchase_revenue_in_usd) OVER () / COUNT(user_pseudo_id) OVER ()) * (COUNT(user_pseudo_id) OVER () / COUNT(DISTINCT user_pseudo_id) OVER ()), 2) AS CV,
6  PARSE_DATE('%Y%m%d', event_date) AS Order_Date,
7  FROM `tc-da-1.turing_data_analytics.raw_events`
8  WHERE event_name = 'purchase'),
9
10t2 AS (
11  SELECT user_pseudo_id, AOV, Purchase_Frequency, CV, Order_Date,
12  MIN(Order_Date) OVER (PARTITION BY user_pseudo_id) AS First_order,
13  MAX(Order_Date) OVER (PARTITION BY user_pseudo_id) AS Last_order
14  FROM t1
15  GROUP BY 1,2,3,4,5)
16
17  SELECT DISTINCT user_pseudo_id, AOV, Purchase_Frequency, CONCAT ('$ ', CV) AS Customer_Value, First_order, Last_order,
18  CONCAT('$ ', (CV * 3)) AS CLV
19  FROM t2
20  ORDER BY 7 DESC
21