WITH recent_orders AS (
SELECT
o.order_id,
o.customer_id,
o.total_amount,
o.order_date
FROM
Orders o
WHERE
o.order_date >= current_date - INTERVAL '6 months'
),
high_value_orders AS (
SELECT
ro.order_id,
ro.customer_id,
ro.total_amount,
ro.order_date
FROM
recent_orders ro
WHERE
ro.total_amount > 500
),
customer_order_summary AS (
SELECT
hvo.customer_id,
SUM(hvo.total_amount) AS total_spent,
AVG(hvo.total_amount) AS avg_order_value,
COUNT(hvo.order_id) AS total_orders,
ROW_NUMBER() OVER (ORDER BY SUM(hvo.total_amount) DESC) AS rank_by_spent,
AVG(SUM(hvo.total_amount)) OVER (PARTITION BY hvo.customer_id ORDER BY MIN(hvo.order_date)) AS running_avg_order_value,
MAX(hvo.total_amount) OVER (PARTITION BY hvo.customer_id) AS max_order_value,
SUM(SUM(hvo.total_amount)) OVER (PARTITION BY hvo.customer_id ORDER BY MIN(hvo.order_date)) AS cumulative_spent
FROM
high_value_orders hvo
GROUP BY
hvo.customer_id
),
product_summary AS (
SELECT
oi.product_id,
hvo.customer_id,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.price) AS total_revenue
FROM
Order_Items oi
RIGHT JOIN high_value_orders hvo ON oi.order_id = hvo.order_id
GROUP BY
oi.product_id, hvo.customer_id
)
SELECT
c.customer_name,
cos.total_spent,
cos.avg_order_value,
cos.total_orders,
cos.rank_by_spent,
cos.running_avg_order_value,
cos.max_order_value,
cos.cumulative_spent,
ps.product_id,
ps.total_quantity,
ps.total_revenue
FROM
Customers c
LEFT JOIN customer_order_summary cos ON c.customer_id = cos.customer_id
LEFT JOIN product_summary ps ON c.customer_id = ps.customer_id
ORDER BY
c.customer_name, ps.product_id;
@it_underside