DB Exercises
0
← Exercises/
Aggregations•9 of 12
PrevNext
AggregationsIntermediatePRO

Hospital Formulary Drug Dispensation & Expenditure

Scenario

Hospital pharmacy leadership is auditing pharmaceutical procurement costs across inpatient units. They need to aggregate expenditures per drug classification to pinpoint medications driving high clinical costs.

Task
Group records in `pharmacy_dispensations` by `drug_category`. Calculate: 1. `drug_category` 2. `distinct_patients`: Count of unique `patient_id` recipients 3. `total_units_dispensed`: Sum of `units_dispensed` 4. `total_expenditure`: Sum of (`units_dispensed * unit_cost`) Order results by `total_expenditure` descending.
Expected Output Format

Summary of pharmaceutical spending per drug category sorted by total cost.

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 “Hospital Formulary Drug Dispensation & Expenditure” SQL Problem

Business Scenario

Hospital pharmacy leadership is auditing pharmaceutical procurement costs across inpatient units. They need to aggregate expenditures per drug classification to pinpoint medications driving high clinical costs.

Expected Output & Criteria

Summary of pharmaceutical spending per drug category sorted by total cost.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE pharmacy_dispensations (
        dispense_id INT PRIMARY KEY,
        patient_id INT NOT NULL,
        drug_category VARCHAR(50) NOT NULL,
        units_dispensed INT NOT NULL,
        unit_cost NUMERIC(8, 2) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYSUMCOUNTORDER BY

Want more Aggregations practice?

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

View Aggregations Track→