Untitled

CREATE VIEW covid_19 AS  
SELECT
	DISTINCT(fcv.venueid) AS venue,
    dt.yearid AS year,
    dt.monthid AS month,
	COUNT(DISTINCT f.patientid) AS covid_cases,
	COUNT(DISTINCT fpd.patientid) AS drug_treatment_count,
	COUNT(DISTINCT fcq.citizenid) AS quarantine_count,
	COUNT(CASE WHEN dv.vaccineName = 'Johnson & Johnson' THEN 1 END) AS Johnson_and_Johnson,
	COUNT(CASE WHEN dv.vaccineName = 'Moderna' THEN 1 END) AS Moderna,
	COUNT(CASE WHEN dv.vaccineName = 'Pfizer-BioNTech' THEN 1 END) AS Pfizer_BioNTech,
    (COUNT(CASE WHEN dv.vaccineName = 'Johnson & Johnson' THEN 1 END) / COUNT(DISTINCT fcs.citizenid)) * 100 AS Johnson_and_Johnson_percentage,
    (COUNT(CASE WHEN dv.vaccineName = 'Moderna' THEN 1 END) / COUNT(DISTINCT fcs.citizenid)) * 100 AS Moderna_percentage,
    (COUNT(CASE WHEN dv.vaccineName = 'Pfizer-BioNTech' THEN 1 END) / COUNT(DISTINCT fcs.citizenid)) * 100 AS Pfizer_BioNTech_percentage
FROM
    FactCitizenVenues fcv
left JOIN 
    DimTime dt ON fcv.dateid = dt.dateid
left JOIN 
    FactPatient f ON f.patientid = fcv.citizenid 
left JOIN 
    FactCitizenQuarantine fcq ON fcq.citizenid = fcv.citizenid
left JOIN 
    FactPatientDrug fpd ON fpd.patientid = fcv.citizenid
left JOIN 
    FactCitizenVaccines fcs ON fcv.citizenid = fcs.citizenid
left JOIN 
    DimVaccine dv ON fcs.vaccineid = dv.vaccineid 
GROUP BY 
    fcv.venueid, dt.yearid, dt.monthid
ORDER BY 
    fcv.venueid, dt.yearid, dt.monthid
;