DB Exercises
0
← Exercises/
Business Analytics•12 of 14
PrevNext
Business AnalyticsAdvancedPRO

Clinical Pathology Antibiogram Susceptibility Matrix

Scenario

Hospital infection control committees construct an annual antibiogram matrix to guide empiric antimicrobial therapy: for each bacterial pathogen, compute the percentage of isolate samples tested that were susceptible.

Task
Group culture sensitivity records in `antibiogram_isolates` by `pathogen_name`. Calculate: 1. `pathogen_name` 2. `total_isolates`: Total samples tested 3. `susceptible_count`: Count where `interpretation = 'Susceptible'` 4. `susceptibility_pct`: Percentage of isolates susceptible: `ROUND(100.0 * COUNT(*) FILTER (WHERE interpretation = 'Susceptible') / COUNT(*), 1)` Order results by `susceptibility_pct` descending, then by `pathogen_name` ascending.
Expected Output Format

Summary of pathogen isolates and susceptibility percentage.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Clinical Pathology Antibiogram Susceptibility Matrix” SQL Problem

Business Scenario

Hospital infection control committees construct an annual antibiogram matrix to guide empiric antimicrobial therapy: for each bacterial pathogen, compute the percentage of isolate samples tested that were susceptible.

Expected Output & Criteria

Summary of pathogen isolates and susceptibility percentage.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE antibiogram_isolates (
        sample_id INT PRIMARY KEY,
        pathogen_name VARCHAR(80) NOT NULL,
        antibiotic_tested VARCHAR(50) NOT NULL,
        interpretation VARCHAR(20) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYCOUNTROUND

Want more Business Analytics practice?

Explore the full syllabus of 14 exercises, syntax guides, and interview pitfalls.

View Business Analytics Track→