Hospital Data Analysis with SQL

Ingestion of the data into Postgres

import boto3
import json
import pandas as pd

from sqlalchemy import create_engine
import psycopg2

%reload_ext sql

def get_secret(secret_name):
    region_name = "us-east-1"
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=region_name)
    get_secret_value_response = client.get_secret_value(SecretId=secret_name)
    get_secret_value_response = json.loads(get_secret_value_response['SecretString'])
    return get_secret_value_response

db_credentials = get_secret(secret_name='wysde')

USERNAME = db_credentials["RDS_POSTGRES_USERNAME"]
PASSWORD = db_credentials["RDS_POSTGRES_PASSWORD"]
HOST = "database-1.cy8ltogyfgas.us-east-1.rds.amazonaws.com"
PORT = 5432
DBNAME = "postgres"
CONN = f"postgresql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DBNAME}"

# load the data into our postgres database
alchemyEngine = create_engine(CONN, pool_recycle=3600);
postgreSQLConnection = alchemyEngine.connect();

%sql {CONN}

SCHEMA = "hospital"

%sql create schema if not exists {SCHEMA};

patients = pd.read_csv("patients.csv", index_col=0)
patients.birth_date = pd.to_datetime(patients.birth_date)
patients.info()
patients.to_sql('patients', postgreSQLConnection, if_exists='replace', schema=SCHEMA, index=False)

admissions = pd.read_csv("admissions.csv", index_col=0)
admissions.admission_date = pd.to_datetime(admissions.admission_date)
admissions.discharge_date = pd.to_datetime(admissions.discharge_date)
admissions.info()
admissions.to_sql('admissions', postgreSQLConnection, if_exists='replace', schema=SCHEMA, index=False)

physicians = pd.read_csv("physicians.csv", index_col=0)
physicians.info()
physicians.to_sql('physicians', postgreSQLConnection, if_exists='replace', schema=SCHEMA, index=False)

province_names = pd.read_csv("province_names.csv", index_col=0)
province_names.info()
province_names.to_sql('province_names', postgreSQLConnection, if_exists='replace', schema=SCHEMA, index=False)

postgreSQLConnection.close();

Connect

import boto3
import json

%reload_ext sql

def get_secret(secret_name):
    region_name = "us-east-1"
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=region_name)
    get_secret_value_response = client.get_secret_value(SecretId=secret_name)
    get_secret_value_response = json.loads(get_secret_value_response['SecretString'])
    return get_secret_value_response

db_credentials = get_secret(secret_name='wysde')

USERNAME = db_credentials["RDS_POSTGRES_USERNAME"]
PASSWORD = db_credentials["RDS_POSTGRES_PASSWORD"]
HOST = "database-1.cy8ltogyfgas.us-east-1.rds.amazonaws.com"
PORT = 5432
DBNAME = "postgres"
CONN = f"postgresql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DBNAME}"

%sql {CONN}

SCHEMA = "hospital"
%sql SET search_path = {SCHEMA}

Easy

Show first name, last name, and the full province name of each patient.

Example: 'Ontario' instead of 'ON'

%%sql
SELECT
  first_name,
  last_name,
  province_name
FROM patients
  JOIN province_names ON province_names.province_id = patients.province_id
LIMIT 10;

first_name	last_name	province_name
Velu	Galv�n	Alberta
Derek	Nappa	Alberta
Aniki	Leotardo	Alberta
Leonard	Sacrimoni	Alberta
Reginald	Cavor	Alberta
Laura	March	British Columbia
Lydia	Carver	British Columbia
Annie	Smart	British Columbia
Jackie	Dean	British Columbia
Catherine	Minoru	British Columbia

Show patient_id, first_name, last_name from patients whose diagnosis is 'Dementia'.

Primary diagnosis is stored in the admissions table.

%%sql
SELECT
  patients.patient_id,
  first_name,
  last_name
FROM patients
  JOIN admissions ON admissions.patient_id = patients.patient_id
WHERE diagnosis = 'Dementia';

patient_id	first_name	last_name
160	Miranda	Delacour
178	David	Bustamonte
207	Matt	Celine

Show first name and last name concatinated into one column to show their full name.

%%sql
SELECT
  CONCAT(first_name, ' ', last_name) AS full_name
FROM patients
LIMIT 10;

