Using Snowflake Window Functions to Identify Gaps in Prescription Supplies

Managing prescription medications is crucial for patient health, especially for those with chronic conditions. Ensuring continuous medication supply is vital to avoid health complications. In this article, we will explore how to use Snowflake Window Functions to identify patients who have more than a 7-day gap in their prescription supplies.

Setting Up the Data

We start with a sample dataset of pharmacy claims. The table pharm_claims includes information on patient prescriptions, such as service date, National Drug Code (NDC) and days supply.

CREATE DATABASE if not exists pharmacy;

USE pharmacy;

drop table if exists pharm_claims;

CREATE TABLE if not exists pharm_claims (
    patient_key VARCHAR(100) PRIMARY KEY,
    service_date VARCHAR(100),
    ndc VARCHAR(100),
    days_supply INTEGER
);

INSERT INTO pharm_claims (patient_key, service_date, ndc, days_supply) VALUES
('P001', '2024-01-01', '1234567890', 30),
('P001', '2024-02-05', '1234567890', 30),
('P001', '2024-03-10', '1234567890', 30),
('P002', '2024-01-15', '0987654321', 60),
('P002', '2024-04-20', '0987654321', 30),
('P002', '2024-05-25', '0987654321', 30),
('P003', '2024-02-01', '1122334455', 90),
('P003', '2024-05-01', '1122334455', 30);

Identifying Gaps in Prescription Supply

To find gaps in prescription supply, we need to calculate the end date of each prescription by adding the days supply to the service date. Then, we will compare each prescription’s end date with the next prescription’s service date for the same patient and NDC code.

Here’s how we do it using Snowflake Window Functions:

WITH patient_data AS (
    SELECT 
        patient_key,
        service_date,
        ndc,
        days_supply,
        DATEADD(day, days_supply, service_date) AS end_date,
        LAG(DATEADD(day, days_supply, service_date)) OVER (PARTITION BY patient_key, ndc ORDER BY service_date) AS previous_end_date
    FROM pharm_claims
)
SELECT 
    patient_key,
    service_date,
    ndc,
    days_supply,
    end_date,
    previous_end_date,
    DATEDIFF(day, previous_end_date, service_date) AS gap_in_days
FROM patient_data
WHERE previous_end_date IS NOT NULL
ORDER BY patient_key, service_date;

Explanation:

1. Calculate the End Date: We use DATEADD to add the days_supply to the service_date, creating the end_date.
2. Get the Previous End Date: We use the LAG function to get the end date of the previous prescription for the same patient and NDC code.
3. Calculate the Gap: We use DATEDIFF to calculate the number of days between the previous_end_date and the service_date of the current prescription.
4. Filter for Gaps Greater Than 7 Days: We filter the results to show only those rows where the gap is greater than 7 days.

Results

The query will return a list of patients who have more than a 7-day gap in their prescription supply for a specific medication.

+-------------+--------------+------------+-------------+------------+-------------------+-------------+
| PATIENT_KEY | SERVICE_DATE | NDC        | DAYS_SUPPLY | END_DATE   | PREVIOUS_END_DATE | GAP_IN_DAYS |
|-------------+--------------+------------+-------------+------------+-------------------+-------------|
| P002        | 2024-04-20   | 0987654321 |          30 | 2024-05-20 | 2024-03-15        |          36 |
+-------------+--------------+------------+-------------+------------+-------------------+-------------+

Conclusion

Using Snowflake Window Functions, we can efficiently identify gaps in prescription supplies, ensuring better medication management for patients. By monitoring these gaps, healthcare providers can take proactive measures to prevent interruptions in patient care.

Feel free to adapt this approach to your specific use case and data requirements. Happy querying!