Navigation Menu
Lukman-Omotosho / Lukman-Omotosho Public
Files
Consumer Complaints Analysis
Houses_in_Nigeria.Rmd
Houses_in_Nigeria.html
Nigeria_Agric_Export_Analysis.Rmd
Nigeria_Agric_Export_Analysis.html
Nigeria_Agric_Export_Analysis.pbix
Order and Sales Analysis.pbix
README.md
Retail Strategy Analytics 1.Rmd
Sales_Intro.Rmd
Sales_Intro.html
Scraping_a_Table.py
hospital_patients_analysis.sql
nigeria_houses.sql
Breadcrumbs
/
Latest commit
History
File metadata and controls
177 lines (155 loc) · 5.31 KB
create table patients ( patient varchar(100) primary key not null, birthdate date, deathdate date, ssn varchar(100), drivers varchar(100), passport varchar(100), prefix char(10), first_name char(30), last_name char(30), suffix char(10), maiden char(20), marital char(10), race char(20), ethnicity char(30), gender char(10), birthplace char(30), address varchar(100)); create table patient_condition ( start_date date, stop_date date, patient varchar(100), encounter_id varchar(100), code_id int, condition_description varchar(100)); create table medications ( start_date date, stop_date date, patient varchar(100), encounter_id varchar(100), code_id int, medication_description varchar(100), reason_code int, reason_description char(100)); select * from patients limit 10; select * from medications limit 10; select * from patient_condition limit 10; #The most reoccurent disease in patient select condition_description, count(*) as no_of_occurence from patient_condition group by condition_description order by no_of_occurence desc limit 10; #Number of viral sinusitis conditions in males and females select p.gender, pc.condition_description, count(*) as no_of_occurencies from patients p inner join patient_condition pc on p.patient = pc.patient group by p.gender, pc.condition_description having pc.condition_description = 'Viral sinusitis (disorder)' order by no_of_occurencies; #Number of males and females with Acute Bronchitis select p.gender, pc.condition_description, count(*) as no_of_occurencies from patients p inner join patient_condition pc on p.patient = pc.patient group by p.gender, pc.condition_description having pc.condition_description = 'Acute bronchitis (disorder)' order by no_of_occurencies; #Number of pregnancy according to ethnic group select p.ethnicity, pc.condition_description, count(*) as no_of_occurencies from patients p inner join patient_condition pc on p.patient = pc.patient group by p.ethnicity, pc.condition_description having pc.condition_description = 'Normal pregnancy' order by no_of_occurencies desc limit 10; #Medications and the reason for their use select medication_description, reason_description, count(*) as frequency from medications group by medication_description, reason_description order by frequency desc; #The medications most used by each ethnic group select p.ethnicity, m.medication_description, count(*) as frequency from patients p inner join medications m on p.patient = m.patient group by p.ethnicity, m.medication_description order by frequency desc; #Number of dead patient who took injections select count(p.deathdate) as no_of_death from (select * from medications where medication_description like '%Injection') i inner join patients p on i.patient = p.patient; #Medications most used by married patients select p.marital, m.medication_description, count(*) as frequency from patients p inner join medications m on p.patient = m.patient group by p.marital, m.medication_description having p.marital = 'M' order by frequency desc limit 10; #The race using the most drugs related to disorder select p.race, m.reason_description, count(*) as frequency from patients p inner join medications m on p.patient = m.patient group by p.race, m.reason_description having m.reason_description like '%(disorder)' order by frequency desc; #Conditions encountered by most dead patient select pc.condition_description, count(deathdate) as frequency from patients p inner join patient_condition pc on p.patient = pc.patient group by pc.condition_description order by frequency desc limit 10; #Conditions with the highest average number of days to recover select condition_description, avg(datediff(stop_date, start_date)) as recover_days from patient_condition group by condition_description order by recover_days desc limit 10; #Conditions with the lowest average number of days to recover select condition_description, avg(datediff(stop_date, start_date)) as recover_days from patient_condition group by condition_description order by recover_days limit 10; #Average number of days it takes for each gender to recover from disorders select p.gender, pc.condition_description, avg(datediff(pc.stop_date, pc.start_date)) as recover_days from patients p inner join patient_condition pc on p.patient = pc.patient group by gender, condition_description having pc.condition_description like '%(disorder)' order by 2; #Birthplace of patients with highest number of conditions select p.birthplace, count(pc.condition_description) as no_of_conditions from patients p inner join patient_condition pc on p.patient = pc.patient group by birthplace order by no_of_conditions desc limit 1; #Number of pregnancy by race select p.race, pc.condition_description, count(*) as no_of_pregnancies from patients p inner join patient_condition pc on p.patient = pc.patient group by race, condition_description having pc.condition_description = 'Normal pregnancy' order by no_of_pregnancies desc; #The name of patients with the most number of conditions select p.first, p.last, count(*) as no_of_conditions from patients p inner join patient_condition pc on p.patient = pc.patient group by first, last order by no_of_conditions desc limit 10; #The patients with the least number of conditions select p.first, p.last, count(*) as no_of_conditions from patients p inner join patient_condition pc on p.patient = pc.patient group by first, last order by no_of_conditions limit 10;
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
