Date: Fri, 28 Feb 2014 12:45:26 +0100 (CET)
From: Jan Ostrochovsky <ostrochovsky@mobiletech.sk>
To: David Johnston <polobo@yahoo.com>
Cc: pgsql-sql@postgresql.org
Message-ID: <976203811.595346.1393587926835.JavaMail.root@mobiletech.sk>
In-Reply-To: <1393513774376-5793882.post@n5.nabble.com>
Subject: Re: how to effectively SELECT new "customers"
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="----=_Part_595345_143802898.1393587926834"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

------=_Part_595345_143802898.1393587926834
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

----- Original Message -----

> From: "David Johnston" <polobo@yahoo.com>
> To: pgsql-sql@postgresql.org
> Sent: Thursday, February 27, 2014 4:09:34 PM
> Subject: Re: [SQL] how to effectively SELECT new "customers"

> Jan Ostrochovsky wrote
> > Hello, I am solving following task and it seems hard to me to find
> > effective solution. Maybe somebody knows how to help me: We have
> > table
> > "purchases" and each one record is identified by "customer_id". We
> > want to
> > know not only how many different customers did at least one
> > purchase per
> > time period, grouped by time periods (easy task: "COUNT(DISTINCT
> > customer_id)" with "GROUP BY period"), but also to know how many
> > NEW
> > customers there were. We define new customer as customer_id, which
> > had
> > first record in table "purchases" after 12 month of inactivity (no
> > record
> > in table "purchases" previous 12 months). I have found one
> > solution, but
> > it is very slow and ugly. I tried several other concepts, but
> > without
> > success. Any hint could be helpful for me. Thanks in advance! Jano

> Without incorporating additional meta-data about the purchases onto
> the
> customer table the most basic solution would be:

> SELECT DISTINCT customer_id FROM products WHERE date > (now() - '12
> months'::interval)
> EXCEPT
> SELECT DISTINCT customer_id FROM products WHERE date <= (now() - '12
> months'::interval)

> ---

> Another solution:
> WHERE ... >12 AND NOT EXISTS (SELECT ... WHERE <= 12)

> ---

> Depending on the frequency that you need to run this query it may be
> worthwhile to create a materialized view that captures the necessary
> data
> and then whenever a new sale is generated you simply update that view
> by
> changing the attributes of that single customer. At any point you can
> quickly determine, using the view, which customers were active at the
> time
> of last purchases and which ones were dormant or non-existent.

> David J.

Thank you David, your alternatives seem as smart alternatives of my solution, what is based on the difference between COUNT(DISTINCT customer_id) in e.g. 13 months (in case when reporting period is 1 month over previous 12 months) and COUNT(DISTINCT customer_id) in 12 months prior to reporting period (but without the reporting period). But I would also like to incorporate other data in the output, which should seem e.g.: 

town, period, payment_channel_id, purchases_count, purchases_total_revenue, distinct_customer_count, distinct_NEW_customer_count 
BA, 201401, A, 495, 847, 103, 25 
BA, 201401, B, 345, 456, 99, 21 
BA, 201312, A, 554, 1021, 105, 30 
ZA, 201401, A, 323, 987, 95, 23 
... 

there is 12-month-long time-window for new customer determination, sliding according to particular period, back to the past 

how to achieve this? e.g. by running your or my concept repeatedly (manually or in the higher level loop), but this is too slow... or to find more sophisticated SQL query to accomplish this in one step... any other ideas for such SQL query running once, not repeatedly (in loop) for each reporting period? (I was considering window functions, connectby, grouping sets, ..., but no success yet) 

materialized view could be part of that solution, when we will have the right query and uprgade from 9.0 to 9.3 (as I see it: http://www.postgresql.org/docs/9.3/static/rules-materializedviews.html)... but it should be scalable to several to tens purchases every minute 

thanks again for your contribution, David 

Jano 

------=_Part_595345_143802898.1393587926834
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: times new roman,new york,times,serif; font-size: =
12pt; color: #000000'><hr id=3D"zwchr"><blockquote style=3D"border-left:2px=
 solid #1010FF;margin-left:5px;padding-left:5px;color:#000;font-weight:norm=
al;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-=
serif;font-size:12pt;"><b>From: </b>"David Johnston" &lt;polobo@yahoo.com&g=
t;<br><b>To: </b>pgsql-sql@postgresql.org<br><b>Sent: </b>Thursday, Februar=
y 27, 2014 4:09:34 PM<br><b>Subject: </b>Re: [SQL] how to effectively SELEC=
T new "customers"<br><br>Jan Ostrochovsky wrote<br>&gt; Hello, I am solving=
 following task and it seems hard to me to find<br>&gt; effective solution.=
 Maybe somebody knows how to help me: We have table<br>&gt; "purchases" and=
 each one record is identified by "customer_id". We want to<br>&gt; know no=
t only how many different customers did at least one purchase per<br>&gt; t=
ime period, grouped by time periods (easy task: "COUNT(DISTINCT<br>&gt; cus=
tomer_id)" with "GROUP BY period"), but also to know how many NEW<br>&gt; c=
ustomers there were. We define new customer as customer_id, which had<br>&g=
t; first record in table "purchases" after 12 month of inactivity (no recor=
d<br>&gt; in table "purchases" previous 12 months). I have found one soluti=
on, but<br>&gt; it is very slow and ugly. I tried several other concepts, b=
ut without<br>&gt; success. Any hint could be helpful for me. Thanks in adv=
ance! Jano<br><br>Without incorporating additional meta-data about the purc=
hases onto the<br>customer table the most basic solution would be:<br><br>S=
ELECT DISTINCT customer_id FROM products WHERE date &gt; (now() - '12<br>mo=
nths'::interval)<br>EXCEPT<br>SELECT DISTINCT customer_id FROM products WHE=
RE date &lt;=3D (now() - '12<br>months'::interval)<br><br>---<br><br>Anothe=
r solution:<br>WHERE ... &gt;12 AND NOT EXISTS (SELECT ... WHERE &lt;=3D 12=
)<br><br>---<br><br>Depending on the frequency that you need to run this qu=
ery it may be<br>worthwhile to create a materialized view that captures the=
 necessary data<br>and then whenever a new sale is generated you simply upd=
ate that view by<br>changing the attributes of that single customer. &nbsp;=
At any point you can<br>quickly determine, using the view, which customers =
were active at the time<br>of last purchases and which ones were dormant or=
 non-existent.<br><br>David J.<br></blockquote>Thank you David, your altern=
atives seem as smart alternatives of my solution, what is based on the diff=
erence between COUNT(DISTINCT customer_id) in e.g. 13 months (in case when =
reporting period is 1 month over previous 12 months) and COUNT(DISTINCT cus=
tomer_id) in 12 months prior to reporting period (but without the reporting=
 period). But I would also like to incorporate other data in the output, wh=
ich should seem e.g.:<div><br></div><div>town, period, payment_channel_id, =
purchases_count, purchases_total_revenue, distinct_customer_count, distinct=
_NEW_customer_count</div><div>BA, 201401, A, 495, 847, 103, 25</div><div>BA=
, 201401, B, 345, 456, 99, 21</div><div>BA, 201312, A, 554, 1021, 105, 30</=
div><div>ZA, 201401, A, 323, 987, 95, 23<br>...</div><div><br>there is 12-m=
onth-long time-window for new customer determination, sliding according to =
particular period, back to the past</div><div><br></div><div>how to achieve=
 this? e.g. by running your or my concept repeatedly (manually or in the hi=
gher level loop), but this is too slow... or to find more sophisticated SQL=
 query to accomplish this in one step... any other ideas for such SQL query=
 running once, not repeatedly (in loop) for each reporting period? (I was c=
onsidering window functions, connectby, grouping sets, ..., but no success =
yet)</div><div><br></div><div>materialized view could be part of that solut=
ion, when we will have the right query and uprgade from 9.0 to 9.3 (as I se=
e it:&nbsp;http://www.postgresql.org/docs/9.3/static/rules-materializedview=
s.html)... but it should be scalable to several to tens purchases every min=
ute</div><div><br></div><div>thanks again for your contribution, David</div=
><div><br></div><div>Jano<br></div><div><br></div></div></body></html>
------=_Part_595345_143802898.1393587926834--