Срок до второй покупки по источникам: как считать по покупателям

SQL, по которому посчитано

WITH won AS (
    SELECT customer_id, deal_id, date, source
    FROM deals
    WHERE status = 'Выиграна'
      AND customer_id IS NOT NULL
),
first_on_day AS (
    SELECT customer_id, date, source,
           row_number() OVER (PARTITION BY customer_id, date ORDER BY deal_id) AS day_rn
    FROM won
),
customer_days AS (
    SELECT customer_id, date, source
    FROM first_on_day
    WHERE day_rn = 1
),
ranked AS (
    SELECT customer_id, date, source,
           row_number() OVER (PARTITION BY customer_id ORDER BY date) AS purchase_rn
    FROM customer_days
),
first_deal AS (
    SELECT customer_id, source AS first_source, date AS first_date
    FROM ranked
    WHERE purchase_rn = 1
),
second_deal AS (
    SELECT customer_id, date AS second_date
    FROM ranked
    WHERE purchase_rn = 2
),
pairs AS (
    SELECT f.first_source AS source,
           f.customer_id,
           date_diff('day', f.first_date, s.second_date) AS days_to_second,
           s.second_date
    FROM first_deal f
    JOIN second_deal s USING (customer_id)
)
SELECT source,
       count(*) AS customers,
       round(avg(days_to_second), 1) AS avg_days,
       round(median(days_to_second), 1) AS median_days
FROM pairs
WHERE second_date >= (
    SELECT CAST(date_trunc('month', max(date)) - INTERVAL '11 months' AS DATE)
    FROM deals
)
GROUP BY source
ORDER BY median_days