2

I am stuck on implementing a left join on dates for each individual customer.

I have a table which stores customer purchases:

SELECT purchase_date, customer
FROM purchases

I would like to create a similar table however one that includes ALL dates and not just purchase dates, for example:

SELECT date, customer, purchase_made

Where purchase_made is a BOOLEAN column. I understand that I need to use a LEFT JOIN however I am stuck at making sure that EACH customer has a full set of dates. For example if no customers made a purchase on 2016-01-01, and there are 2000 customers in the database, then 2000 rows of 2016-01-01 should be added (with each row attributed to a customer).

So far I've tried:

WITH dates AS (
    SELECT GENERATE_SERIES('2016-01-01'::TIMESTAMP, '2016-01-15'::TIMESTAMP, '1 day'::INTERVAL) AS date
    ),

SELECT date, customer 
FROM purchases 
LEFT JOIN dates ON purchases.date=dates.date;

How to implement the BOOLEAN column?

Also is it possible to add the date rows only AFTER a customers first purchase? So for example only insert 2016-01-01 for customers who made a purchase prior to 2016-01-01.

  • By far the most efficient way of doing this would be to create a date table which contains a list of all available dates and then join onto this. The advantage is that you can set it how you want and you won't have to generate your dates list every time you run the calculation. You can then use this as your base table and left join the other data to it. That way, if you have a date with no data you'll still return it in your result set. – Rich Benner Jul 6 '16 at 7:11
  • Using Date Value as foreign key is not exactly the best way of joining tables. Are there any other keys that are available? An integer value would be the best for used to perform this action? – Yevgraf Andreyevich Zhivago Jul 6 '16 at 7:23
  • @RichBenner The complexity is around joining the two tables such that the dates are joined for every name. For example, today's date should be attributed to every customer (even if they did not make a purchase). The boolean purchase_made column is going to be used to identify if a purchase has been made. – Greg Jul 6 '16 at 7:37
  • @Jeffrey There is nothing else I can join it on as the dates are really all that there is. So for example if a customer purchased something on 2016-01-05, only that observation will be in the purchases table. As such I generate a date series in a range and join the purchases onto that. – Greg Jul 6 '16 at 7:38
  • @Dave it was a bit much for a comment so I've just provided an example of my solution as an answer. It's not the only way to get it done but it's the way that I'd do it when considering efficiency. – Rich Benner Jul 6 '16 at 7:52
1

To follow on from my comment. I'd recommend a date table.

Sample Customer Purchase Data

IF OBJECT_ID('tempdb..#CustomerPurchases') IS NOT NULL DROP TABLE #CustomerPurchases
GO
CREATE TABLE #CustomerPurchases (PurchaseDate date, CustomerName varchar(20))
INSERT INTO #CustomerPurchases (PurchaseDate, CustomerName)
VALUES
 ('2016-07-04','Jon Snow')
,('2016-07-06','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-07','Jon Snow')
,('2016-07-05','Daenerys Targaryen')
,('2016-07-06','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-09','Daenerys Targaryen')
,('2016-07-10','Daenerys Targaryen')

Sample Date Table

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL DROP TABLE #DateTable
GO
CREATE TABLE #DateTable (DateList date)
INSERT INTO #DateTable (DateList)
VALUES
 ('2016-07-04')
,('2016-07-05')
,('2016-07-06')
,('2016-07-07')
,('2016-07-08')
,('2016-07-09')
,('2016-07-10')

You could cross join the list of dates to then get a full list of all customers and all dates. Then join to the actual sales data to return the Boolean value you're after.

SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT 
                CustomerName 
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT 
                CustomerName
                ,PurchaseDate 
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName

Results would look like this

DateList    CustomerName        PurchaseMade
2016-07-04  Daenerys Targaryen  0
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-09  Daenerys Targaryen  1
2016-07-10  Daenerys Targaryen  1
2016-07-04  Jon Snow            1
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0
2016-07-09  Jon Snow            0
2016-07-10  Jon Snow            0

If you were to do this then your date table would obviously be much wider than the week example I've done. You could then just use date parameters to restrict to the date range you're after. Something like this;

DECLARE @StartDate date; SET @StartDate = '2016-07-05'
DECLARE @EndDate date; SET @EndDate = '2016-07-08'
SELECT
dt.DateList
,cu.CustomerName
,(CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade
FROM #DateTable dt
CROSS JOIN  (
                SELECT DISTINCT 
                CustomerName 
                FROM #CustomerPurchases
            ) cu
LEFT JOIN   (
                SELECT DISTINCT 
                CustomerName
                ,PurchaseDate 
                FROM #CustomerPurchases
            ) cp
    ON dt.DateList = cp.PurchaseDate
    AND cu.CustomerName = cp.CustomerName
WHERE dt.DateList BETWEEN @StartDate AND @EndDate
ORDER BY CustomerName, DateList

Which would give these results

DateList    CustomerName        PurchaseMade
2016-07-05  Daenerys Targaryen  1
2016-07-06  Daenerys Targaryen  1
2016-07-07  Daenerys Targaryen  0
2016-07-08  Daenerys Targaryen  0
2016-07-05  Jon Snow            0
2016-07-06  Jon Snow            1
2016-07-07  Jon Snow            1
2016-07-08  Jon Snow            0
  • Yeah no worries, it's probably the cross join that's the bit you were looking for but I got a bit carried away on my example :) – Rich Benner Jul 6 '16 at 8:04
  • The question is tagged with postgresql not sql-server, so several improvements coul dbe made. You could use generate_series(), as in the question. For creating just 365-366 rows, it's as efficient as a Calendar table. And Postgres has boolean type so the (CASE WHEN cp.PurchaseDate IS NULL THEN 0 ELSE 1 END) PurchaseMade can be simplified to (cp.PurchaseDate IS NOT NULL) AS PurchaseMade – ypercubeᵀᴹ Jul 6 '16 at 8:36
  • And the SELECT DISTINCT inside the cp table can be simplified to SELECT TOP (1) or SELECT ... LIMIT 1 for Postgres. – ypercubeᵀᴹ Jul 6 '16 at 8:40
  • @ypercubeᵀᴹ TOP (1) did not work in Postgres however LIMIT 1 produced the same results and a significant speed up :) – Greg Jul 6 '16 at 13:13
  • Yeah, the TOP (1) is SQL Server syntax. – ypercubeᵀᴹ Jul 6 '16 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.