agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Ron256 <ejaluronaldlee@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: generating the average 6 months spend excluding first orders
Date: Wed, 3 Dec 2014 07:42:16 -0700 (MST)
Message-ID: <1417617736671-5829086.post@n5.nabble.com> (raw)
In-Reply-To: <1417031476188-5828414.post@n5.nabble.com>
References: <1417012543637-5828331.post@n5.nabble.com>
<1417023477345-5828381.post@n5.nabble.com>
<CAKFQuwbyE1=iByxRiUkeBZ474_aGEBgjaKZwqQUHLOf91OT2Dg@mail.gmail.com>
<1417024519548-5828385.post@n5.nabble.com>
<CAKFQuwafwdV3uqWgFy-ChXtdCO46WG+96yM2TCf9y4NBZGNOew@mail.gmail.com>
<1417025960949-5828390.post@n5.nabble.com>
<1417026089049-5828392.post@n5.nabble.com>
<1417026875109-5828394.post@n5.nabble.com>
<1417029980347-5828407.post@n5.nabble.com>
<1417031476188-5828414.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (14+ messages)
Message-ID: <1417617736671-5829086.post@n5.nabble.com>
Permalink: ../1417617736671-5829086.post@n5.nabble.com/
Also on: postgresql.org/message-id/1417617736671-5829086.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ejaluronaldlee@gmail.com
Subject: Re: generating the average 6 months spend excluding first orders
In-Reply-To: <1417617736671-5829086.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox