The 'Subscription List' SQL Problem

Here's my answer....

WITH MonthlySummaries([Date], joined, [left])--a table collected from two aggregations, giving us
      AS (SELECT    joined.[date],--those who joined and those who left in each month
                    joined.joined,
                    COALESCE([left].[left], 0)
          FROM      (SELECT CAST('01 '+LEFT(DATENAME(month, DateJoined), 3)
                            +' '+DATENAME(year, DateJoined) AS DATETIME) AS [Date],
                            COUNT(*) AS [joined]
                     FROM   Registrations
                     GROUP BY CAST('01 '+LEFT(DATENAME(month, DateJoined),
                                              3)+' '+DATENAME(year, DateJoined) AS DATETIME)
                    ) joined
                    LEFT OUTER JOIN (SELECT COUNT(*) AS [left],
                                            [date] = CAST('01 '
                                            +LEFT(DATENAME(month, Dateleft),
                                                  3)+' '+DATENAME(year, Dateleft) AS DATETIME)
                                     FROM   Registrations
                                     GROUP BY CAST('01 '
                                            +LEFT(DATENAME(month, Dateleft),
                                                  3)+' '+DATENAME(year, Dateleft) AS DATETIME)
                                    ) [left] ON joined.date=[left].date
         )
SELECT  MonthlySummaries.date,
        [joined],
        [left],
        c.subscribers
FROM    MonthlySummaries
        INNER JOIN (SELECT  a.date,
                            SUM(b.joined)-SUM(b.[left]) AS subscribers
                    FROM    MonthlySummaries a
                            INNER JOIN Monthlysummaries b ON b.date<=a.date
                    GROUP BY a.date
                   ) c ON c.date=MonthlySummaries.date
ORDER BY monthlySummaries.date

Here is my attempt... Bearing in mind that I have only had 1 coffee so far today, this may yet get revised:

-- Matt 1
DECLARE @TempData TABLE (Joined [smallint], Unsubscribed [smallint], Num int)
INSERT INTO @TempData (Joined, Unsubscribed, Num)
SELECT Joined, Unsubscribed, COUNT(*) FROM
 (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined,
         DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed
    FROM registrations) idat
GROUP BY [Joined], [Unsubscribed]

CREATE TABLE #TempMonths (MonthNumber [smallint], Joined [int], Unsubscribed [int], JoinedToDate [int], UnsubscribedToDate [int])
CREATE CLUSTERED INDEX [IX_TempMonths_1] ON #TempMonths (MonthNumber)
INSERT INTO #TempMonths (MonthNumber, Joined)
SELECT Joined, SUM(Num) FROM @TempData
GROUP BY Joined

UPDATE #TempMonths SET Unsubscribed = idat.UnsubscribedCount
FROM #TempMonths tm INNER JOIN
     (SELECT Unsubscribed AS UnsubscribedMonthNumber, SUM(Num) AS UnsubscribedCount FROM @TempData GROUP BY Unsubscribed) idat
  ON [idat].[UnsubscribedMonthNumber] = tm.MonthNumber

DECLARE @PrevJoined [int], @PrevUnsubscribed [int]
SET @PrevJoined = 0
SET @PrevUnsubscribed = 0

UPDATE #TempMonths
   SET @PrevJoined = JoinedToDate = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
  FROM #TempMonths WITH(INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, 
       Joined AS JoinedThisMonth, 
       ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, 
       JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers 
  FROM #TempMonths 
 ORDER BY MonthDate

Here's version 2 (+2 cups of coffee)

-- Matt 2
CREATE TABLE #TempData (
  Joined       SMALLINT,
  Unsubscribed SMALLINT,
  Num          INT     
);

INSERT INTO #TempData (Joined, Unsubscribed, Num)
SELECT   Joined, Unsubscribed, SUM(Num)
FROM     (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined, DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed, [Num]
          FROM   (SELECT   [DateJoined], [DateLeft], COUNT(*) AS Num
                  FROM     registrations WITH (TABLOCKX)
                  GROUP BY DateJoined, DateLeft) AS innerdat) AS idat
GROUP BY [Joined], [Unsubscribed];

CREATE TABLE #TempMonths (
  MonthNumber        SMALLINT,
  Joined             INT     ,
  Unsubscribed       INT     ,
  JoinedToDate       INT     ,
  UnsubscribedToDate INT     
);

CREATE CLUSTERED INDEX [IX_TempMonths_1]
  ON #TempMonths(MonthNumber);

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT   Joined, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT   Unsubscribed, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined AS INT, @PrevUnsubscribed AS INT;

SET @PrevJoined = 0;

SET @PrevUnsubscribed = 0;

UPDATE #TempMonths
SET    @PrevJoined       = JoinedToDate       = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
FROM   #TempMonths WITH (INDEX (0), TABLOCKX)
OPTION (MAXDOP 1);

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, Joined AS JoinedThisMonth, ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers
FROM   #TempMonths;

Here is version 3 - which is faster still on my machine, although only a slight tweak. Interesting to see the results Phil puts out given that 2 is slower on his rig than 1 is.

-- Matt 3
CREATE TABLE #TempData (
  Joined       SMALLINT,
  Unsubscribed SMALLINT,
  Num          INT     
);
CREATE CLUSTERED INDEX [IX_TempData_1]
  ON #TempData(Joined);
CREATE INDEX [IX_TempData_2]
  ON #TempData(Unsubscribed);

INSERT INTO #TempData (Joined, Unsubscribed, Num)
SELECT   Joined, Unsubscribed, SUM(Num)
FROM     (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined, DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed, [Num]
          FROM   (SELECT   [DateJoined], [DateLeft], COUNT(*) AS Num
                  FROM     registrations WITH (TABLOCKX)
                  GROUP BY DateJoined, DateLeft) AS innerdat) AS idat
GROUP BY [Joined], [Unsubscribed];

CREATE TABLE #TempMonths (
  MonthNumber        SMALLINT,
  Joined             INT     ,
  Unsubscribed       INT     ,
  JoinedToDate       INT     ,
  UnsubscribedToDate INT     
);

CREATE CLUSTERED INDEX [IX_TempMonths_1]
  ON #TempMonths(MonthNumber);

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT   Joined, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT   Unsubscribed, SUM(Num) AS Num
        FROM     #TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined AS INT, @PrevUnsubscribed AS INT;

SET @PrevJoined = 0;

SET @PrevUnsubscribed = 0;

UPDATE #TempMonths
SET    @PrevJoined       = JoinedToDate       = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
FROM   #TempMonths WITH (INDEX (0), TABLOCKX)
OPTION (MAXDOP 1);

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, Joined AS JoinedThisMonth, ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers
FROM   #TempMonths;

Ok this is my final one:

-- Matt 1b
DECLARE @TempData TABLE (Joined [smallint], Unsubscribed [smallint], Num int, UNIQUE (Joined, Unsubscribed))
INSERT INTO @TempData (Joined, Unsubscribed, Num)
SELECT Joined, Unsubscribed, COUNT(*) FROM
 (SELECT DATEDIFF(m, 0, [DateJoined]) AS Joined,
         DATEDIFF(m, 0, [DateLeft]) AS Unsubscribed
    FROM registrations WITH (TABLOCKX)) idat
GROUP BY [Joined], [Unsubscribed]

CREATE TABLE #TempMonths (MonthNumber [smallint], Joined [int], Unsubscribed [int], JoinedToDate [int], UnsubscribedToDate [int])
CREATE CLUSTERED INDEX [IX_TempMonths_1] ON #TempMonths (MonthNumber)

INSERT INTO #TempMonths (MonthNumber, Joined, Unsubscribed)
SELECT td1.Joined, td1.Num, td2.Num
FROM   (SELECT Joined, SUM(Num) AS Num FROM @TempData
        GROUP BY Joined) AS td1
       LEFT OUTER JOIN
       (SELECT Unsubscribed, SUM(Num) AS Num FROM @TempData
        GROUP BY Unsubscribed) AS td2
       ON td2.Unsubscribed = td1.Joined;

DECLARE @PrevJoined [int], @PrevUnsubscribed [int]
SET @PrevJoined = 0
SET @PrevUnsubscribed = 0

