Date: Tue, 25 Nov 2014 20:00:05 -0700 (MST)
From: Ron256 <ejaluronaldlee@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1416970805198-5828253.post@n5.nabble.com>
Subject: generating the average 6 months spend excluding first orders
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.html
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