agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedgenerating the average 6 months spend excluding first orders
14+ messages / 2 participants
[nested] [flat]
* generating the average 6 months spend excluding first orders
@ 2014-11-26 03:00 Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 03:00 UTC (permalink / raw)
To: pgsql-sql
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.
I have some sample data below:
CREATE TABLE orders
(
persistent_key_str character varying,
ord_id character varying(50),
ord_submitted_date date,
item_sku_id character varying(50),
item_extended_actual_price_amt numeric(18,2)
);
INSERT INTO orders VALUES
('01120736182','ORD6266073','2010-12-08','100856-01',39.90);
INSERT INTO orders
VALUES('01120736182','ORD33997609','2011-11-23','100265-01',49.99);
INSERT INTO orders
VALUES('01120736182','ORD33997609','2011-11-23','200020-01',29.99);
INSERT INTO orders
VALUES('01120736182','ORD33997609','2011-11-23','100817-01',44.99);
INSERT INTO orders
VALUES('01120736182','ORD89267964','2012-12-05','200251-01',79.99);
INSERT INTO orders
VALUES('01120736182','ORD89267964','2012-12-05','200269-01',59.99);
INSERT INTO orders
VALUES('01011679971','ORD89332495','2012-12-05','200102-01',169.99);
INSERT INTO orders
VALUES('01120736182','ORD89267964','2012-12-05','100907-01',89.99);
INSERT INTO orders
VALUES('01120736182','ORD89267964','2012-12-05','200840-01',129.99);
INSERT INTO orders
VALUES('01120736182','ORD125155068','2013-07-27','201443-01',199.99);
INSERT INTO orders
VALUES('01120736182','ORD167230815','2014-06-05','200141-01',59.99);
INSERT INTO orders
VALUES('01011679971','ORD174927624','2014-08-16','201395-01',89.99);
INSERT into orders
values('01000217334','ORD92524479','2012-12-20','200021-01',29.99);
INSERT into orders
values('01000217334','ORD95698491','2013-01-08','200021-01',19.99);
INSERT into orders
values('01000217334','ORD90683621','2012-12-12','200021-01',29.990);
INSERT into orders
values('01000217334','ORD92524479','2012-12-20','200560-01',29.99);
INSERT into orders
values('01000217334','ORD145035525','2013-12-09','200972-01',49.99);
INSERT into orders
values('01000217334','ORD145035525','2013-12-09','100436-01',39.99);
INSERT into orders
values('01000217334','ORD90683374','2012-12-12','200284-01',39.99);
INSERT into orders
values('01000217334','ORD139437285','2013-11-07','201794-01',134.99);
INSERT into orders values('01000827006','W02238550001','2010-06-11','HL
101077',349.000);
INSERT into orders values('01000827006','W01738200001','2009-12-10','EL
100310 BLK',119.96);
INSERT into orders values('01000954259','P00444170001','2009-12-03','PC
100455 BRN',389.99);
INSERT into orders values('01002319116','W02242430001','2010-06-12','TR
100966',35.99);
INSERT into orders values('01002319116','W02242430002','2010-06-12','EL
100985',99.99);
INSERT into orders values('01002319116','P00532470001','2010-05-04','HO
100482',49.99);
Using the data, this is what I have done:
SELECT q.ord_year, avg( item_extended_actual_price_amt )
FROM (
SELECT EXTRACT(YEAR FROM ord_submitted_date) as ord_year,
persistent_key_str,
min(ord_submitted_date) as first_order_date
FROM ORDERS
GROUP BY ord_year, persistent_key_str
) q
JOIN ORDERS o
ON q.persistent_key_str = o.persistent_key_str and
q.ord_year = EXTRACT (year from o.ord_submitted_date) and
o.ord_submitted_date > q.first_order_date AND o.ord_submitted_date <
q.first_order_date + INTERVAL ' 6 months'
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.
Thanks,
Ron
--
View this message in context: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253....
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 03:53 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G Johnston @ 2014-11-26 03:53 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
@ 2014-11-26 04:56 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 04:56 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 14:35 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 14:35 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 17:37 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 17:37 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 17:50 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G Johnston @ 2014-11-26 17:50 UTC (permalink / raw)
To: pgsql-sql
On Wed, Nov 26, 2014 at 10:37 AM, Ron256 [via PostgreSQL] <
ml-node+s1045698n5828381h35@n5.nabble.com> wrote:
> 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: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
@ 2014-11-26 17:55 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 17:55 UTC (permalink / raw)
To: pgsql-sql
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
<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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 18:04 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G Johnston @ 2014-11-26 18:04 UTC (permalink / raw)
To: pgsql-sql
On Wed, Nov 26, 2014 at 10:55 AM, Ron256 [via PostgreSQL] <
ml-node+s1045698n5828385h59@n5.nabble.com> wrote:
>
> 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[image: My output]
>
> 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: http://postgresql.nabble.com/generating-the-average-6-months-spend-excluding-first-orders-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
@ 2014-11-26 18:19 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 18:19 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 18:21 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:34 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 18:21 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 18:34 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:26 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 18:34 UTC (permalink / raw)
To: pgsql-sql
Using the following query,
SELECT o.persistent_key_str, o.ord_id
FROM orders o
WHERE o.ord_submitted_date in (
SELECT min(ord_submitted_date)ord_date
FROM orders
group 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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:34 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 19:26 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:51 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 19:26 UTC (permalink / raw)
To: pgsql-sql
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_str AND 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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:34 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:26 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-11-26 19:51 ` Ron256 <ejaluronaldlee@gmail.com>
2014-12-03 14:42 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Ron256 @ 2014-11-26 19:51 UTC (permalink / raw)
To: pgsql-sql
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_str AND 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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: generating the average 6 months spend excluding first orders
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` Re: generating the average 6 months spend excluding first orders David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:34 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:26 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:51 ` Re: generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
@ 2014-12-03 14:42 ` Ron256 <ejaluronaldlee@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Ron256 @ 2014-12-03 14:42 UTC (permalink / raw)
To: pgsql-sql
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-tp5828253p...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2014-12-03 14:42 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-26 03:00 generating the average 6 months spend excluding first orders Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 03:53 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 04:56 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 14:35 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:37 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 17:50 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 17:55 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:04 ` David G Johnston <david.g.johnston@gmail.com>
2014-11-26 18:19 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:21 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 18:34 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:26 ` Ron256 <ejaluronaldlee@gmail.com>
2014-11-26 19:51 ` Ron256 <ejaluronaldlee@gmail.com>
2014-12-03 14:42 ` Ron256 <ejaluronaldlee@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox