Exam_Final, Sql Server

Exam_Final

CREATE TABLE Users(
id int IDENTITY PRIMARY KEY NOT NULL,
Name varchar(100),
Email varchar(100),
Sub_Date datetime,
Unsub_Date datetime    
);
go
INSERT INTO Users (Name,Email,Sub_Date,Unsub_Date)
VALUES ('Neerad','nrdnandan@gmail.com','2019-09-01','2018-01-01'),('MehtaJi','mehatchuttad@gmail.com','2019-08-10',NULL), ('MRS','nrdnandan@gmail.com','2019-09-01','2019-09-01'),
('SimanG','simanbewara@gmail.com','2018-05-10','2019-01-05'),('Xing','wangx@gmail.com','2020-01-01',NULL);
GO

CREATE PROCEDURE MONTHLY_REPORT
AS
BEGIN
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME

SET @STARTDATE  = '01 JANUARY 2019';
SET @ENDDATE    = '31 DECEMBER 2019';

WITH CTE_Months (dates)
AS
(
    SELECT @STARTDATE dates
    UNION ALL
    SELECT DATEADD(MONTH,1,dates)
    FROM CTE_Months
    WHERE DATEADD(MONTH,1,dates) < @ENDDATE
),
CTE_A (MONTHS, PEOPLESTARTED)
AS
(
    SELECT MONTH(SUB_DATE),COUNT(*)
    FROM USERS
    GROUP BY MONTH(SUB_DATE)
),
CTE_B (MONTHS, PEOPLEENDEDTHISMONTH)
AS
(
    SELECT MONTH(Unsub_Date), COUNT(*)
    FROM USERS
    GROUP BY MONTH(Unsub_Date)
)
SELECT MONTH(M.dates) MONTHS, ((CAST(PEOPLEENDEDTHISMONTH AS float) / CAST(PEOPLESTARTED AS float)) * 100) RETENTION_RATE
FROM CTE_A A RIGHT JOIN CTE_Months M ON MONTH(M.dates) = A.MONTHS
LEFT JOIN CTE_B B ON MONTH(M.dates) = B.MONTHS

END
GO
EXEC MONTHLY_REPORT;


run \| edit \| history \| help	0