SQL First In First Out Loyalty Point

Question

fellow developers and analysts. I have some experience in SQL and have resorted to similar posts. However, this is slightly more niche. Thank you in advance for helping.

I have the below dataset (edited. Apology)

Setup

CREATE TABLE CustomerPoints
(
    CustomerID INT,
    [Date]     Date,
    Points     INT
)

INSERT INTO CustomerPoints
VALUES
    (1, '20150101', 500),
    (1, '20150201', -400),
    (1, '20151101', 300),
    (1, '20151201', -400)

and need to turn it into (edited. The figures in previous table were incorrect)

Any positive amount of points are points earned whereas negative are redeemed. Because of the FIFO (1st in 1st out concept), of the second batch of points spent (-400), 100 of those were taken from points earned on 20150101 (UK format) and 300 from 20151101.

The goal is to calculate, for each customer, the number of points spent within x and y months of earning. Again, thank you for your help.

Answer 1

I have already answered a similar question here and here

You need to explode points earned and redeemed by single units and then couple them, so each point earned will be matched by a redeemed point.

For each of these matching rows calculate the months elapsed from the earning to the redeeming and then aggregate it all.

For FN_NUMBERS(n) it is a tally table, look at other answers I have linked above.

;with
p as (select * from CustomerPoints),
e as (select * from p where points>0),
r as (select * from p where points<0),
ex as (
    select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn
    from e
    join FN_NUMBERS(1000) on N<= e.points
),
rx as (
    select *, ROW_NUMBER() over (partition by CustomerID order by [date] ) rn
    from r
    join FN_NUMBERS(1000) on N<= -r.points
),
j as (
select ex.CustomerID, DATEDIFF(month,ex.date, rx.date) mm
from ex
join rx on ex.CustomerID = rx.CustomerID and ex.rn = rx.rn and rx.date>ex.date
)
-- use this select to see points redeemed in current and past semester
select * from j  join (select 0 s union all select 1 s ) p on j.mm >= (p.s*6)+(p.s) and j.mm < p.s*6+6 pivot (count(mm) for s in ([0],[2])) p order by 1, 2

-- use this select to see points redeemed with months detail
--select * from j pivot (count(mm) for mm in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) p order by 1

-- use this select to see points redeemed in rows per month
--select CustomerID, mm, COUNT(mm) PointsRedeemed   from j  group by CustomerID, mm order by 1

output of default query, 0 is 0-6 months, 1 is 7-12 (age of redemption in months)

CustomerID  0   1
1           700 100

output of 2nd query, 0..12 is the age of redemption in months

CustomerID  0   1   2   3   4   5   6   7   8   9   10  11  12
1           0   700 0   0   0   0   0   0   0   0   0   100 0

output from 3rd query, is the age of redemption in months

CustomerID  mm  PointsRedeemed
1           1   700
1           11  100

bye