-- Positive invoice value: complete months, positive quantities/prices, cancellations excluded.
-- Exact-row deduplication occurs in analyze.py; see the quantified sensitivity check.
SELECT month, ROUND(SUM(value),2) AS positive_value,
       COUNT(DISTINCT invoice) AS orders,
       ROUND(SUM(value)/COUNT(DISTINCT invoice),2) AS aov
FROM sales GROUP BY month ORDER BY month;
-- COHORT
-- First OBSERVED purchase cohort, not necessarily true lifetime acquisition.
WITH first_purchase AS (
  SELECT customer, MIN(month) AS cohort FROM sales
  WHERE customer IS NOT NULL GROUP BY customer
), activity AS (
  SELECT DISTINCT s.customer, f.cohort, s.month,
    (CAST(substr(s.month,1,4) AS INTEGER)-CAST(substr(f.cohort,1,4) AS INTEGER))*12
    +CAST(substr(s.month,6,2) AS INTEGER)-CAST(substr(f.cohort,6,2) AS INTEGER) AS age
  FROM sales s JOIN first_purchase f ON s.customer=f.customer
)
SELECT cohort, age, COUNT(DISTINCT customer) AS active_customers
FROM activity GROUP BY cohort, age ORDER BY cohort, age;