agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: generating the average 6 months spend excluding first orders
Date: Tue, 25 Nov 2014 20:53:33 -0700 (MST)
Message-ID: <1416974013524-5828256.post@n5.nabble.com> (raw)
In-Reply-To: <1416970805198-5828253.post@n5.nabble.com>
References: <1416970805198-5828253.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (14+ messages) latest in thread
Message-ID: <1416974013524-5828256.post@n5.nabble.com>
Permalink: ../1416974013524-5828256.post@n5.nabble.com/
Also on: postgresql.org/message-id/1416974013524-5828256.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: david.g.johnston@gmail.com
Subject: Re: generating the average 6 months spend excluding first orders
In-Reply-To: <1416974013524-5828256.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