Thread: Re: generating the average 6 months spend excluding first orders
Ron256 wrote > Hi all, > > I have to two tasks where I am supposed to generate the average 6 months > spend and average 1 year spend using the customer data but excluding the > first time orders. > > SELECT q.ord_year, avg( item_extended_actual_price_amt ) > [...] > GROUP BY q.ord_year > ORDER BY q.ord_year > ; > > Can someone help me look into my query and see whether I am doing it the > right way before I go a head to do the same for the average 1 year spend? > > Any suggestions are highly appreciated. You do not specify whether you want rolling or calendar periods. The query group by forces calendar year boundaries but I would typically think that TTM (trailing-twelve-months) and TSM values would be more appropriate. If you are going to execute the query often it would likely be worthwhile to identify the entity for "first order" (i.e., buyer) as a separate table and simply store the orderID of their first order in the table. Your query can then simply pull all transactions from the past 6 or 12 months, join against the buyer, and omit any record that matches the first orderid stored on the buyer table. David J. -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828256.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, It is my mistake. It rolling over 12 months or 365 days period. Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828331.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, It is my mistake. It rolling over 12 months or 365 days period. Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828259.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, Please I need your help on getting the first time buyer. I am using the following query but I am getting incorrect results with cte as (select *, row_number() OVER( partition by persistent_key_str order by ord_submitted_date) RN from orders ) select * from cte where rn = 1 When you use this persistent_key_str = '01000217334' I get incorrect results. How can I resolve this? Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828381.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David,
Please I need your help on getting the first time buyer.
I am using the following query but I am getting incorrect results
with cte as
(select *,
row_number() OVER( partition by persistent_key_str order by ord_submitted_date) RN
from orders )
select *
from cte where rn = 1
When you use this persistent_key_str = '01000217334' I get incorrect results.
How can I resolve this?
Unless you tell us what you think the correct result should be it is impossible to know whether it is the result or your expectation that is incorrect.
It would also help to modify your query instead of simply saying "when you use this persistent_key_str = '...'"; show us the query that makes use of that detail.
David J.
View this message in context: Re: generating the average 6 months spend excluding first orders
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, I made a few changes to my query and looks like I am moving in the right direction I have also attached my output. WITH first_cust_cte AS (SELECT min(ord_submitted_date)ord_date , persistent_key_strFROM ordersgroup by persistent_key_str ) SELECT o.persistent_key_str, o.ord_idFROM orders o INNER JOIN first_cust_cte cON o.persistent_key_str = c.persistent_key_strWHEREord_submitted_date = ord_date <http://postgresql.nabble.com/file/n5828385/First_time_orders.png> Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828385.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, I made a few changes to my query and looks like I am moving in the right direction
I have also attached my output.
WITH first_cust_cte AS
(
SELECT min(ord_submitted_date)ord_date
, persistent_key_str
FROM orders
group by persistent_key_str
)
SELECT o.persistent_key_str, o.ord_id
FROM orders o INNER JOIN first_cust_cte c
ON o.persistent_key_str = c.persistent_key_str
WHERE ord_submitted_date = ord_date
Thanks,
Ron
Your query assumes that a person cannot place two orders on the same day - notes rows 3 & 4. If the actual date field had second or smaller precision this will probably be OK...
David J.
View this message in context: Re: generating the average 6 months spend excluding first orders
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Actually 3 and 4 placed orders on the same day. The results I sent your were incorrect. I am still struggling on how to come to the right result set. -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828390.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Actually 3 and 4 placed orders on the same day. The results I sent your were incorrect. I am still struggling on how to come to the right result set. -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828392.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Using the following query, SELECT o.persistent_key_str, o.ord_idFROM orders o WHERE o.ord_submitted_date in ( SELECT min(ord_submitted_date)ord_date FROM ordersgroup by persistent_key_str) I was able to generate the following output: <http://postgresql.nabble.com/file/n5828394/First_time_orders.png> The customer who placed two orders on the same date also appears in the result set. Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828394.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
I have modified the first query to my needs and I believe, it gives the correct results for the first time orders. accept WITH first_cust_cte AS ( SELECT min(ord_submitted_date)ord_date , persistent_key_str FROM orders group by persistent_key_str ) SELECT o.persistent_key_str, o.ord_id FROM orders o INNER JOIN first_cust_cte c ON o.persistent_key_str = c.persistent_key_strAND o.ord_submitted_date = c.ord_date Thanks for your support. Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828414.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
David, I have modified the first query to my needs and I believe, it gives the correct results for the first time orders. accept WITH first_cust_cte AS ( SELECT min(ord_submitted_date)ord_date , persistent_key_str FROM orders group by persistent_key_str ) SELECT o.persistent_key_str, o.ord_id FROM orders o INNER JOIN first_cust_cte c ON o.persistent_key_str = c.persistent_key_strAND o.ord_submitted_date = c.ord_date Thanks for your support. Thanks, Ron -- View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p5828407.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
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.