Exam_3_Final, Sql Server

Exam_3_Final

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE Users(
    id INT NOT NULL IDENTITY PRIMARY KEY ,
    Name varchar(100),
    Email varchar(100),
    Sub_Date datetime,
    Unsub_Date datetime);
GO
INSERT INTO Users(Name, Email, Sub_Date, Unsub_Date)
VALUES 
('NANDAN','NANDAN@CHANDAN.COM','2019-09-01','2018-10-26'),
('PHALLE','NANDAN@CHANDAN.COM','2019-08-01', NULL),
('NEERAD','4CHAWANIGHODEPE@NANDANMERELODEPE','2019-11-01','2019-12-26'),
('HAGAL','MEHAT_HAGAL@GMAIL.COM','2019-10-11','2019-11-11')
GO
SELECT * FROM Users;
GO
CREATE PROCEDURE report
AS
BEGIN
    SELECT MONTH(Sub_Date) AS MONTH, COUNT(*) SUBSCRIPTIONS, COUNT(*) OVER () AS TOTAL_USERS
    INTO #STARTUSER
    FROM Users
    GROUP BY MONTH(Sub_Date)
    
    SELECT MONTH(Unsub_Date) AS MONTH, COUNT(*) UNSUBSCRIPTION
    INTO #ENDUSER
    FROM Users
    GROUP BY MONTH(Unsub_Date)
    
    SELECT U.MONTH MONTH, U.SUBSCRIPTIONS STARTING_SUBSCRIPTION, P.UNSUBSCRIPTION ENDING_SUBSCRIPTION, (U.TOTAL_USERS - ISNULL(( P.UNSUBSCRIPTION), 0 )) AS RETENTION
    FROM #STARTUSER U 
    FULL JOIN #ENDUSER P ON U.MONTH = P.MONTH  
    where U.SUBSCRIPTIONS is not NULL
    ORDER BY U.MONTH

END
GO
EXEC report;


run \| edit \| history \| help	0