Back to questions
Patient Support Analysis (Part 4) UnitedHealth SQL Interview Question
Patient Support Analysis (Part 4)
UnitedHealth SQL Interview Question
UnitedHealth Group (UHG) has a program called Advocate4Me, which allows policy holders (or, members) to call an advocate and receive support for their health care needs – whether that's claims and benefits support, drug coverage, pre- and post-authorisation, medical records, emergency assistance, or member portal services.
To analyze the performance of the program, write a query to determine the month-over-month growth rate specifically for long-calls. A long-call is defined as any call lasting more than 5 minutes (300 seconds).
Output the year and month in numerical format and chronological order, along with the growth percentage rounded to 1 decimal place.
Table:
| Column Name | Type |
|---|---|
| policy_holder_id | integer |
| case_id | varchar |
| call_category | varchar |
| call_date | timestamp |
| call_duration_secs | integer |
Example Input:
| policy_holder_id | case_id | call_category | call_date | call_duration_secs |
|---|---|---|---|---|
| 1 | f1d012f9-9d02-4966-a968-bf6c5bc9a9fe | emergency assistance | 04/13/2023 19:16:53 | 144 |
| 1 | 41ce8fb6-1ddd-4f50-ac31-07bfcce6aaab | authorisation | 05/25/2023 09:09:30 | 815 |
| 2 | 8471a3d4-6fc7-4bb2-9fc7-4583e3638a9e | emergency assistance | 03/09/2023 10:58:54 | 128 |
| 2 | 38208fae-bad0-49bf-99aa-7842ba2e37bc | benefits | 06/05/2023 07:35:43 | 619 |
| 3 | f0e7a8e3-df93-40f3-9b5e-fadff9ebe072 | provider network | 01/12/2023 04:53:41 | 483 |
| 3 | b72f91e6-c3f8-4358-a1f2-c9507e8dcba4 | member portal | 04/04/2023 20:03:22 | 275 |
| 3 | 3acbe22d-22b3-4144-954d-74c127bc49ea | benefits | 04/12/2023 00:05:29 | 329 |
| 3 | e32b61c2-a90d-4371-a5ee-6bc44fa49bbd | benefits | 05/25/2023 06:07:41 | 512 |
| 3 | 6099f469-b5d6-4447-9acf-d936355eae7c | emergency assistance | 06/11/2023 12:04:21 | 33 |
Example Output:
| yr | mth | long_calls_growth_pct |
|---|---|---|
| 2023 | 1 | NULL |
| 2023 | 2 | 0.0 |
| 2023 | 2 | 100.0 |
| 2023 | 4 | 200.0 |
| 2023 | 5 | -33.3 |
| 2023 | 6 | 0.0 |
Explanation:
Call counts: Jan - 1 call; Feb - 0 call; Mar - 1 call; Apr - 3 calls; May - 2 calls; Jun - 2 calls
- January: With no previous month's call information, the growth percentage is shown as NULL.
- February: The absence of calls compared to January results in a 0.0% growth rate.
- March: Witnessing an increase of 1 call compared to February, the growth percentage is +100.0%.
- April: Experiencing a surge of 3 calls compared to March, the growth percentage is +200.0%.
- May: With a decrease of 1 call compared to April, the growth percentage is -33.3%.
- June: Maintaining the same number of calls as May, the growth percentage remains at 0.0%.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Filter for long calls
We start by filtering for call duration exceeding 300 seconds using the field.
Step 2: Extract monthly data
Next, we extract the year and month information from the field using the function. To prevent confusion with SQL date parts, it's advisable to refrain from employing aliases like and .
Step 3: Calculate monthly call counts
To compute the total number of long calls for each month, we can use the aggregate function. Additionally, we utilize the window function to retrieve the previous month's call count, facilitating the calculation of the month-over-month growth rate.
Then, we incorporate the and functions into the window function:
Here's the output for the first 5 months:
| yr | mth | curr_mth_calls | prev_mth_calls |
|---|---|---|---|
| 2023 | 1 | 23 | NULL |
| 2023 | 2 | 27 | 23 |
| 2023 | 3 | 20 | 27 |
| 2023 | 4 | 21 | 20 |
| 2023 | 5 | 24 | 21 |
Let's analyze the output:
- January 2023: As it marks the beginning of the year, there are no previous month's call count indicated by a NULL value in the field.
- February 2023: There were 27 calls in February with 23 calls carried over from January 2023.
- March 2023: The number of calls decreased to 20 compared to 27 carried over from February 2023
- April 2023: There was a slight increase to 21 calls from 20 carried over from March 2023.
- May 2023: The number of calls rose again to 24 from 21 carried over from April 2023.
The window function retrieves the previous month's call count and carries it over to the current month, enabling us the calculation of the month-on-month growth rate.
Step 4: Compute growth rate
Applying the formula to calculate the month-over-month growth percentage:
Month-over-month Growth Percentage = (Current Month's Calls - Previous Month's Calls) / Previous Month's Calls x 100
We wrap the previous query in Step 3 in a CTE named . Finally, we round the results to one decimal place and sort in chronological order.