UPDATE #TempMonths
   SET @PrevJoined = JoinedToDate = @PrevJoined + Joined,
       @PrevUnsubscribed = UnsubscribedToDate = @PrevUnsubscribed + ISNULL(Unsubscribed, 0)
  FROM #TempMonths WITH(INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT DATEADD(m, MonthNumber, 0) AS MonthDate, 
       Joined AS JoinedThisMonth, 
       ISNULL(Unsubscribed, 0) AS UnsubscribedThisMonth, 
       JoinedToDate - UnsubscribedToDate AS EndOfMonthSubscribers 
  FROM #TempMonths

I'm using the "Quirky XML Select" for this:

DECLARE @monthly_joins int
DECLARE @monthly_leaves int
DECLARE @running_users int
DECLARE @current_month date
DECLARE @xchar nvarchar(max)
DECLARE @xml XML
DECLARE @now date

SELECT @xchar = '', @monthly_joins = 0, @monthly_leaves = 0, @current_month = NULL, @running_users = 0, @now = '20091001'

SELECT 
    @xchar = 
        CASE 
            WHEN eventDate = ISNULL(@current_month, eventDate) 
                THEN @xchar 
            ELSE 
                CASE
                    WHEN @current_month < @now 
                        THEN @xchar + '<A MONTH="'+ CONVERT(char(8),ISNULL(@current_month,eventDate),112) +'" JOINS="'+ CAST(@monthly_joins AS varchar(10)) +'" LEAVES="'+ CAST(@monthly_leaves AS varchar(10)) +'" TOT="'+ CAST(@running_users AS varchar(10)) +'"/>' 
                        ELSE @xchar
                    END
        END,
    @monthly_joins = 
        CASE 
            WHEN eventDate = ISNULL(@current_month,eventDate) 
                THEN @monthly_joins + JOINS 
            ELSE JOINS 
        END,
    @monthly_leaves = 
        CASE 
            WHEN eventDate = ISNULL(@current_month,eventDate) 
                THEN @monthly_leaves + LEAVES
            ELSE LEAVES 
        END,
    @running_users = @running_users + JOINS - LEAVES,
    @current_month = eventDate
FROM (
        SELECT CAST(CONVERT(char(6), DateJoined, 112) + '01' AS DATE) AS eventDate, JOINS = 1, LEAVES = 0
        FROM registrations
        UNION ALL
        SELECT CAST(CONVERT(char(6), DateLeft, 112) + '01' AS DATE) AS eventDate, JOINS = 0, LEAVES = CASE WHEN DateLeft IS NOT NULL THEN 1 ELSE 0 END
        FROM registrations
) AS Data
ORDER BY eventDate

IF @current_month < @now
    SELECT @xchar = @xchar + '<A MONTH="'+ CONVERT(char(8), @current_month, 112) +'" JOINS="'+ CAST(@monthly_joins AS varchar(10)) +'" LEAVES="'+ CAST(@monthly_leaves AS varchar(10)) +'" TOT="'+ CAST(@running_users AS varchar(10)) +'"/>'
SELECT @xml = CAST('<R>' + @xchar + '</R>' AS XML)

SELECT DISTINCT CONVERT(date, T.results.value('@MONTH[1]','char(6)') + '01', 112) as [month],
    T.results.value('@JOINS[1]','int') as joins,
    T.results.value('@LEAVES[1]','int') as leaves,
    T.results.value('@TOT[1]','int') as tot
FROM @xml.nodes('/R/A') AS T(results)

There's still some index needed, but I'm going with this for the moment. I'll be back with indexes maybe.

Here is my second attempt without a "quirky" update

-- Peso 2
DECLARE @Stage TABLE
    (
        theMonth DATE PRIMARY KEY CLUSTERED,
        PeopleJoined INT NOT NULL,
        PeopleLeft INT NOT NULL,
        Subscribers INT NOT NULL
    )

INSERT      @Stage
        (
            theMonth,
            PeopleJoined,
            PeopleLeft,
            Subscribers
        )
SELECT      p.theMonth,
        p.DateJoined AS PeopleJoined,
        COALESCE(p.DateLeft, 0) AS PeopleLeft,
        0 AS Subscribers
