Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1p2-0005a6-8c for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:20:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WJ1p1-0003lt-MF for pgsql-sql@arkaria.postgresql.org; Thu, 27 Feb 2014 14:20:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1oz-0003k1-Uy for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:20:22 +0000 Received: from zimbra.isdd.sk ([91.233.248.204]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WJ1ox-0006vy-S5 for pgsql-sql@postgresql.org; Thu, 27 Feb 2014 14:20:21 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra.isdd.sk (Postfix) with ESMTP id 9F8AEAC8767 for ; Thu, 27 Feb 2014 15:20:18 +0100 (CET) X-Virus-Scanned: amavisd-new at zimbra.isdd.sk Received: from zimbra.isdd.sk ([127.0.0.1]) by localhost (zimbra.isdd.sk [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id shEwPyNazhwd for ; Thu, 27 Feb 2014 15:20:18 +0100 (CET) Received: from zimbra.isdd.sk (zimbra.isdd.sk [91.233.248.204]) by zimbra.isdd.sk (Postfix) with ESMTP id 5ECA2AC8756 for ; Thu, 27 Feb 2014 15:20:18 +0100 (CET) Date: Thu, 27 Feb 2014 15:20:18 +0100 (CET) From: Jan Ostrochovsky To: pgsql-sql@postgresql.org Message-ID: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk> In-Reply-To: <883409020.566998.1393509966607.JavaMail.root@mobiletech.sk> Subject: how to effectively SELECT new "customers" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_567695_231456775.1393510818244" X-Originating-IP: [10.8.253.141] X-Mailer: Zimbra 7.2.6_GA_2926 (ZimbraWebClient - GC33 (Win)/7.2.6_GA_2926) X-Pg-Spam-Score: -0.5 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_567695_231456775.1393510818244 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit 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 ------=_Part_567695_231456775.1393510818244 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <= div style=3D'font-family: times new roman,new york,times,serif; font-size: = 12pt; color: #000000'>
Hello,
I am solving following task and=
 it seems hard to me to find effective solution. Maybe somebody knows how t=
o help me:
We have table "purchases" and each one record is ident=
ified by "customer_id".
We want to know not only how many differe=
nt customers did at least one purchase per time period, grouped by time per=
iods
(easy task: "COUNT(DISTINCT customer_id)" with "GROUP BY per=
iod"), but also to know how many NEW customers there were.
We def=
ine new customer as customer_id, which had first record in table "purchases=
" after 12 month of inactivity (no record
in table "purchases" pr=
evious 12 months).
I have found one solution, but it is very slow=
 and ugly. I tried several other concepts, but without success.
A=
ny hint could be helpful for me. Thanks in advance!
Jano
------=_Part_567695_231456775.1393510818244--