CODEWITHSUNDEEP
sql-server
user3396213
user3396213

Reputation: 25

Rolling Prior13 months with Current Month Sales

Within a SQL Server 2012 database, I have a table with two columns customerid and date. I am interested in getting by year-month, a count of customers that have purchased in current month but not in prior 13 months. The table is extremely large so something efficient would be highly appreciated. Results table is shown after the input data. In essence, it is a count of customers that purchased in current month but not in prior 13 months (by year and month).

---input table-----

declare @Sales as Table ( customerid Int, date Date );
insert into @Sales ( customerid, date) values
  ( 1, '01/01/2012' ),  
  ( 1, '04/01/2013' ),  
  ( 1, '01/01/2014' ),  
  ( 1, '01/01/2014' ),  
  ( 1, '04/06/2014' ),  
  ( 2, '04/01/2014' ),  
  ( 3, '01/03/2012' ),  
  ( 3, '01/03/2014' ),  
  ( 4, '01/04/2012' ),  
  ( 4, '04/04/2013' ),  
  ( 5, '02/01/2010' ),  
  ( 5, '02/01/2013' ),  
  ( 5, '04/01/2014' )      
    select  customerid, date
      from @Sales;

---desired results ----

yearmth monthpurchasers monthpurchasernot13m
201002 1 1
201201 3 3
201302 1 1
201304 2 2
201401 2 1
201404 3 2
Thanks very much for looking at this!

Dev

Upvotes: 2

Views: 648

Answers (3)

dean
dean

Reputation: 10098

You didn't provide the expected result, but I believe this is pretty close (at least logically):

;with g as (
    select customerid, year(date)*100 + month(date) as mon
    from @Sales 
    group by customerid, year(date)*100 + month(date) 
),
x as (
    select *,
        count(*) over(partition by customerid order by mon
            rows between 13 preceding and 1 preceding) as cnt
    from g
),
y as (
    select mon, count(*) as cnt from x
    where cnt = 0
    group by mon
)
select g.mon,
    count(distinct(g.customerid)) as monthpurchasers,
    isnull(y.cnt, 0) as cnt
from g
left join y on g.mon = y.mon
group by g.mon, y.cnt
order by g.mon

Upvotes: 1

poaca
poaca

Reputation: 76

Below query will produce what you are looking for. I am not sure how performance will be on a big table (how big is your table?) but it is pretty straight forward so I think it will be ok. I simply calculate the 13 months earlier on CTE to find my sale window. Than join to the Sales table within that window / customer id and grouping records based on the unmatched records. You don't actually need 2 CTE's here you can do the DATEADD(mm,-13,date) on the join part of the second CTE but I thought it might be more clear this way.

P.S. If you need to change the time frame from 13 months to something else all you have to change is the DATEADD(mm,-13,date) this simply substracts 13 months from the date value.

Hope this helps or at least leads to a better solution

;WITH PurchaseWindow AS (
select  customerid, date, DATEADD(mm,-13,date) minsaledate
FROM @Sales
), JoinBySaleWindow AS (
SELECT a.customerid, a.date,a.minsaledate,b.date earliersaledate
FROM PurchaseWindow a
    LEFT JOIN @sales b ON a.customerid =b.customerid 
        --Find the sales for the customer within the last 13 months of original sale
        AND b.date BETWEEN a.date AND a.minsaledate
)
SELECT DATEPART(yy,date) AS [year], DATEPART(mm, date) AS [month], COUNT(DISTINCT customerid) monthpurchases
FROM JoinBySaleWindow
--Exclude records where a sale within last 13 months occured
WHERE earliersaledate IS NULL
GROUP BY DATEPART(mm, date), DATEPART(yy,date)

Sorry about the typos they are fixed now.

Upvotes: 0

Erran Morad
Erran Morad

Reputation: 4743

Tell me if this query helps. It extracts all the rows which meet your condition into a Table variable. Then, I use your query and join to this table.

declare @startDate datetime
declare @todayDate datetime
declare @tbl_Custs as Table(customerid int)
set @startDate = '04/01/2014' -- mm/dd/yyyy
set @todayDate = GETDATE()
insert into @tbl_Custs
-- purchased only this month
select customerid
from Sales
where ([date] >= @startDate and [date] <= @todayDate)
and customerid NOT in 
(
-- purchased in past 13 months 
select distinct customerid 
from Sales 
where ([date] >= DATEADD(MONTH,-13,[date]) 
and [date] < @startDate)
) 
-- your query goes here
select year(date) as year
    ,month(date) as month
    ,count(distinct(c.customerid)) as monthpurchasers
from @tbl_Custs as c right join
Sales as s
on c.customerid = s.customerid
group by year(date) , month(date) 
order by year(date) , month(date)

Upvotes: 0

Related Questions