FROM        (
            SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, u.theDate), 0) AS theMonth,
                    u.theCol,
                    COUNT(*) AS Items
            FROM        dbo.Registrations AS r
            UNPIVOT     (
                        theDate
                        FOR theCol IN (r.DateJoined, r.DateLeft)
                    ) AS u
            GROUP BY    DATEADD(MONTH, DATEDIFF(MONTH, 0, u.theDate), 0),
                    u.theCol
        ) AS d
PIVOT       (
            MAX(d.Items)
            FOR theCol IN ([DateJoined], [DateLeft])
        ) AS p
WHERE       p.DateJoined IS NOT NULL
ORDER BY    p.theMonth

UPDATE  x
SET x.Subscribers = (SELECT SUM(w.PeopleJoined) - SUM(w.PeopleLeft) FROM @Stage AS w WHERE w.theMonth <= x.theMonth)
FROM    @Stage AS x

SELECT      theMonth,
        PeopleJoined,
        PeopleLeft,
        Subscribers
FROM        @Stage
ORDER BY    theMonth

Here is my third attempt, with a "quirky" update

-- Peso 3 "Quirky update"
DECLARE @Stage TABLE
    (
        theMonth DATE PRIMARY KEY CLUSTERED,
        PeopleJoined INT NOT NULL,
        PeopleLeft INT NOT NULL,
        Subscribers INT NOT NULL
    )

INSERT      @Stage
        (
            theMonth,
            PeopleJoined,
            PeopleLeft,
            Subscribers
        )
SELECT      p.theMonth,
        p.DateJoined AS PeopleJoined,
        COALESCE(p.DateLeft, 0) AS PeopleLeft,
        0 AS Subscribers
FROM        (
            SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, u.theDate), 0) AS theMonth,
                    u.theCol,
                    COUNT(*) AS Items
            FROM        dbo.Registrations AS r
            UNPIVOT     (
                        theDate
                        FOR theCol IN (r.DateJoined, r.DateLeft)
                    ) AS u
            GROUP BY    DATEADD(MONTH, DATEDIFF(MONTH, 0, u.theDate), 0),
                    u.theCol
        ) AS d
PIVOT       (
            MAX(d.Items)
            FOR theCol IN ([DateJoined], [DateLeft])
        ) AS p
WHERE       p.DateJoined IS NOT NULL
ORDER BY    p.theMonth

DECLARE @Subscribers INT = 0

UPDATE  @Stage
SET @Subscribers = Subscribers = @Subscribers + PeopleJoined - PeopleLeft

SELECT      theMonth,
        PeopleJoined,
        PeopleLeft,
        Subscribers
FROM        @Stage
ORDER BY    theMonth

I don't think a quirky update is necessary here. Every month (every record) is 15 bytes (Date 3 bytes, Join 4 bytes, Leave 4 bytes and Subscribers 4 bytes; all non null).

Since a page is the minimum amount of data read by SQL Server anyway, you can have about 44 years (and 9 months) in a single page. The "triangular join" update will be sufficient for those records (537 records for 44.78 years of data).

SQLServerCentral started in March 2001, so there can be only 103 records (102 months since March 2001 to September 2009). A triangular join update for 103 records is really nothing.

Oh well, I'll throw in a bone too. Phil, are you continously going to update us of the timings on your machine? This code allows you to enter any month to see what the status was then. The code also includes months were there are no new subscribers, just in case. And no quirky update of any sort.

DECLARE @Viewpoint INT

SET     @ViewPoint = DATEDIFF(MONTH, 0, GETDATE())

SELECT      DATEADD(MONTH, v.Number + d. Joined, 0) AS theMonth,
        SUM(CASE WHEN v.Number = 0 THEN d.NewSubscribers ELSE 0 END) AS NewSubscribes,
        SUM(CASE v.Number + d. Joined WHEN d.Leave THEN d.NewSubscribers ELSE 0 END) AS Unsubscribes,
        SUM(d.NewSubscribers) - SUM(CASE v.Number + d.Joined WHEN d.Leave THEN d.NewSubscribers ELSE 0 END) AS CurrentSubscribers
