Demo 3: Complex SQL Queries¶

%pip install jupysql duckdb-engine pandas polars --quiet

from sqlalchemy import create_engine
%load_ext sql

# Configure SQL magic for better output
%config SqlMagic.autopandas = True
%config SqlMagic.autocommit=True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False

# Connect to DuckDB
# Execute SQL queries with pandas' `read_sql` function
# results_df = pd.read_sql("SELECT * FROM demographics LIMIT 5", engine)
engine = create_engine('duckdb:///:memory:')

# Use this engine for jupysql
%sql engine

# NOTE: memory db's are not shared between connections
# If using only jupysql, you can use the following:
# %sql duckdb:///:memory:

-- Clean up any existing tables
DROP TABLE IF EXISTS questionnaire;
DROP TABLE IF EXISTS laboratory;
DROP TABLE IF EXISTS examination;
DROP TABLE IF EXISTS demographics;

-- Load demographics
CREATE TABLE demographics AS
SELECT * FROM read_csv_auto('data/demographics.csv');

-- Load examination
CREATE TABLE examination AS
SELECT * FROM read_csv_auto('data/examination.csv');

-- Load laboratory
CREATE TABLE laboratory AS
SELECT * FROM read_csv_auto('data/labs.csv');

-- Load questionnaire
CREATE TABLE questionnaire AS
SELECT * FROM read_csv_auto('data/questionnaire.csv');

-- Calculate BMI categories using CTE
WITH bmi_categories AS (
    SELECT 
        d.SEQN,
        d.RIDAGEYR,
        d.RIAGENDR,
        e.BMXBMI,
        CASE
            WHEN e.BMXBMI < 18.5 THEN 'Underweight'
            WHEN e.BMXBMI < 25 THEN 'Normal'
            WHEN e.BMXBMI < 30 THEN 'Overweight'
            ELSE 'Obese'
        END AS bmi_category
    FROM demographics d
    JOIN examination e ON d.SEQN = e.SEQN
)
SELECT 
    RIAGENDR,
    bmi_category,
    COUNT(*) AS count,
    AVG(RIDAGEYR) AS avg_age
FROM bmi_categories
GROUP BY RIAGENDR, bmi_category
ORDER BY RIAGENDR, bmi_category;

-- Calculate running average of BMI by age
SELECT 
    SEQN,
    RIDAGEYR,
    RIAGENDR,
    BMXBMI,
    AVG(BMXBMI) OVER (PARTITION BY RIAGENDR ORDER BY RIDAGEYR) AS running_avg_bmi
FROM (
    SELECT d.SEQN, d.RIDAGEYR, d.RIAGENDR, e.BMXBMI
    FROM demographics d
    JOIN examination e ON d.SEQN = e.SEQN
) AS combined
ORDER BY RIAGENDR, RIDAGEYR;

-- INNER JOIN: Get participants with complete data
SELECT d.SEQN, d.RIDAGEYR, d.RIAGENDR, e.BMXBMI, l.LBXTC
FROM demographics d
INNER JOIN examination e ON d.SEQN = e.SEQN
INNER JOIN laboratory l ON d.SEQN = l.SEQN
WHERE e.BMXBMI IS NOT NULL AND l.LBXTC IS NOT NULL
LIMIT 10;

-- LEFT JOIN: Get all participants with available data
SELECT d.SEQN, d.RIDAGEYR, d.RIAGENDR, e.BMXBMI, l.LBXTC
FROM demographics d
LEFT JOIN examination e ON d.SEQN = e.SEQN
LEFT JOIN laboratory l ON d.SEQN = l.SEQN
LIMIT 10;

-- Find participants with above average BMI
SELECT SEQN, RIDAGEYR, RIAGENDR, BMXBMI
FROM (
    SELECT d.SEQN, d.RIDAGEYR, d.RIAGENDR, e.BMXBMI
    FROM demographics d
    JOIN examination e ON d.SEQN = e.SEQN
) AS combined
WHERE BMXBMI > (SELECT AVG(BMXBMI) FROM examination WHERE BMXBMI IS NOT NULL)
ORDER BY BMXBMI DESC
LIMIT 10;

-- Use appropriate indexes
CREATE INDEX idx_demographics_seqn ON demographics(SEQN);
CREATE INDEX idx_examination_seqn ON examination(SEQN);
CREATE INDEX idx_laboratory_seqn ON laboratory(SEQN);

-- Create a view for health summary
-- NOTE: DuckDB does not support materialized views
CREATE VIEW health_summary AS
SELECT 
    d.SEQN,
    d.RIDAGEYR,
    d.RIAGENDR,
    e.BMXBMI,
    l.LBXTC,
    q.DIQ010
FROM demographics d
LEFT JOIN examination e ON d.SEQN = e.SEQN
LEFT JOIN laboratory l ON d.SEQN = l.SEQN
LEFT JOIN questionnaire q ON d.SEQN = q.SEQN;

-- Query the view
-- NOTE: This is done on the fly by querying the underlying tables
SELECT * FROM health_summary LIMIT 5;