/*
Morgan Stanley SQL Interview Question

A vaccine is administered in two doses. It is best if the doses are given between 42 and 72 days apart, inclusive.

Write a SQL query to return the percentage of beneficiaries who received both doses within the recommended time period, rounded to the nearest and Vaccination_Date
*/
    
-- Schema Setup
CREATE TABLE Doses (
 Dose_id INT PRIMARY KEY,
 Beneficiary_id INT,
 Dose_type VARCHAR(6),
 Vaccination_date DATE
);

INSERT INTO Doses (dose_id, beneficiary_id, dose_type, vaccination_date) VALUES
(2193, 750, 'first', '2021-05-15'),
(2194, 750, 'second', '2021-07-05'),
(2195, 751, 'first', '2021-06-01'),
(2196, 751, 'second', '2021-07-31'),
(2197, 752, 'first', '2021-06-10'),
(2198, 752, 'second', '2021-07-30'),
(2199, 753, 'first', '2021-06-15'),
(2200, 753, 'second', '2021-09-01'),
(2201, 754, 'first', '2021-04-18'),
(2202, 754, 'second', '2021-06-10'),
(2203, 755, 'first', '2021-05-25'),
(2204, 755, 'second', '2021-08-15');

-- Solution
WITH all_doses AS
(
 SELECT
     dose_id,
     beneficiary_id,
     vaccination_date AS first_dose,
     LEAD(vaccination_date) OVER(PARTITION BY beneficiary_id ORDER BY beneficiary_id) AS second_dose
 FROM Doses
)

SELECT
    ROUND(SUM(CASE WHEN dose_flag = 'Y' THEN 1 ELSE 0 END) / COUNT(dose_flag) * 100.0) AS percentage_within_recommended_period
FROM
(SELECT
     dose_id,
     beneficiary_id,
     first_dose,
     second_dose,
     CASE WHEN DATEDIFF(second_dose,first_dose) BETWEEN 42 AND 72 THEN 'Y' ELSE 'N' END AS dose_flag
 FROM all_doses
 WHERE second_dose IS NOT NULL
) AS doses_subquery;