generating the average 6 months spend excluding first orders
От | Ron256 |
---|---|
Тема | generating the average 6 months spend excluding first orders |
Дата | |
Msg-id | 1416970805198-5828253.post@n5.nabble.com обсуждение исходный текст |
Список | 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.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
В списке pgsql-sql по дате отправления:
Предыдущее
От: Ron256Дата:
Сообщение: Re: generating the average 6 months spend excluding first orders
Следующее
От: Ron256Дата:
Сообщение: Re: generating the average 6 months spend excluding first orders