sinusitis_athena.sql

-- Purpose: Process data for sinusitis study using Amazon Athena
-- Author:  Gary A. Stafford (January 2022)

CREATE TABLE "sinusitis_athena" WITH (
	format = 'Parquet',
	write_compression = 'SNAPPY',
	external_location = 's3://databrew-demo-111222333444-us-east-1/sinusitis_athena/',
    bucketed_by = ARRAY['patient'],
    bucket_count = 1
) AS
SELECT DISTINCT
    patient,
	code,
	description,
	date_diff(
		'day',
		date(substr(birthdate, 1, 10)),
		date(substr(start, 1, 10))
	) as condition_age,
	marital,
	race,
	ethnicity,
	gender
FROM conditions AS c,
	patients AS p
WHERE c.patient = p.id
	AND gender <> ''
	AND ethnicity <> ''
	AND race <> ''
	AND marital <> ''
	AND description LIKE '%sinusitis%'
ORDER BY patient, code;