Date: Fri, 28 Feb 2014 12:44:12 +0100 (CET)
From: Jan Ostrochovsky <ostrochovsky@mobiletech.sk>
To: David Johnston <polobo@yahoo.com>
Cc: pgsql-sql@postgresql.org
Message-ID: <966514640.595308.1393587852428.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_595307_1818397471.1393587852427"
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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

> 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.

------=_Part_595307_1818397471.1393587852427
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'><div><blockquote style=3D"border-left:2px solid #1010=
FF;margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-styl=
e:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-s=
ize:12pt;" id=3D"DWT494"><b>From: </b>"David Johnston" &lt;polobo@yahoo.com=
&gt;<br><b>To: </b>pgsql-sql@postgresql.org<br><b>Sent: </b>Thursday, Febru=
ary 27, 2014 4:09:34 PM<br><b>Subject: </b>Re: [SQL] how to effectively SEL=
ECT new "customers"<br><br>Jan Ostrochovsky wrote<br>&gt; Hello, I am solvi=
ng following task and it seems hard to me to find<br>&gt; effective solutio=
n. Maybe somebody knows how to help me: We have table<br>&gt; "purchases" a=
nd each one record is identified by "customer_id". We want to<br>&gt; know =
not only how many different customers did at least one purchase per<br>&gt;=
 time period, grouped by time periods (easy task: "COUNT(DISTINCT<br>&gt; c=
ustomer_id)" with "GROUP BY period"), but also to know how many NEW<br>&gt;=
 customers there were. We define new customer as customer_id, which had<br>=
&gt; first record in table "purchases" after 12 month of inactivity (no rec=
ord<br>&gt; in table "purchases" previous 12 months). I have found one solu=
tion, but<br>&gt; it is very slow and ugly. I tried several other concepts,=
 but without<br>&gt; success. Any hint could be helpful for me. Thanks in a=
dvance! Jano<br><br>Without incorporating additional meta-data about the pu=
rchases onto the<br>customer table the most basic solution would be:<br><br=
>SELECT DISTINCT customer_id FROM products WHERE date &gt; (now() - '12<br>=
months'::interval)<br>EXCEPT<br>SELECT DISTINCT customer_id FROM products W=
HERE date &lt;=3D (now() - '12<br>months'::interval)<br><br>---<br><br>Anot=
her 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 =
query it may be<br>worthwhile to create a materialized view that captures t=
he necessary data<br>and then whenever a new sale is generated you simply u=
pdate that view by<br>changing the attributes of that single customer. &nbs=
p;At any point you can<br>quickly determine, using the view, which customer=
s were active at the time<br>of last purchases and which ones were dormant =
or non-existent.<br><br>David J.<br><br></blockquote><blockquote style=3D"b=
order-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#000;fo=
nt-weight:normal;font-style:normal;text-decoration:none;font-family:Helveti=
ca,Arial,sans-serif;font-size:12pt;"></blockquote></div></div></body></html=
>
------=_Part_595307_1818397471.1393587852427--