Back to questions
Patient Support Analysis (Part 2) [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.
Calls to the Advocate4Me call centre are categorised, but sometimes they can't fit neatly into a category. These uncategorised calls are labelled “n/a”, or are just empty (when a support agent enters nothing into the category field).
Write a query to find the percentage of calls that cannot be categorised. Round your answer 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 |
|---|---|---|---|---|---|
| 52481621 | a94c-2213-4ba5-812d | 01/17/2022 19:37:00 | 286 | 161 | |
| 51435044 | f0b5-0eb0-4c49-b21e | n/a | 01/18/2022 2:46:00 | 208 | 225 |
| 52082925 | 289b-d7e8-4527-bdf5 | benefits | 01/18/2022 3:01:00 | 291 | 352 |
| 54624612 | 62c2-d9a3-44d2-9065 | IT_support | 01/19/2022 0:27:00 | 273 | 358 |
| 54624612 | 9f57-164b-4a36-934e | claims | 01/19/2022 6:33:00 | 157 | 362 |
Example Output:
| call_percentage |
|---|
| 40.0 |
Explanation:
A total of 5 calls were registered. Out of which 2 calls were not categorised. That makes 40.0% (2/5 x 100.0) of the calls uncategorised.
The dataset you are querying against may have different input & output - this is just an example!
Solution
We'll start by defining the formula.
Percentage of uncategorized calls = (Number of uncategorized calls / Total calls) x 100
Let's break this problem into 4 steps:
- Filter for uncategorised calls and count them.
- Count the total calls.
- Use the percentage formula.
- Round the output.
Step 1: Filter for uncategorised calls and count them.
First, count the calls that are uncategorised i.e. call records with the column having either "n/a" or values which looks like an empty space.
Using the function, we can get the count of uncategorised calls.
| uncategorised_calls |
|---|
| 225 |
225 calls were recorded without being assigned to a category.
Instead of putting this query into a subquery or CTE which can make the solution a bit lengthy, we're using the clause with the combination of the function. You can read about it in detail here under 4.2.7. Aggregate Expressions section.
Let's use the clause in our query above.
Have a run in the editor - it produces the same result!
Step 2: Count the total calls
In the following step, we will utilise another function to get the number of total calls regardless of the category.
| uncategorised_calls | total_calls |
|---|---|
| 225 | 500 |
Step 3: Use the percentage formula
Let's now modify our query to fit into the percentage formula.
| uncategorised_call_pct |
|---|
| 45.0000000000000000 |
Note: It is crucial to multiply by 100.0 instead of 100 since division operations require at least one numeric value to be of the data type. Otherwise, the digits after the decimal will be truncated and the results will be incorrect.
Step 4: Round the output
The last step is to round the percentage to one decimal place. function can be used to accomplish it.
This brings us to our final solution query. Yay!
Solution #1: Using FILTER clause
There are numerous methods to solve this question. Below are 2 more suggested solutions for you to try out.
Solution #2: Using WHERE clause
Solution #3: Using CTE
PostgreSQL 14