full_name
Donald Waterfield
Mickey Baasha
Jiji Sharma
Blair Diaz
Charles Wolfe
Sue Falcon
Thomas ONeill
Sonny Beckett
Sister Spitzer
Cedric Coltrane

Show how many patients have a birth_date with 2010 as the birth year.

%%sql
SELECT COUNT(*) AS total_patients
FROM patients
WHERE EXTRACT(YEAR from birth_date) = 2010;

total_patients
5

Show the first_name, last_name, and height of the patient with the greatest height.

%%sql
SELECT * FROM patients LIMIT 10;

patient_id	first_name	last_name	gender	birth_date	city	province_id	allergies	height	weight
1	Donald	Waterfield	M	1963-02-12 00:00:00	Barrie	ON	None	156	65
2	Mickey	Baasha	M	1981-05-28 00:00:00	Dundas	ON	Sulfa	185	76
3	Jiji	Sharma	M	1957-09-05 00:00:00	Hamilton	ON	Penicillin	194	106
4	Blair	Diaz	M	1967-01-07 00:00:00	Hamilton	ON	None	191	104
5	Charles	Wolfe	M	2017-11-19 00:00:00	Orillia	ON	Penicillin	47	10
6	Sue	Falcon	F	2017-09-30 00:00:00	Ajax	ON	Penicillin	43	5
7	Thomas	ONeill	M	1993-01-31 00:00:00	Burlington	ON	None	180	117
8	Sonny	Beckett	M	1952-12-11 00:00:00	Port Hawkesbury	NS	None	174	105
9	Sister	Spitzer	F	1966-10-15 00:00:00	Toronto	ON	Penicillin	173	95
10	Cedric	Coltrane	M	1961-11-10 00:00:00	Toronto	ON	None	157	61

%%sql
SELECT
  first_name,
  last_name,
  height
FROM patients
WHERE height = (
    SELECT max(height)
    FROM patients
  );

first_name	last_name	height
Joe	Snyder	224

Show all columns for patients who have one of the following patient_ids: 1,45,534,879,1000

%%sql
SELECT *
FROM patients
WHERE
  patient_id IN (1, 45, 534, 879, 1000)
LIMIT 10;

patient_id	first_name	last_name	gender	birth_date	city	province_id	allergies	height	weight
1	Donald	Waterfield	M	1963-02-12 00:00:00	Barrie	ON	None	156	65
45	Cross	Gordon	M	2009-03-20 00:00:00	Ancaster	ON	None	125	53

Show the total number of admissions

%%sql
SELECT COUNT(*) AS total_admissions
FROM admissions;

total_admissions
400

Show all the columns from admissions where the patient was admitted and discharged on the same day.

%%sql
SELECT *
FROM admissions
WHERE admission_date = discharge_date
LIMIT 10;

patient_id	admission_date	discharge_date	diagnosis	attending_physician_id
1	2018-09-20 00:00:00	2018-09-20 00:00:00	Ineffective Breathin Pattern R/T Fluid Accumulatio	24
9	2018-12-31 00:00:00	2018-12-31 00:00:00	Ruptured Appendicitis	19
10	2019-02-27 00:00:00	2019-02-27 00:00:00	Lower Quadrant Pain	27
17	2019-03-04 00:00:00	2019-03-04 00:00:00	Diabetes Mellitus	9
28	2019-03-30 00:00:00	2019-03-30 00:00:00	Cancer Of The Stomach	26
31	2018-09-26 00:00:00	2018-09-26 00:00:00	Cardiovascular Disease	19
53	2018-10-24 00:00:00	2018-10-24 00:00:00	Urinary Tract Infection	8
54	2019-04-07 00:00:00	2019-04-07 00:00:00	Hypertension	21
70	2018-07-17 00:00:00	2018-07-17 00:00:00	Migraine	20
78	2018-06-17 00:00:00	2018-06-17 00:00:00	Hypertension	17

Show the total number of admissions for patient_id 45.

%%sql
SELECT
  patient_id,
  COUNT(*) AS total_admissions
FROM admissions
WHERE patient_id = 45
GROUP BY patient_id;

patient_id	total_admissions
45	1

Based on the cities that our patients live in, show unique cities that are in province_id 'NS'?

%%sql
SELECT DISTINCT(city) AS unique_cities
FROM patients
WHERE province_id = 'NS';

