I have modified my query but I am really wondering why I am getting incorrect
results.
Please see the following link. http://sqlfiddle.com/#!15/5897e/4.
I am getting the same values for both Average 1 year spend and Average 6
months spend which might not be right.
Explaining further, the CTE in the demo generates the first time orders of a
customer which I exclude in the join when calculating the the Average Six
months spend per year.
WITH first_cust_cte AS ( SELECT o_1.persistent_key_str, min(o_1.ord_submitted_date) AS ord_date
FROM orders o_1 GROUP BY o_1.persistent_key_str ),
first_time_customer_orders_to_be_excluded_cte as
(SELECT o.persistent_key_str, o.ord_id FROM orders o JOIN first_cust_cte c ON o.persistent_key_str =
c.persistent_key_str AND o.ord_submitted_date = c.ord_date
)
-- 1 row per year
SELECT EXTRACT(YEAR FROM ord_submitted_date) AS ordered , AVG(o.item_extended_actual_price_amt)::numeric(18,2)
"Avg_6_months_spend"
FROM (SELECT generate_series(min(ord_submitted_date) -- single query ... ,
max(ord_submitted_date) -- ... to get min /
max , '1d')::date FROM orders) g
(ord_submitted_date)
LEFT join orders o USING (ord_submitted_date)
LEFT JOIN first_time_customer_orders_to_be_excluded_cte c
USING(persistent_key_str)
WHERE o.ord_submitted_date >= g.ord_submitted_date - interval '6 MONTHS'
AND ord_submitted_date <= g.ord_submitted_date + interval '6 MONTHS'
AND c.ord_id <> o.ord_id
GROUP BY 1
ORDER BY 1
Can someone help me out? I know someone out there has a solution.
--
View this message in context:
http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5829086.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.