FROM        (
            SELECT      DATEDIFF(MONTH, 0, DateJoined) AS Joined,
                        CASE
                            WHEN DATEDIFF(MONTH, 0, DateLeft) < @ViewPoint THEN DATEDIFF(MONTH, 0, DateLeft)
                            ELSE @ViewPoint
                        END AS Leave,
                        COUNT(*) AS NewSubscribers
            FROM        dbo.Registrations
            WHERE       DateJoined < DATEADD(MONTH, @ViewPoint, 0)
            GROUP BY    DATEDIFF(MONTH, 0, DateJoined),
                        CASE
                            WHEN DATEDIFF(MONTH, 0, DateLeft) < @ViewPoint THEN DATEDIFF(MONTH, 0, DateLeft)
                            ELSE @ViewPoint
                        END
        ) AS d
INNER JOIN  master..spt_values AS v ON v.Type = 'P'
            AND v.Number <= d.Leave - d.Joined
WHERE       v.Number + d.Joined < @Viewpoint
GROUP BY    v.Number + d.Joined
ORDER BY    v.Number + d.Joined

Thank you for fixing the formatting!

I almost didn't post this when I saw how similar it is to some of the other answers. It's pretty fast and really simple (I thought) so here it goes...

DECLARE @Count INT
DECLARE @data TABLE
(
    RptDate DATETIME PRIMARY KEY,
    JoinCount INT,
    LeaveCount INT,
    SubscriberCount INT
)

SET @Count = 0

INSERT @data
(
    RptDate,
    JoinCount,
    LeaveCount
)
SELECT
    joins.JoinMonth AS RptDate,
    joins.JoinCount,
    ISNULL(leaves.LeaveCount, 0)
FROM
    (
    SELECT
        r.DateJoined + 1 - DATEPART(dd, r.DateJoined) AS JoinMonth,
        COUNT(*) AS JoinCount
    FROM
        dbo.Registrations r
    GROUP BY
        r.DateJoined + 1 - DATEPART(dd, r.DateJoined)
    ) AS joins
LEFT OUTER JOIN
    (
    SELECT
        r.DateLeft + 1 - DATEPART(dd, r.DateLeft) AS LeaveMonth,
        COUNT(*) AS LeaveCount
    FROM
        dbo.Registrations r
    WHERE
        r.DateLeft IS NOT NULL
    GROUP BY
        r.DateLeft + 1 - DATEPART(dd, r.DateLeft)
    ) AS leaves
ON
    joins.JoinMonth = leaves.LeaveMonth

UPDATE
    d
SET
    @Count = d.SubscriberCount = d.JoinCount - d.LeaveCount + @Count
FROM
    @data d

SELECT
    d.RptDate,
    d.JoinCount,
    d.LeaveCount,
    d.SubscriberCount
FROM
    @data d

Ideally solution would use window aggregates with a RANGE partition to calculate all of the aggregates in a single pass over the data. Unfortunately this is not currently supported in SQL Server…

In the following solution temp tables could be subqueries but subqueries cause SQL Server to choke. The speed of this solution is dependent on the performance of the TEMP database. A nearly full or badly fragmented TEMP db file will slow things down.

--Create temp table of months, assuming data contains all possible months...
SELECT DISTINCT ReportMonth
  INTO #months
  FROM (
SELECT DISTINCT DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined) ReportMonth FROM  Registrations --Adds
 UNION
SELECT DISTINCT DATEADD(d, -(DatePart(DAY,DateLeft)-1), DateLeft) ReportMonth FROM  Registrations --Drops
 ) a
 WHERE ReportMonth IS NOT NULL

--Create a temp table for Adds, summarised by month
SELECT DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined) ReportMonth
      ,COUNT([Registration_ID]) Adds
  INTO  #adds
  FROM  Registrations
 GROUP BY DATEADD(d, -(DatePart(DAY,DateJoined)-1), DateJoined)

--Create a temp table for Drops, summarised by month
SELECT DATEADD(d, -(DatePart(DAY,DateLeft)-1), DateLeft)   DropMonth
       ,COUNT([Registration_ID]) Drops
  INTO  #drops
  FROM  Registrations
 WHERE  DateLeft IS NOT NULL
 GROUP BY DATEADD(d, -(DatePart(DAY,DateLeft)-1),   DateLeft)

