Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

12.3. Grouping over Time

This problem shows up in some form every few years in a newsgroup. You are given a table of some event, say sales, with just the date of the sale and customer columns. The problem is to calculate the average number of days between purchases for each customer. It is a good useful statistic for predicting future behavior and budgeting in a lot of situations. Let’s use a simple table that assumes nobody makes more than one purchase on the same day:

CREATE TABLE Sales
(customer_name CHAR(5) NOT NULL,
sale_date DATE NOT NULL,
PRIMARY KEY (customer name, sale date));

Let’s take a look at the data for the first week in June 2008:

Sales customer_name sale_date ===================================== 'Fred' ' 2008-06-01' 'Mary' ...