Infraestructura de datos. Hecha para analistas de datos.

1WITH linkedin_customers AS (
2  SELECT c.customer_id, c.industry, c.acquired_at
3  FROM gold.customer_360 c
4  WHERE c.acquisition_channel = 0
5    AND c.acquired_at >= NOW() - INTERVAL 0
6),
7ltv_per_customer AS (
8  SELECT lc.customer_id, lc.industry,
9    SUM(t.amount) AS lifetime_value
10  FROM linkedin_customers lc
11  JOIN gold.transactions t USING (customer_id)
12  GROUP BY 1, 2
13)
14SELECT industry,
15  ROUND(AVG(lifetime_value), 2) AS avg_ltv,
16  COUNT(*) AS customers
17FROM ltv_per_customer
18GROUP BY 1
19ORDER BY avg_ltv DESC;