-
Notifications
You must be signed in to change notification settings - Fork 0
/
DS_hw3.txt
104 lines (89 loc) · 2.68 KB
/
DS_hw3.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
Qs1:
#--------------------- age (I used sep30,2024 as the end day)
# add a column called age
UPDATE hw3.person
SET age = FLOOR(DATEDIFF(CURDATE(), DATE(CONCAT(year_of_birth, '-', month_of_birth, '-', day_of_birth))) / 365.25)
WHERE year_of_birth IS NOT NULL;
#calculate count and percentage of each age group
SELECT
COUNT(*) AS count_in_age_group,
(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM hw3.person)) AS percentage_in_age_group
FROM
hw3.person
WHERE
age > 18 AND age <= 30;
#É (run for each age category)
#---------------------------------------- gender
SELECT
ta.gender_concept_id AS gender_id,
tb.concept_name AS gender_name,
COUNT(DISTINCT ta.person_id) AS ct,
(COUNT(DISTINCT ta.person_id) * 100.0 / (SELECT COUNT(DISTINCT person_id) FROM hw3.person)) AS percentage
FROM
hw3.person AS ta
JOIN
hw3.concept AS tb ON ta.gender_concept_id = tb.concept_id
GROUP BY
ta.gender_concept_id, tb.concept_name
ORDER BY
ct DESC;
#------------------------------------------------- race
SELECT
ta.race_concept_id AS race_id,
tb.concept_name AS race_name,
COUNT(DISTINCT ta.person_id) AS ct,
(COUNT(DISTINCT ta.person_id) * 100.0 / (SELECT COUNT(DISTINCT person_id) FROM hw3.person)) AS percentage
FROM
hw3.person AS ta
JOIN
hw3.concept AS tb ON ta.race_concept_id = tb.concept_id
GROUP BY
ta.race_concept_id, tb.concept_name
ORDER BY
ct DESC;
#---------------------------------------------- ethnicity
SELECT
ta.ethnicity_concept_id AS ethnicity_id,
tb.concept_name AS ethnicity_name,
COUNT(DISTINCT ta.person_id) AS ct,
(COUNT(DISTINCT ta.person_id) * 100.0 / (SELECT COUNT(DISTINCT person_id) FROM hw3.person)) AS percentage
FROM
hw3.person AS ta
JOIN
hw3.concept AS tb ON ta.ethnicity_concept_id = tb.concept_id
GROUP BY
ta.ethnicity_concept_id, tb.concept_name
ORDER BY
ct DESC;
Qs2:
SELECT
de.drug_concept_id,
c.concept_name AS drug_name,
COUNT(DISTINCT de.person_id) AS number_of_patients
FROM
hw3.drug_exposure AS de
JOIN
hw3.concept AS c ON de.drug_concept_id = c.concept_id
GROUP BY
de.drug_concept_id, c.concept_name
HAVING
COUNT(DISTINCT de.person_id) >= 100
ORDER BY
number_of_patients DESC;
## (output see attached)
Qs3:
SELECT
co.condition_concept_id AS disease_concept_id,
c.concept_name AS disease_name,
COUNT(DISTINCT co.person_id) AS number_of_patients
FROM
hw3.condition_occurrence AS co
JOIN
hw3.concept AS c ON co.condition_concept_id = c.concept_id
GROUP BY
co.condition_concept_id, c.concept_name
HAVING
COUNT(DISTINCT co.person_id) >= 500
ORDER BY
number_of_patients DESC;
## (output see attached)