unique_cities
Halifax
Port Hawkesbury

Write a query to find the first_name, last name and birth date of patients who have height more than 160 and weight more than 70

%%sql
SELECT first_name, last_name, birth_date FROM patients
WHERE height > 160 AND weight > 70
LIMIT 10;

first_name	last_name	birth_date
Mickey	Baasha	1981-05-28 00:00:00
Jiji	Sharma	1957-09-05 00:00:00
Blair	Diaz	1967-01-07 00:00:00
Thomas	ONeill	1993-01-31 00:00:00
Sonny	Beckett	1952-12-11 00:00:00
Sister	Spitzer	1966-10-15 00:00:00
Rick	Bennett	1977-01-27 00:00:00
Amy	Leela	1977-06-25 00:00:00
Tom	Halliwell	1987-08-01 00:00:00
Rachel	Winterbourne	1966-04-26 00:00:00

Write a query to find list of patients first_name, last_name, and allergies from Hamilton where allergies are not null

%%sql
SELECT
  first_name,
  last_name,
  allergies
FROM patients
WHERE
  city = 'Hamilton'
  and allergies is not null
LIMIT 10;

first_name	last_name	allergies
Jiji	Sharma	Penicillin
Tom	Halliwell	Ragweed
Nino	Andrews	Peanuts
John	Farley	Gluten
Sam	Threep	Sulpha
Temple	Russert	Hay Fever
Alice	Donovan	Penicillin
Stone	Cutting	Codeine
Phil	Chester	Penicillin
Roland	Murphy	Sulfa Drugs

Medium

Show unique birth years from patients and order them by ascending.

%%sql
SELECT
  DISTINCT EXTRACT(YEAR FROM birth_date) AS birth_year
FROM patients
ORDER BY birth_year
LIMIT 10;

birth_year
1918
1923
1933
1934
1938
1939
1948
1949
1950
1951

Show unique first names from the patients table which only occurs once in the list.

For example, if two or more people are named 'John' in the first_name column then don't include their name in the output list. If only 1 person is named 'Leo' then include them in the output.

%%sql
SELECT first_name
FROM patients
GROUP BY first_name
HAVING COUNT(first_name) = 1
LIMIT 10;

first_name
Moe
Mrs
Cindy
Emil
Herr
Hercule
Nancy
Dame
Belladonna
Shion

Show patient_id and first_name from patients where their first_name start with 'N' and ends with 'y' and is at least 3 characters long.

%%sql
SELECT
  patient_id,
  first_name
FROM patients
WHERE first_name LIKE 'N_%y'
LIMIT 10;

patient_id	first_name
181	Nancy
434	Nicky

Display every patient's first_name. Order the list by the length of each name and then by alphbetically.

%%sql
SELECT first_name
FROM patients
order by
  LENGTH(first_name),
  first_name
LIMIT 10;

first_name
Bo
Abe
Abi
Amy
Amy
Amy
Amy
Ana
Ann
Ann

Show the total amount of male patients and the total amount of female patients in the patients table.

Display the two results in the same row.

%%sql
SELECT 
  (SELECT count(*) FROM patients WHERE gender='M') AS male_count, 
  (SELECT count(*) FROM patients WHERE gender='F') AS female_count;

male_count	female_count
273	227

Show first and last name, allergies from patients which have allergies to either 'Penicillin' or 'Morphine'. Show results ordered ascending by allergies then by first_name then by last_name.

%%sql
SELECT
  first_name,
  last_name,
  allergies
FROM patients
WHERE
  allergies IN ('Penicillin', 'Morphine')
ORDER BY
  allergies,
  first_name,
  last_name
LIMIT 10;

first_name	last_name	allergies
Briareos	Hayes	Morphine
Jon	Guarnaccia	Morphine
Temple	Starsmore	Morphine
Abi	Nesmith	Penicillin
Adam	Hecatonchires	Penicillin
Agatha	Sawyer	Penicillin
Agnes	Duckworth	Penicillin
Alice	Donovan	Penicillin
Amy	Fuhrey	Penicillin
Amy	McFly	Penicillin

Show patient_id, diagnosis from admissions. Find patients admitted multiple times for the same diagnosis.

%%sql
SELECT
  patient_id,
  diagnosis
FROM admissions
GROUP BY
  patient_id,
  diagnosis
