Kemish Dominguez

WITH purchase_dates AS (
    SELECT DISTINCT customer_key, order_date
    FROM sales
),
ranked AS (
    SELECT DISTINCT customer_key, order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_key ORDER BY order_date ASC) AS date_ranks
    FROM purchase_dates
),
first_purch AS (
    SELECT customer_key, order_date AS first_purchase
    FROM ranked
    WHERE date_ranks = 1
),
second_purchase AS (
    SELECT customer_key, order_date AS second_purchase
    FROM ranked
    WHERE date_ranks = 2
),
customer_group AS (
    SELECT fp.customer_key,
        CASE
            WHEN sp.second_purchase IS NOT NULL
             AND DATEDIFF(sp.second_purchase, fp.first_purchase) BETWEEN 1 AND 30
            THEN 'multiple_purch'
            ELSE 'control'
        END AS purchase_group
    FROM first_purch fp
    LEFT JOIN second_purchase sp ON fp.customer_key = sp.customer_key
)
SELECT ROUND(AVG(s.sales_amount)) AS average_sales,
    MONTH(s.order_date) AS month,
    YEAR(s.order_date) AS year,
    purchase_group AS category
FROM sales s
JOIN customer_group cg ON s.customer_key = cg.customer_key
GROUP BY MONTH(s.order_date), YEAR(s.order_date), purchase_group
ORDER BY category DESC, YEAR(s.order_date), MONTH(s.order_date);