--Join all 3 temp tables together and
SELECT m.ReportMonth
      ,ISNULL(a.Adds,0) SubsAdded
      ,ISNULL(d.Drops,0) SubsDropped
--Use 2 correlated subqueries to calculate the running total.
      ,(SELECT SUM(Adds) FROM #adds WHERE #adds.ReportMonth <= a.ReportMonth)
      - ISNULL((SELECT SUM(Drops) FROM #drops WHERE #drops.DropMonth <= a.ReportMonth),0) TotalSubs
  FROM #months m
  LEFT JOIN #adds a
         ON m.ReportMonth = a.ReportMonth
  LEFT JOIN #drops d
         ON a.ReportMonth = d.DropMonth
ORDER BY ReportMonth

  SELECT Dates.date AS [date],
    (SELECT COUNT(*) FROM registrations r WHERE DateAdd(second, -1,DateAdd(month,1,Dates.date)) 
        BETWEEN r.dateJoined AND COALESCE(r.DateLeft,GETDATE() )) AS subscribers,
    (SELECT COUNT(*) FROM registrations r WHERE datepart (month,r.dateJoined) =datepart (month,Dates.date)
                    and datepart (year,r.dateJoined) =datepart (year,Dates.date)) AS joined,
    (SELECT COUNT(*) FROM registrations r WHERE datepart (month,r.dateleft) =datepart (month,Dates.date)
                    and datepart (year,r.dateLeft) =datepart (year,Dates.date)) AS [left]

FROM
    (SELECT 
      [date]=CAST('01 '+LEFT(DATENAME(month,DateJoined),3)+' '+DATENAME(year,DateJoined) AS DATETIME)
    FROM Registrations
    GROUP BY CAST('01 '+LEFT(DATENAME(month,DateJoined),3)+' '+DATENAME(year,DateJoined) AS DATETIME)) dates
ORDER BY date   

Phil told my that my answer was in big trouble, so I felt I ought to write another, only to find that Graham had pretty well got there already. I also looked carefully at Matt an Peso's entries. Think of this as a joint entry

CREATE TABLE #Preaggregation
(
 MonthJoined INT,
 MonthLeft INT,
 HowMany INT
)
INSERT  INTO #Preaggregation
    (
     MonthJoined,
     MonthLeft,
     HowMany
    )
    SELECT  [monthJoined],
            [MonthLeft],
            COUNT(*) AS [HowMany]
    FROM    (SELECT DATEDIFF(MONTH, 0, DateJoined) AS [MonthJoined],
                    DATEDIFF(MONTH, 0, COALESCE(dateleft, GETDATE())) AS [Monthleft]
             FROM   Registrations
            ) f
    GROUP BY MonthJoined,
            MonthLeft
            WITH CUBE 
CREATE INDEX idxMonthJoined ON #preaggregation (monthjoined, MonthLeft, Howmany)
CREATE INDEX idxMonthLeft ON #preaggregation (monthLeft, MonthJoined, Howmany) ;
WITH    JoinedAndLeft
      AS (SELECT    P1.MonthJoined,
                    p1.howmany AS PeopleJoined,
                    pj.howmany AS [Peopleleft]--,
          FROM      #preaggregation p1
                    INNER JOIN #preaggregation pj ON pj.monthleft=p1.monthjoined
          WHERE     pj.monthjoined IS NULL
                    AND p1.monthJoined IS NOT NULL
                    AND p1.monthleft IS NULL
         )
SELECT  DATEADD(MONTH, thisMonth.Monthjoined, 0) AS [Date],
        MIN(thisMonth.PeopleJoined)as [joined],
        MIN(thisMonth.PeopleLeft) as [left],
        SUM(previous.PeopleJoined-previous.PeopleLeft) as [subscribers]
FROM    JoinedAndleft thisMonth
        INNER JOIN JoinedAndLeft previous ON previous.Monthjoined<=ThisMonth.Monthjoined
GROUP BY thisMonth.Monthjoined
ORDER BY thisMonth.monthjoined