Back to questions
Patient Support Analysis (Part 3) UnitedHealth SQL Interview Question
Patient Support Analysis (Part 3)
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.
Write a query to obtain the number of unique callers who made calls within a 7-day interval of their previous calls. If a caller made more than two calls within the 7-day period, count them only once.
If you like this question, try out Patient Support Analysis (Part 4)!
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 |
|---|---|---|---|---|
| 13 | 894a08c6-82c6-49ed-84a0-b627752cdca7 | benefits | 01/11/2023 02:09:23 | 794 |
| 13 | 7ee4e5f0-4924-47f7-8809-172bb59c6ef0 | provider network | 02/01/2023 18:41:03 | 659 |
| 14 | 0b68aafa-6907-43c3-9dbd-a6c171cf5006 | claims assistance | 02/17/2023 10:03:27 | 757 |
| 14 | c4660294-9443-4aba-b6d4-f39a9d5e5f00 | benefits | 02/21/2023 13:57:31 | 36 |
| 15 | 06545ac5-18f5-4ae8-9b1e-087c7dc8deca | member portal | 01/08/2023 03:19:24 | 973 |
| 15 | 9580a1ad-842a-482f-a05c-7e1c09b926b3 | provider network | 01/09/2023 01:33:06 | 729 |
Example Output:
| policy_holder_count |
|---|
| 2 |
Explanation:
Policy holder IDs 14 and 15 each made two calls within a 7-day interval. For example, ID 14 made calls on 02/17/2023 and 02/21/2023, and ID 14 on 01/08/2023 and 01/09/2023 consecutively. Hence, the count of unique callers is two.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Retrieve the previous call for each caller
To ensure we capture the timing between consecutive calls, we need to retrieve the previous call made by each policy holder.
We achieve this by using the window function which allows us to access data from the preceding row within each partition of policy holders, ordered by call date.
Explore our comprehensive tutorial to window functions here!
Here, the function retrieves the call date of the previous call for each policy holder.
Displaying records for policy holder ID 13, 14, and 15:
| policy_holder_id | call_date | previous_call |
|---|---|---|
| 13 | 01/11/2023 02:09:23 | NULL |
| 13 | 02/01/2023 18:41:03 | 01/11/2023 02:09:23 |
| 14 | 02/17/2023 10:03:27 | 01/24/2023 01:18:07 |
| 14 | 02/21/2023 13:57:31 | 02/17/2023 10:03:27 |
| 15 | 01/08/2023 03:19:24 | NULL |
| 15 | 01/09/2023 01:33:06 | 01/08/2023 03:19:24 |
Let's interpret the function:
- The clause divides the rows into unique groups based on policy holder IDs.
- The function operates within each partition, enabling separate assessments for each policy holder.
- Rows within each partition are sorted chronologically based on the call date specified in the clause.
Step 2: Calculate the time difference between consecutive calls
With the timestamps of each call and its preceding call obtained, we can now compute the time difference between them. This calculation is crucial for identifying calls made within a 7-day interval. We utilize the function to derive the time difference in seconds which you can learn about here.
Displaying records for policy holder ID 13, 14, and 15:
Here, the column represents the precise time difference in seconds between the current call and the previous call for each policy holder.
| policy_holder_id | current_call | previous_call | time_difference |
|---|---|---|---|
| 13 | 01/11/2023 02:09:23 | NULL | NULL |
| 13 | 02/01/2023 18:41:03 | 01/11/2023 02:09:23 | "days":21,"hours":16,"minutes":31,"seconds":40 |
| 14 | 02/17/2023 10:03:27 | 01/24/2023 01:18:07 | "days":24,"hours":8,"minutes":45,"seconds":20 |
| 14 | 02/21/2023 13:57:31 | 02/17/2023 10:03:27 | "days":4,"hours":3,"minutes":54,"seconds":4 |
| 15 | 01/08/2023 03:19:24 | NULL | NULL |
| 15 | 01/09/2023 01:33:06 | 01/08/2023 03:19:24 | "hours":22,"minutes":13,"seconds":42 |
The column displays the precise time difference in format between the and columns.
Let's analyze their call history:
- Policy holder 13 made their second call after 21 days from their first call. Hence, this is not considered a call within a 7-day interval.
- Policy holder 14 made their second call after 4 days from their first call. Hence, this is not considered a call within a 7-day interval.
- Policy holder 15 made their second call after 22 hours from their first call. This is considered a call within a 7-day period.
Step 3: Filter calls within a 7-day interval
While the previous step provides the time difference in seconds, we need to ensure that we're only considering calls made within a 7-day window. To accomplish this, we convert the time difference into days using the function in conjunction with function to obtain the time difference in seconds.
| policy_holder_id | current_call | previous_call | time_diff_secs |
|---|---|---|---|
| 13 | 01/11/2023 02:09:23 | NULL | NULL |
| 13 | 02/01/2023 18:41:03 | 01/11/2023 02:09:23 | 1873900.000000 |
| 14 | 02/17/2023 10:03:27 | 01/24/2023 01:18:07 | 2105120.000000 |
| 14 | 02/21/2023 13:57:31 | 02/17/2023 10:03:27 | 359644.000000 |
| 15 | 01/08/2023 03:19:24 | NULL | NULL |
| 15 | 01/09/2023 01:33:06 | 01/08/2023 03:19:24 | 80022.000000 |
The column displays the difference between two calls in seconds. To convert this value into days, we use the following conversion:
1 day = 24 hours x 60 minutes x 60 seconds
Now, the column presents the difference between two calls in days.
| policy_holder_id | current_call | previous_call | time_diff_days |
|---|---|---|---|
| 13 | 02/01/2023 18:41:03 | 01/11/2023 02:09:23 | 21.69 |
| 13 | 02/19/2023 17:25:09 | 02/01/2023 18:41:03 | 17.95 |
| 14 | 02/17/2023 10:03:27 | 01/24/2023 01:18:07 | 24.36 |
| 14 | 02/21/2023 13:57:31 | 02/17/2023 10:03:27 | 4.16 |
| 15 | 01/08/2023 03:19:24 | NULL | NULL |
| 15 | 01/09/2023 01:33:06 | 01/08/2023 03:19:24 | 0.93 |
Step 4: Count the number of unique policy holders with calls within 7 days
Finally, we tally the count of unique policy holders who made calls within a 7-day interval. By employing a Common Table Expression (CTE) (learn about it here!), we include our previous query and apply a clause to filter out calls made beyond the 7-day threshold. To ensure accurate counting, we utilize the .
Feel free to remove the and columns which were included for demonstration purposes in the CTE. And there you have it - our final query!
Solution #2: Using INTERVAL