Medical Image Analytics

Objective

Build an analytics system for medical imaging operations, tracking study volumes, turnaround times, quality metrics, and resource utilization.

Step 1: Create Studies Analytics Table

Store study-level metrics.

CREATE TABLE study_analytics (
    id INTEGER PRIMARY KEY,
    study_uid VARCHAR(100) UNIQUE,
    study_date DATE NOT NULL,
    study_time TIME,
    modality VARCHAR(20),
    body_region VARCHAR(100),
    exam_code VARCHAR(50),
    patient_type VARCHAR(50),
    priority VARCHAR(20),
    institution VARCHAR(200),
    department VARCHAR(100),
    referring_physician VARCHAR(200),
    performing_technologist VARCHAR(200),
    radiologist VARCHAR(200),
    image_count INTEGER,
    study_size_mb DECIMAL(10, 2),
    acquisition_duration_min INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Report Metrics Table

Track reporting performance.

CREATE TABLE report_metrics (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    report_status VARCHAR(50),
    dictation_start TIMESTAMP,
    dictation_end TIMESTAMP,
    preliminary_time TIMESTAMP,
    final_time TIMESTAMP,
    addendum_time TIMESTAMP,
    dictation_duration_min INTEGER,
    turnaround_hours DECIMAL(8, 2),
    word_count INTEGER,
    critical_finding BOOLEAN DEFAULT FALSE,
    communication_time_min INTEGER,
    FOREIGN KEY (study_id) REFERENCES study_analytics(id)
);

Step 3: Create Quality Metrics Table

Store quality indicators.

CREATE TABLE quality_metrics (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    image_quality_score DECIMAL(3, 2),
    motion_artifact BOOLEAN DEFAULT FALSE,
    repeat_required BOOLEAN DEFAULT FALSE,
    repeat_reason VARCHAR(100),
    contrast_issue BOOLEAN DEFAULT FALSE,
    protocol_deviation BOOLEAN DEFAULT FALSE,
    deviation_description TEXT,
    peer_reviewed BOOLEAN DEFAULT FALSE,
    peer_review_score DECIMAL(3, 2),
    discrepancy_found BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (study_id) REFERENCES study_analytics(id)
);

Step 4: Create Equipment Analytics Table

Track scanner utilization.

CREATE TABLE equipment_analytics (
    id INTEGER PRIMARY KEY,
    equipment_id VARCHAR(50),
    equipment_name VARCHAR(200),
    modality VARCHAR(20),
    location VARCHAR(100),
    record_date DATE,
    studies_performed INTEGER DEFAULT 0,
    total_scan_time_min INTEGER DEFAULT 0,
    uptime_hours DECIMAL(5, 2),
    downtime_hours DECIMAL(5, 2),
    maintenance_events INTEGER DEFAULT 0,
    utilization_percent DECIMAL(5, 2)
);

Step 5: Create Radiation Dose Table

Track CT radiation exposure.

CREATE TABLE radiation_dose (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    protocol_name VARCHAR(100),
    ctdi_vol DECIMAL(8, 2),
    dlp DECIMAL(10, 2),
    effective_dose_msv DECIMAL(8, 4),
    patient_size VARCHAR(20),
    dose_alert BOOLEAN DEFAULT FALSE,
    dose_alert_reason VARCHAR(200),
    FOREIGN KEY (study_id) REFERENCES study_analytics(id)
);

Step 6: Insert Study Analytics Data

Add sample analytics.

INSERT INTO study_analytics (id, study_uid, study_date, study_time, modality, body_region, exam_code, patient_type, priority, institution, department, referring_physician, performing_technologist, radiologist, image_count, study_size_mb, acquisition_duration_min) VALUES
    (1, '1.2.840.001', '2024-01-15', '08:00:00', 'CT', 'CHEST', 'CT-CHEST-CON', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Anderson', 'Tech. Wilson', 'Dr. Miller', 450, 285.5, 15),
    (2, '1.2.840.002', '2024-01-15', '08:30:00', 'MR', 'BRAIN', 'MR-BRAIN-WO', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Thompson', 'Tech. Brown', 'Dr. Garcia', 320, 425.0, 35),
    (3, '1.2.840.003', '2024-01-15', '09:00:00', 'CT', 'ABDOMEN', 'CT-ABD-PEL', 'inpatient', 'stat', 'Main Hospital', 'ED', 'Dr. White', 'Tech. Davis', 'Dr. Miller', 520, 345.2, 12),
    (4, '1.2.840.004', '2024-01-15', '09:30:00', 'XR', 'CHEST', 'XR-CHEST-2V', 'emergency', 'stat', 'Main Hospital', 'ED', 'Dr. Lee', 'Tech. Wilson', 'Dr. Chen', 2, 15.0, 5),
    (5, '1.2.840.005', '2024-01-15', '10:00:00', 'US', 'ABDOMEN', 'US-ABD-COMP', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Garcia', 'Tech. Martinez', 'Dr. Garcia', 45, 28.5, 25),
    (6, '1.2.840.006', '2024-01-15', '10:30:00', 'CT', 'HEAD', 'CT-HEAD-WO', 'emergency', 'stat', 'Main Hospital', 'ED', 'Dr. White', 'Tech. Davis', 'Dr. Miller', 80, 52.0, 8),
    (7, '1.2.840.007', '2024-01-15', '11:00:00', 'MR', 'SPINE', 'MR-LSPINE', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Anderson', 'Tech. Brown', 'Dr. Garcia', 280, 380.0, 40),
    (8, '1.2.840.008', '2024-01-15', '11:30:00', 'CT', 'CHEST', 'CT-CHEST-PE', 'inpatient', 'stat', 'Main Hospital', 'ICU', 'Dr. Thompson', 'Tech. Wilson', 'Dr. Miller', 380, 245.8, 10),
    (9, '1.2.840.009', '2024-01-15', '12:00:00', 'XR', 'HAND', 'XR-HAND-3V', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Lee', 'Tech. Martinez', 'Dr. Chen', 3, 8.5, 8),
    (10, '1.2.840.010', '2024-01-15', '12:30:00', 'CT', 'ABDOMEN', 'CT-ABD-PEL', 'outpatient', 'routine', 'Main Hospital', 'Radiology', 'Dr. Garcia', 'Tech. Davis', 'Dr. Miller', 490, 325.0, 15);

Step 7: Insert Report Metrics

Add reporting performance data.

INSERT INTO report_metrics (id, study_id, report_status, dictation_start, dictation_end, preliminary_time, final_time, dictation_duration_min, turnaround_hours, word_count, critical_finding, communication_time_min) VALUES
    (1, 1, 'final', '2024-01-15 09:00:00', '2024-01-15 09:12:00', '2024-01-15 09:15:00', '2024-01-15 11:00:00', 12, 3.0, 285, FALSE, NULL),
    (2, 2, 'final', '2024-01-15 10:00:00', '2024-01-15 10:18:00', '2024-01-15 10:20:00', '2024-01-15 13:00:00', 18, 4.5, 320, FALSE, NULL),
    (3, 3, 'final', '2024-01-15 09:20:00', '2024-01-15 09:28:00', '2024-01-15 09:30:00', '2024-01-15 10:00:00', 8, 1.0, 245, FALSE, NULL),
    (4, 4, 'final', '2024-01-15 09:40:00', '2024-01-15 09:45:00', '2024-01-15 09:46:00', '2024-01-15 10:30:00', 5, 1.0, 125, FALSE, NULL),
    (5, 5, 'final', '2024-01-15 11:00:00', '2024-01-15 11:15:00', '2024-01-15 11:18:00', '2024-01-15 14:00:00', 15, 4.0, 350, FALSE, NULL),
    (6, 6, 'final', '2024-01-15 10:45:00', '2024-01-15 10:50:00', '2024-01-15 10:52:00', '2024-01-15 11:15:00', 5, 0.75, 180, TRUE, 3),
    (7, 7, 'final', '2024-01-15 12:30:00', '2024-01-15 12:50:00', '2024-01-15 12:55:00', '2024-01-15 15:00:00', 20, 4.5, 425, FALSE, NULL),
    (8, 8, 'final', '2024-01-15 11:45:00', '2024-01-15 11:52:00', '2024-01-15 11:55:00', '2024-01-15 12:30:00', 7, 1.0, 210, FALSE, NULL),
    (9, 9, 'final', '2024-01-15 12:15:00', '2024-01-15 12:20:00', '2024-01-15 12:22:00', '2024-01-15 14:00:00', 5, 1.75, 95, FALSE, NULL),
    (10, 10, 'final', '2024-01-15 13:30:00', '2024-01-15 13:45:00', '2024-01-15 13:48:00', '2024-01-15 16:00:00', 15, 3.5, 295, FALSE, NULL);

Step 8: Insert Quality Metrics

Add quality indicators.

INSERT INTO quality_metrics (id, study_id, image_quality_score, motion_artifact, repeat_required, repeat_reason, contrast_issue, protocol_deviation, peer_reviewed, peer_review_score, discrepancy_found) VALUES
    (1, 1, 0.95, FALSE, FALSE, NULL, FALSE, FALSE, TRUE, 0.98, FALSE),
    (2, 2, 0.88, TRUE, FALSE, NULL, FALSE, FALSE, FALSE, NULL, FALSE),
    (3, 3, 0.92, FALSE, FALSE, NULL, FALSE, FALSE, TRUE, 0.95, FALSE),
    (4, 4, 0.90, FALSE, FALSE, NULL, FALSE, FALSE, FALSE, NULL, FALSE),
    (5, 5, 0.85, FALSE, FALSE, NULL, FALSE, FALSE, FALSE, NULL, FALSE),
    (6, 6, 0.93, FALSE, FALSE, NULL, FALSE, FALSE, TRUE, 1.00, FALSE),
    (7, 7, 0.78, TRUE, TRUE, 'Motion artifact - patient moved', FALSE, FALSE, FALSE, NULL, FALSE),
    (8, 8, 0.96, FALSE, FALSE, NULL, FALSE, FALSE, TRUE, 0.92, TRUE),
    (9, 9, 0.91, FALSE, FALSE, NULL, FALSE, FALSE, FALSE, NULL, FALSE),
    (10, 10, 0.94, FALSE, FALSE, NULL, FALSE, FALSE, FALSE, NULL, FALSE);

Step 9: Insert Equipment Analytics

Add scanner utilization data.

INSERT INTO equipment_analytics (id, equipment_id, equipment_name, modality, location, record_date, studies_performed, total_scan_time_min, uptime_hours, downtime_hours, maintenance_events, utilization_percent) VALUES
    (1, 'CT-001', 'GE Revolution CT', 'CT', 'Main Radiology', '2024-01-15', 25, 300, 10.0, 0.5, 0, 85.0),
    (2, 'CT-002', 'Siemens Force CT', 'CT', 'ED', '2024-01-15', 18, 180, 8.0, 1.0, 1, 75.0),
    (3, 'MR-001', 'Siemens Prisma 3T', 'MR', 'Main Radiology', '2024-01-15', 12, 420, 10.0, 0.0, 0, 70.0),
    (4, 'MR-002', 'GE Signa 1.5T', 'MR', 'Outpatient Center', '2024-01-15', 10, 350, 9.0, 1.0, 0, 65.0),
    (5, 'XR-001', 'GE Revolution XR', 'XR', 'Main Radiology', '2024-01-15', 45, 225, 10.0, 0.0, 0, 60.0),
    (6, 'US-001', 'Philips EPIQ Elite', 'US', 'Main Radiology', '2024-01-15', 20, 500, 10.0, 0.0, 0, 83.0);

Step 10: Insert Radiation Dose Data

Track CT dose metrics.

INSERT INTO radiation_dose (id, study_id, protocol_name, ctdi_vol, dlp, effective_dose_msv, patient_size, dose_alert, dose_alert_reason) VALUES
    (1, 1, 'CT Chest Standard', 12.5, 450.0, 6.75, 'medium', FALSE, NULL),
    (2, 3, 'CT Abdomen Pelvis', 15.2, 680.0, 10.20, 'large', FALSE, NULL),
    (3, 6, 'CT Head Stroke', 58.0, 980.0, 2.10, 'medium', FALSE, NULL),
    (4, 8, 'CT Chest PE', 8.5, 320.0, 4.80, 'small', FALSE, NULL),
    (5, 10, 'CT Abdomen Pelvis', 18.5, 820.0, 12.30, 'large', TRUE, 'Above diagnostic reference level');

Step 11: Daily Volume Report

Analyze daily study counts.

SELECT
    modality,
    COUNT(*) as study_count,
    SUM(image_count) as total_images,
    SUM(study_size_mb) / 1024 as total_gb,
    AVG(acquisition_duration_min) as avg_scan_min
FROM study_analytics
WHERE study_date = '2024-01-15'
GROUP BY modality
ORDER BY study_count DESC;

Step 12: Turnaround Time Analysis

Measure reporting efficiency.

SELECT
    sa.modality,
    sa.priority,
    COUNT(*) as studies,
    AVG(rm.turnaround_hours) as avg_tat_hours,
    MIN(rm.turnaround_hours) as min_tat_hours,
    MAX(rm.turnaround_hours) as max_tat_hours
FROM study_analytics sa
INNER JOIN report_metrics rm ON sa.id = rm.study_id
WHERE rm.report_status = 'final'
GROUP BY sa.modality, sa.priority
ORDER BY sa.modality, sa.priority;

Step 13: Radiologist Productivity

Analyze individual performance.

SELECT
    sa.radiologist,
    COUNT(*) as studies_read,
    AVG(rm.dictation_duration_min) as avg_dictation_min,
    AVG(rm.turnaround_hours) as avg_tat_hours,
    SUM(rm.word_count) as total_words,
    COUNT(CASE WHEN rm.critical_finding THEN 1 END) as critical_findings
FROM study_analytics sa
INNER JOIN report_metrics rm ON sa.id = rm.study_id
GROUP BY sa.radiologist
ORDER BY studies_read DESC;

Step 14: Equipment Utilization Report

Track scanner performance.

SELECT
    equipment_name,
    modality,
    location,
    studies_performed,
    total_scan_time_min,
    uptime_hours,
    downtime_hours,
    utilization_percent || '%' as utilization,
    CASE
        WHEN utilization_percent >= 80 THEN 'High'
        WHEN utilization_percent >= 60 THEN 'Medium'
        ELSE 'Low'
    END as utilization_level
FROM equipment_analytics
WHERE record_date = '2024-01-15'
ORDER BY utilization_percent DESC;

Step 15: Quality Dashboard

Summarize quality metrics.

SELECT
    sa.modality,
    COUNT(*) as total_studies,
    AVG(qm.image_quality_score) as avg_quality_score,
    COUNT(CASE WHEN qm.motion_artifact THEN 1 END) as motion_artifacts,
    COUNT(CASE WHEN qm.repeat_required THEN 1 END) as repeats,
    COUNT(CASE WHEN qm.peer_reviewed THEN 1 END) as peer_reviewed,
    COUNT(CASE WHEN qm.discrepancy_found THEN 1 END) as discrepancies,
    CAST(COUNT(CASE WHEN qm.repeat_required THEN 1 END) AS DECIMAL) / COUNT(*) * 100 as repeat_rate_percent
FROM study_analytics sa
INNER JOIN quality_metrics qm ON sa.id = qm.study_id
GROUP BY sa.modality
ORDER BY sa.modality;

Step 16: Radiation Dose Summary

Monitor dose levels.

SELECT
    rd.protocol_name,
    COUNT(*) as exams,
    AVG(rd.ctdi_vol) as avg_ctdi,
    AVG(rd.dlp) as avg_dlp,
    AVG(rd.effective_dose_msv) as avg_eff_dose_msv,
    COUNT(CASE WHEN rd.dose_alert THEN 1 END) as dose_alerts
FROM radiation_dose rd
GROUP BY rd.protocol_name
ORDER BY avg_eff_dose_msv DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS radiation_dose;
DROP TABLE IF EXISTS quality_metrics;
DROP TABLE IF EXISTS report_metrics;
DROP TABLE IF EXISTS equipment_analytics;
DROP TABLE IF EXISTS study_analytics;

Expected Outcomes

• Volume metrics tracked
• TAT measured
• Quality monitored
• Equipment utilized
• Dose tracked

Key Performance Indicators

Key Concepts Learned

• Operational analytics
• Turnaround tracking
• Quality measurement
• Equipment utilization
• Dose monitoring