HAVING COUNT(*) > 1
LIMIT 10;

patient_id	diagnosis
320	Pneumonia
137	Pregnancy

Show the city and the total number of patients in the city.

Order from most to least patients and then by city name ascending.

%%sql
SELECT
  city,
  COUNT(*) AS num_patients
FROM patients
GROUP BY city
ORDER BY num_patients DESC, city asc
LIMIT 10;

city	num_patients
Hamilton	215
Burlington	33
Toronto	33
Brantford	20
Stoney Creek	17
Ancaster	14
Cambridge	9
Barrie	8
Delhi	6
Dundas	6

Show first name, last name and role of every person that is either patient or physician.

The roles are either "Patient" or "Physician"

%%sql
SELECT first_name, last_name, 'Patient' as role FROM patients
    union
SELECT first_name, last_name, 'Physician' FROM physicians
LIMIT 10;

first_name	last_name	role
Lenny	Holmes	Patient
Vernon	Halliwell	Patient
Charles	Wolfe	Patient
Spiros	Mangel	Patient
Jane	Mars	Patient
Mary	Knight	Patient
Catherine	Minoru	Patient
Maggie	Price	Patient
Bertha	Crowley	Patient
Harry	Sofer	Patient

Show all allergies ordered by popularity. Remove NULL values from query.

%%sql
SELECT
  allergies,
  COUNT(*) AS total_diagnosis
FROM patients
WHERE
  allergies IS NOT NULL
GROUP BY allergies
ORDER BY total_diagnosis DESC
LIMIT 10;

allergies	total_diagnosis
Penicillin	113
Codeine	30
Sulfa	24
ASA	8
Sulfa Drugs	7
Peanuts	6
Tylenol	6
Valporic Acid	5
Wheat	4
Hay Fever	4

Show all patient's first_name, last_name, and birth_date who were born in the 1970s decade. Sort the list starting from the earliest birth_date.

%%sql
SELECT
  first_name,
  last_name,
  birth_date
FROM patients
WHERE
  EXTRACT (YEAR FROM birth_date) BETWEEN 1970 AND 1979
ORDER BY birth_date ASC
LIMIT 10;

first_name	last_name	birth_date
Jadu	Principal	1970-03-28 00:00:00
Kenny	Skelton	1970-05-29 00:00:00
Temple	LoPresti	1970-06-08 00:00:00
Dominic	Poppins	1971-02-18 00:00:00
Robbie	Wilde	1971-03-27 00:00:00
Richard	Davis	1971-08-03 00:00:00
Tom	Lovegood	1971-11-27 00:00:00
Joanna	Cooper	1972-01-15 00:00:00
Freddie	Landsman	1972-03-16 00:00:00
Sarah	Daniels	1972-07-22 00:00:00

Separate the last_name and first_name with a comma. Order the list by the first_name in decending order.

We want to display each patient's full name in a single column. Their last_name in all upper letters must appear first, then first_name in all lower case letters. EX: SMITH,jane

%%sql
SELECT
  CONCAT(UPPER(last_name), ',', LOWER(first_name)) AS new_name_format
FROM patients
ORDER BY first_name DESC
LIMIT 10;

new_name_format
MILLER,zoe
RIVIERA,woody
BASHIR,woody
HALE,winnie
CLOCK,winnie
LARKIN,winifred
DINGLE,willie
TEMPLIN,william
MANU,will
VINCENT,wilfred

Show the province_id(s), sum of height; where the total sum of its patient's height is greater than or equal to 7,000.

%sql select * from patients limit 10;

patient_id	first_name	last_name	gender	birth_date	city	province_id	allergies	height	weight
1	Donald	Waterfield	M	1963-02-12 00:00:00	Barrie	ON	None	156	65
2	Mickey	Baasha	M	1981-05-28 00:00:00	Dundas	ON	Sulfa	185	76
3	Jiji	Sharma	M	1957-09-05 00:00:00	Hamilton	ON	Penicillin	194	106
4	Blair	Diaz	M	1967-01-07 00:00:00	Hamilton	ON	None	191	104
5	Charles	Wolfe	M	2017-11-19 00:00:00	Orillia	ON	Penicillin	47	10
6	Sue	Falcon	F	2017-09-30 00:00:00	Ajax	ON	Penicillin	43	5
7	Thomas	ONeill	M	1993-01-31 00:00:00	Burlington	ON	None	180	117
8	Sonny	Beckett	M	1952-12-11 00:00:00	Port Hawkesbury	NS	None	174	105
9	Sister	Spitzer	F	1966-10-15 00:00:00	Toronto	ON	Penicillin	173	95
10	Cedric	Coltrane	M	1961-11-10 00:00:00	Toronto	ON	None	157	61

%%sql
SELECT
  province_id,
  SUM(height) AS sum_height
FROM patients
GROUP BY province_id
HAVING SUM(height) >= 7000;

province_id	sum_height
ON	76576

Show the difference between the largest weight and smallest weight for patients with the last name 'Maroni'.

%%sql
SELECT
  (MAX(weight) - MIN(weight)) AS weight_delta
FROM patients
WHERE last_name = 'Maroni';

weight_delta
0

Show all of the days of the month (1-31) and how many admission_dates occurred on that day. Sort by the day with most admissions to least admissions.

%%sql
SELECT
  EXTRACT (DAY FROM admission_date) AS day_number,
  COUNT(*) AS number_of_admissions
FROM admissions
GROUP BY day_number
ORDER BY number_of_admissions DESC
LIMIT 10;

day_number	number_of_admissions
2	21
4	19
9	18
18	16
10	16
7	16
12	16
24	15
15	15
25	15

Show all columns for patient_id 45's most recent admission_date.

%%sql
SELECT *
FROM admissions
WHERE patient_id = 45
ORDER BY admission_date DESC
LIMIT 1;

patient_id	admission_date	discharge_date	diagnosis	attending_physician_id
45	2018-11-15 00:00:00	2018-11-23 00:00:00	Post Partum Hemmorage	6

%%sql
SELECT *
FROM admissions
WHERE
  patient_id = '45'
  AND admission_date = (
    SELECT MAX(admission_date)
    FROM admissions
    WHERE patient_id = '45'
  );

patient_id	admission_date	discharge_date	diagnosis	attending_physician_id
45	2018-11-15 00:00:00	2018-11-23 00:00:00	Post Partum Hemmorage	6

Show patient_id, attending_physician_id, and diagnosis for admissions that match one of the given criteria.

1. Criteria 1: patient_id is an odd number and attending_physician_id is either 1, 5, or 19.
2. Criteria 2: attending_physician_id contains a 2 and the length of patient_id is 3 characters.

%%sql
SELECT
  patient_id,
  attending_physician_id,
  diagnosis
FROM admissions
WHERE
  (
    attending_physician_id IN (1, 5, 19)
    AND patient_id % 2 != 0
  )
  OR 
  (
    (attending_physician_id::text) LIKE '%2%'
    AND LENGTH(patient_id::text) = 3
  )
  LIMIT 10;

patient_id	attending_physician_id	diagnosis
9	19	Ruptured Appendicitis
13	1	Renal Failure
15	5	Hiatal Hernia
31	19	Cardiovascular Disease
51	1	Undiagnosed Chest Pain
100	22	Depression, Dementia
100	21	Respiratory Failure
103	22	Ovarian Cyst
103	2	Basal Skull Fracture
104	25	Abdominal Pain

Show first_name, last_name, and the total number of admissions attended for each physician.

Every admission has been attended by a physician.

%%sql
SELECT
  first_name,
  last_name,
  COUNT(*) as admissions_total
from admissions a
  JOIN physicians ph on ph.physician_id = a.attending_physician_id
GROUP BY first_name, last_name
LIMIT 10;

first_name	last_name	admissions_total
Simon	Santiago	17
Donna	Greenwood	15
Larry	Miller	16
Irene	Brooks	6
Jenny	Pulaski	11
Ralph	Wilson	18
Tyrone	Smart	15
Mickey	Duval	17
Marie	Brinkman	15
Miriam	Tregre	12

For each physicain, display their id, full name, and the first and last admission date they attended.

%%sql
SELECT
  physician_id,
  first_name || ' ' || last_name as full_name,
  MIN(admission_date) as first_admission_date,
  MAX(admission_date) as last_admission_date
from admissions a
  JOIN physicians ph on a.attending_physician_id = ph.physician_id
GROUP BY physician_id, full_name
LIMIT 10;

physician_id	full_name	first_admission_date	last_admission_date
1	Claude Walls	2018-06-20 00:00:00	2019-05-07 00:00:00
2	Joshua Green	2018-06-26 00:00:00	2019-06-02 00:00:00
3	Miriam Tregre	2018-06-10 00:00:00	2019-04-07 00:00:00
4	James Russo	2018-06-15 00:00:00	2019-04-19 00:00:00
5	Scott Hill	2018-06-28 00:00:00	2019-05-06 00:00:00
6	Tasha Phillips	2018-08-02 00:00:00	2019-06-02 00:00:00
7	Hazel Patterson	2018-06-20 00:00:00	2019-04-23 00:00:00
8	Mickey Duval	2018-06-10 00:00:00	2019-06-05 00:00:00
9	Jon Nelson	2018-06-26 00:00:00	2019-04-10 00:00:00
10	Monica Singleton	2018-06-18 00:00:00	2019-05-04 00:00:00

Display the total amount of patients for each province. Order by descending.

%%sql
SELECT
  province_name,
  COUNT(*) as patient_count
FROM patients pa
  join province_names pr on pr.province_id = pa.province_id
GROUP BY pr.province_id, province_name
ORDER BY patient_count desc
LIMIT 10;

province_name	patient_count
Ontario	480
Nova Scotia	6
British Columbia	5
Alberta	5
Manitoba	3
Saskatchewan	1

For every admission, display the patient's full name, their admission diagnosis, and their physician's full name who diagnosed their problem.

%%sql
SELECT
  CONCAT(patients.first_name, ' ', patients.last_name) as patient_name,
  diagnosis,
  CONCAT(physicians.first_name,' ',physicians.last_name) as physician_name
FROM patients
  JOIN admissions ON admissions.patient_id = patients.patient_id
  JOIN physicians ON physicians.physician_id = admissions.attending_physician_id
LIMIT 10;

patient_name	diagnosis	physician_name
Dirk Lindley	Pain In Abdomen Left Side	Claude Walls
Annie Smart	Cervical Spinal Tumor	Claude Walls
Hannah MacLeod	Chlymidia	Claude Walls
Marvin Dredd	Scatica	Claude Walls
Cordelia Sugden	Active Labour	Claude Walls
Daniel Nelson	Fibromyalgia	Claude Walls
Naoto Stewart	Headache	Claude Walls
Maria Bluenight	Osteoarthritis	Claude Walls
Joyce Bosch	Total Hysterectomy	Claude Walls
Gala Littlefield	Undiagnosed Chest Pain	Claude Walls

Hard

Show all of the patients grouped into weight groups. Show the total amount of patients in each weight group. Order the list by the weight group decending.

For example, if they weight 100 to 109 they are placed in the 100 weight group, 110-119 = 110 weight group, etc.

%%sql
SELECT
  COUNT(*) AS patients_in_group,
  FLOOR(weight / 10) * 10 AS weight_group
FROM patients
GROUP BY weight_group
ORDER BY weight_group DESC
LIMIT 10;

patients_in_group	weight_group
3	140.0
8	130.0
23	120.0
45	110.0
57	100.0
38	90.0
52	80.0
71	70.0
87	60.0
40	50.0

Show patient_id, weight, height, isObese from the patients table.

Display isObese as a boolean 0 or 1.

Obese is defined as weight(kg)/(height(m)2) >= 30.

weight is in units kg.

height is in units cm.

%%sql
SELECT patient_id, weight, height, 
  (CASE 
      WHEN weight/(POWER(height/100.0,2)) >= 30 THEN
          1
      ELSE
          0
      END) AS isObese
FROM patients
LIMIT 10;

patient_id	weight	height	isobese
1	65	156	0
2	76	185	0
3	106	194	0
4	104	191	0
5	10	47	1
6	5	43	0
7	117	180	1
8	105	174	1
9	95	173	1
10	61	157	0

Show patient_id, first_name, last_name, and attending physician's specialty.

Show only the patients who has a diagnosis as 'Hypertension' and the physician's first name is 'Lisa'

Check patients, admissions, and physicians tables for required information.

%%sql
SELECT
  p.patient_id,
  p.first_name AS patient_first_name,
  p.last_name AS patient_last_name,
  ph.specialty AS attending_physician_specialty
FROM patients p
  JOIN admissions a ON a.patient_id = p.patient_id
  JOIN physicians ph ON ph.physician_id = a.attending_physician_id
WHERE
  ph.first_name = 'Lisa' and
  a.diagnosis = 'Hypertension';

patient_id	patient_first_name	patient_last_name	attending_physician_specialty
54	Molly	Jackson	Obstetrician/Gynecologist
324	Mary	Matthews	Obstetrician/Gynecologist

All patients who have gone through admissions, can see their medical documents on our site. Those patients are given a temporary password after their first admission. Show the patient_id and temp_password.

The password must be the following, in order:

1. patient_id
2. the numerical length of patient's last_name
3. year of patient's birth_date

%%sql
SELECT
  DISTINCT P.patient_id,
  CONCAT(
    P.patient_id,
    LENGTH(last_name),
    EXTRACT (YEAR from birth_date)
  ) AS temp_password
FROM patients P
  JOIN admissions A ON A.patient_id = P.patient_id
LIMIT 10;

patient_id	temp_password
244	24481986
64	6441957
155	15591989
47	4791979
339	33971986
40	4071953
9	971966
82	8271990
39	3982016
6	662017

Each admission costs $50 for patients without insurance, and $10 for patients with insurance. All patients with an even patient_id have insurance.

Give each patient a 'Yes' if they have insurance, and a 'No' if they don't have insurance. Add up the admission_total cost for each has_insurance group.

%%sql
SELECT 
CASE WHEN patient_id % 2 = 0 Then 
    'Yes'
ELSE 
    'No' 
END as has_insurance,
SUM(CASE WHEN patient_id % 2 = 0 Then 
    10
ELSE 
    50 
END) as cost_after_insurance
FROM admissions 
GROUP BY has_insurance;

has_insurance	cost_after_insurance
No	9850
Yes	2030

Show the provinces that has more patients identified as 'M' than 'F'. Must only show full province_name

%%sql
SELECT pr.province_name
FROM patients AS pa
  JOIN province_names AS pr ON pa.province_id = pr.province_id
GROUP BY pr.province_name
HAVING
  COUNT( CASE WHEN gender = 'M' THEN 1 END) > COUNT( CASE WHEN gender = 'F' THEN 1 END)
LIMIT 10;

province_name
Manitoba
Nova Scotia
Alberta
Ontario
Saskatchewan

Show the percent of patients that have 'M' as their gender. Round the answer to the nearest hundreth number and in percent form.

%%sql
SELECT
  round(100 * avg(CASE WHEN  gender = 'M' THEN 1 ELSE 0 END), 2) || '%' AS percent_of_male_patients
FROM
  patients;

percent_of_male_patients
54.60%

Extra

Show patients who borned in or after 2005.

%%sql
SELECT *
FROM patients
WHERE EXTRACT(YEAR from birth_date) >= 2005
LIMIT 10;

patient_id	first_name	last_name	gender	birth_date	city	province_id	allergies	height	weight
5	Charles	Wolfe	M	2017-11-19 00:00:00	Orillia	ON	Penicillin	47	10
6	Sue	Falcon	F	2017-09-30 00:00:00	Ajax	ON	Penicillin	43	5
30	Roderick	Payne	M	2015-03-22 00:00:00	Halifax	NS	None	58	17
32	Bertha	Crowley	F	2009-03-14 00:00:00	Delhi	ON	None	125	39
35	Anthony	Maxwell	M	2011-02-11 00:00:00	Oakville	ON	None	92	37
38	Kelly	Hamilton	F	2010-09-29 00:00:00	Stoney Creek	ON	Penicillin	84	36
39	Charles	Andonuts	M	2016-05-20 00:00:00	Hamilton	ON	None	62	15
43	Stone	Cutting	M	2010-09-24 00:00:00	Hamilton	ON	Codeine	110	42
45	Cross	Gordon	M	2009-03-20 00:00:00	Ancaster	ON	None	125	53
50	Phil	Beckett	M	2014-10-11 00:00:00	Ottawa	ON	None	83	26

Show last name of patients that ends with the letter 'A'

%%sql
SELECT
  last_name
FROM
  patients
WHERE
  last_name LIKE '%a'
LIMIT 10;

last_name
Baasha
Sharma
Leela
Kobayakawa
Riviera
Riviera
Provenza
Baasha
Scarpetta
Luca