Back to questions
Patient Support Analysis (Part 4) [UnitedHealth SQL Interview Question]
UnitedHealth Group has a program called Advocate4Me, which allows members to call an advocate and receive support for their health care needs – whether that's behavioural, clinical, well-being, health care financing, benefits, claims or pharmacy help.
A long-call is categorised as any call that lasts more than 5 minutes (300 seconds). What's the month-over-month growth of long-calls?
Output the year, month (both in numerical and chronological order) and growth percentage rounded to 1 decimal place.
Table:
| Column Name | Type |
|---|---|
| policy_holder_id | integer |
| case_id | varchar |
| call_category | varchar |
| call_received | timestamp |
| call_duration_secs | integer |
| original_order | integer |
Example Input:
| policy_holder_id | case_id | call_category | call_received | call_duration_secs | original_order |
|---|---|---|---|---|---|
| 50986511 | b274-c8f0-4d5c-8704 | 2022-01-28T09:46:00 | 252 | 456 | |
| 54026568 | 405a-b9be-45c2-b311 | n/a | 2022-01-29T16:19:00 | 397 | 217 |
| 54026568 | c4cc-fd40-4780-8a53 | benefits | 2022-01-30T08:18:00 | 320 | 134 |
| 54026568 | 81e8-6abf-425b-add2 | n/a | 2022-02-20T17:26:00 | 1324 | 83 |
| 54475101 | 5919-b9c2-49a5-8091 | 2022-02-24T18:07:00 | 206 | 498 | |
| 54624612 | a17f-a415-4727-9a3f | benefits | 2022-02-27T10:56:00 | 435 | 19 |
| 53777383 | dfa9-e5a7-4a9b-a756 | benefits | 2022-03-19T00:10:00 | 318 | 69 |
| 52880317 | cf00-56c4-4e76-963a | claims | 2022-03-21T01:12:00 | 340 | 254 |
| 52680969 | 0c3c-7b87-489a-9857 | 2022-03-21T14:00:00 | 310 | 213 | |
| 54574775 | ca73-bf99-46b2-a79b | billing | 2022-04-18T14:09:00 | 181 | 312 |
| 51435044 | 6546-61b4-4a05-9a5e | 2022-04-18T21:58:00 | 354 | 439 | |
| 52780643 | e35a-a7c2-4718-a65d | n/a | 2022-05-06T14:31:00 | 318 | 186 |
| 54026568 | 61ac-eee7-42fa-a674 | 2022-05-07T01:27:00 | 404 | 341 | |
| 54674449 | 3d9d-e6e2-49d5-a1a0 | billing | 2022-05-09T11:00:00 | 107 | 450 |
| 54026568 | c516-0063-4b8f-aa74 | 2022-05-13T01:06:00 | 404 | 270 |
Example Output:
| yr | mth | growth_pct |
|---|---|---|
| 2022 | 1 | 0 |
| 2022 | 2 | 0 |
| 2022 | 3 | 50.0 |
| 2022 | 4 | -66.7 |
| 2022 | 5 | 200.0 |
Explanation:
Call counts: Jan - 2 calls; Feb - 2 calls; Mar - 3 calls; Apr - 1 call; May - 3 calls
- In January, the percentage is 0% as there is no previous month's call info.
- In February, the percentage is 0% as there is no increase/decrease from January.
- In March, the growth is +50.0% as there is an increase of 1 call from February.
- In April, drop by 66.7% as there is a reduction of 2 calls from March.
- Finally, in May, there were 3 calls thus a 200.0% growth from April.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Goal: Find the month-on-month (m-o-m) growth rate for UHG's long calls.
Our multi-step approach for solving the question:
- Filter for long calls (more than 300 seconds).
- Summarise data into a table containing monthly long call information.
- Find the monthly call counts.
- Calculate the variance between the current and previous month's calls.
- Apply the m-o-m growth rate formula.
Step 1: Filter for long calls
Long calls are defined to be calls of more than 300 seconds. We can filter for long calls on the field.
Step 2: Summarise data into a table containing monthly long call information
Next, we obtain the year and month information in numerical format using the function on the field.
Bear in mind not to alias the columns as and as these are SQL's date parts and may confuse SQL whether you're calling the fields or SQL's date parts.
Step 3: Find the monthly call counts for the current and previous month
Next, we find the count of calls by year and month using the aggregate function. Additionally, we are also introducing a window function to generate the previous month's calls.
See how we incorporate the and functions directly into the function? It keeps the query short and sweet.
And, here's how we include it in our query:
Showing the output for the first 5 months:
| yr | mth | curr_mth_call | prev_mth_call |
|---|---|---|---|
| 2022 | 1 | 2 | |
| 2022 | 2 | 2 | 2 |
| 2022 | 3 | 10 | 2 |
| 2022 | 4 | 5 | 10 |
| 2022 | 5 | 19 | 5 |
Let's interpret the output together:
- 2022/1 - January is the first month of the year, hence it doesn't have the previous month's call count in the field.
- 2022/2, 3 - In February, the previous month's 2 calls are pulled from January. Similarly, in March.
- 2022/4 - In April, the previous month's 10 calls are pulled from March.
So, can you catch the pattern of how the window function works? To better understand the usage of the LAG function, check here for more examples.
We will convert this query to a CTE called .
Step 4: Calculate the variance between the current and previous month's calls
Here's the formula that we're using:
M-on-M Growth Rate = (Current Month's Calls - Previous Month's Calls) / Previous Month's Calls x 100
We have the current and previous month's calls so let's put them into the query.
Showing the output for the first 5 months:
| yr | mth | growth_pct |
|---|---|---|
| 2022 | 1 | |
| 2022 | 2 | 0.00000000000000000000 |
| 2022 | 3 | 400.0000000000000000 |
| 2022 | 4 | -50.0000000000000000 |
| 2022 | 5 | 280.0000000000000000 |
We want the percentages to be rounded to 1 decimal place, so use a function on the field. The result should be returned in chronological sequence so order the months accordingly.
PostgreSQL 14