Date: Tue, 25 Nov 2014 20:53:33 -0700 (MST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1416974013524-5828256.post@n5.nabble.com>
In-Reply-To: <1416970805198-5828253.post@n5.nabble.com>
References: <1416970805198-5828253.post@n5.nabble.com>
Subject: Re: 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

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql