agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: how to effectively SELECT new "customers"
Date: Thu, 27 Feb 2014 07:09:34 -0800 (PST)
Message-ID: <1393513774376-5793882.post@n5.nabble.com> (raw)
In-Reply-To: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk>
References: <1001132708.567696.1393510818245.JavaMail.root@mobiletech.sk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-effectively-SELECT-new-customers-tp5793867p5793882.ht...
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 (10+ messages) latest in thread
Message-ID: <1393513774376-5793882.post@n5.nabble.com>
Permalink: ../1393513774376-5793882.post@n5.nabble.com/
Also on: postgresql.org/message-id/1393513774376-5793882.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: polobo@yahoo.com
Subject: Re: how to effectively SELECT new "customers"
In-Reply-To: <1393513774376-5793882.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