SQL Exercises

This repo contains training excercises for writing SQL queries using claims databases.

The following exercises are intended to be completed in order. There are many different correct solutions to each exercise. The solutions that are provided in this repo were written to be as readable, efficient, and portable as possible.

Exercise #1

First, we will specifiy a population of new lisinopril users. Count the number of UHC Medicare members that meet all of the following criteria:

• Were continuously enrolled in all of 2017 in a Medicare Advantage with Part D (MAPD) plan
• Had at least one filled prescription for lisinopril during 2018
• Did not have a filled prescription for lisinopril during 2017 Note: This is a simplified method to determine new lisinopril users. An improved method will specified in a later exercise.

Exercise #2

To study a member's health care utilization after starting lisinopril, we also require a sufficient amount of continuous enrollment after starting lisinopril. Using the population specified in exercise #1, how many of these members also had 6 months of MAPD continuous enrollment after the first month they filled a prescription for lisinopril. (Note: These 6 months can extend into 2019.)

Exercise #3

Now we will summarize the monthly trend in health care utilization variables. Using the population specified in exercise #2, create a table with one row per month that contains:

• average per member total allowed amount (medical only)
• average per member total visit count

in months 0 through 6, with month 0 being the first month of lisinopril.

Exercise #4

Using the population specified in exercise #2, create a table with one row per month that contains:

• proportion of members with at least one hospital admission
• proportion of members that filled at least one additional prescription for lisinopril in months 0 through 6, with month 0 being the first month of lisinopril.

Exercise #5

Using the population specified in exercise #2 and the time period of months 0 through 6:

• Create a table that counts the number of members that had each count of lisinopril scripts. (This table should have one row per value of script count and be ordered by script count.)
• Create a table that counts the number of members that had each count of lisinopril days supplied. (This table should have one row per value of days supplied per script and be ordered by days supplied.)

Exercise #6

Using the population specified in exercise #2 and the time period of months 0 through 6, calculate the average adherence rate to lisinopril. Define adherence rate as the proportion of days that are covered by days supplied of lisinopril. (Note: This is different than total days supplied / total days. This is because days supplied for scripts near the end of month 6 will cover days outside time period.)

Exercise #7

What are the most common medical conditions among lisinopril users? Using the population specified in exercise #2 and the time period of months 0 through 6, for each ICD-10 first-3-digit diagnosis category, count the number of members that had at least one primary diagnosis. Create a table that is ordered by the most prevalent diagnosis category and include the category description.