How would I calculate the quotient of 'churn' with the previous month's 'active'?

Question

Trying to get the churn rate, obviously. Getting the quotient within each month would be easy but incorrect.

Frankly, I'm totally lost on this one. Would it make more sense to reorganize the output first?

Answer 1

I put your data in a table variable (which is SQL Server) to write the query. The actual SELECT statement I wrote should work in all RDBMSs - I think it is all ANSI standard SQL. You didn't mention what data you wanted to see, nor did you mention what should happen in MONTH 1 where there is no previous month, but hopefully you will be able to get your final query from seeing this.

To do it, JOIN the table to itself. I use two aliases, d1 and d2. For d1 I want to find CHURN and d2 I want to find ACTIVE. Also, the MONTHS of d2 should be one less than the MONTHS of d1. Finally, since I declared the SUM column as an INT, I multiply it by 1.0 to force it to an approximate data type, otherwise the division would come back as zero or a truncated INT (since it is integer division).

DECLARE @Data TABLE
(
    [ID]               INT,
    [MONTHS]           INT,
    [THIS_MONTH_VALUE] VARCHAR(10),
    [SUM]              INT
);

INSERT INTO @Data
(
    [ID],
    [MONTHS],
    [THIS_MONTH_VALUE],
    [SUM]
)
VALUES
(1, 0, 'NEW', 4987),
(2, 1, 'ACTIVE', 3849),
(3, 1, 'CHURN', 1138),
(4, 1, 'NEW', 884),
(5, 2, 'ACTIVE', 3821),
(6, 2, 'CHURN', 912),
(7, 2, 'NEW', 818),
(9, 3, 'ACTIVE', 3954),
(10, 3, 'CHURN', 942);

-- the following statement should work in any RDBMS but you might have to change
-- the square brackets to whatever your RDBMS uses to escape

SELECT [d1].[ID],
       [d1].[MONTHS],
       [d1].[THIS_MONTH_VALUE],
       [d1].[SUM],
       [d2].[ID],
       [d2].[MONTHS],
       [d2].[THIS_MONTH_VALUE],
       [d2].[SUM],
       1.0 * [d1].[SUM] / [d2].[SUM] AS [CHURN_RATE]
FROM   @Data AS [d1]
       INNER JOIN @Data AS [d2]
           ON [d1].[THIS_MONTH_VALUE] = 'CHURN'
              AND [d2].[THIS_MONTH_VALUE] = 'ACTIVE'
              AND [d2].[MONTHS] = [d1].[MONTHS] - 1;

The output is:

ID	MONTHS	THIS_MONTH_VALUE	SUM	ID	MONTHS	THIS_MONTH_VALUE	SUM	CHURN_RATE
6	2	CHURN	912	2	1	ACTIVE	3849	0.236944660950
10	3	CHURN	942	5	2	ACTIVE	3821	0.246532321381

Again, you might have to modify the query to get exactly what you want.