agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Jan Ostrochovsky <ostrochovsky@mobiletech.sk>
To: David Johnston <polobo@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to effectively SELECT new "customers"
Date: Fri, 28 Feb 2014 12:44:12 +0100 (CET)
Message-ID: <966514640.595308.1393587852428.JavaMail.root@mobiletech.sk> (raw)
In-Reply-To: <1393513774376-5793882.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
> 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.
view thread (10+ messages) latest in thread
Message-ID: <966514640.595308.1393587852428.JavaMail.root@mobiletech.sk>
Permalink: ../966514640.595308.1393587852428.JavaMail.root@mobiletech.sk/
Also on: postgresql.org/message-id/966514640.595308.1393587852428.JavaMail.root@mobiletech.sk
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: ostrochovsky@mobiletech.sk, polobo@yahoo.com
Subject: Re: how to effectively SELECT new "customers"
In-Reply-To: <966514640.595308.1393587852428.JavaMail.root@mobiletech.sk>
* 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