Back to questions
Patient Support Analysis (Part 2) [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.
Calls to the Advocate4Me call centre are classified into various categories, but some calls cannot be neatly categorised. These uncategorised calls are labeled as “n/a”, or are left empty when the support agent does not enter anything into the call category field.
Write a query to calculate the percentage of calls that cannot be categorised. Round your answer to 1 decimal place. For example, 45.0, 48.5, 57.7.
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 | 2023-04-13T19:16:53Z | 144 |
| 1 | 41ce8fb6-1ddd-4f50-ac31-07bfcce6aaab | authorisation | 2023-05-25T09:09:30Z | 815 |
| 2 | 9b1af84b-eedb-4c21-9730-6f099cc2cc5e | n/a | 2023-01-26T01:21:27Z | 992 |
| 2 | 8471a3d4-6fc7-4bb2-9fc7-4583e3638a9e | emergency assistance | 2023-03-09T10:58:54Z | 128 |
| 2 | 38208fae-bad0-49bf-99aa-7842ba2e37bc | benefits | 2023-06-05T07:35:43Z | 619 |
Example Output:
| uncategorised_call_pct |
|---|
| 20.0 |
Explanation:
Out of the total of 5 calls registered, one call was not categorised. Therefore, the percentage of uncategorised calls is calculated as 20.0% (1 out of 5 multiplied by 100 and rounded to one decimal place).
The dataset you are querying against may have different input & output - this is just an example!
Solution
Let's start by defining the formula for the percentage of uncategorised calls:
Percentage of uncategorised calls = (Number of uncategorized calls / Total calls) x 100
We can break down this problem into four steps:
Step 1: Filter and Count Uncategorised Calls
To begin, let's filter for uncategorised calls which are call records with the column containing either "n/a" or values. We'll then count the number of such calls using the function.
Need a refresher about ? Explore our tutorial here!
| uncategorised_calls |
|---|
| 11 |
11 calls were recorded without being assigned to a category.
Step 2: Determine Uncategorised and Total Calls
In this step, we'll create a Common Table Expression (CTE) named "uncategorised_callers" to store the previous query. Additionally, we'll calculate the total number of calls in the table.
Psst, want to learn how to generate a CTE? We have a tutorial with examples here!
| uncategorised_calls | total_calls |
|---|---|
| 11 | 200 |
We found 11 uncategorised calls out of a total of 200.
Step 3: Calculate Uncategorised Calls Percentage
Next, we'll use the CTE to calculate the percentage of uncategorised calls out of the total calls. We'll multiply by 100.0 instead of 100 to ensure accurate decimal results.
| uncategorised_call_pct |
|---|
| 5.5000000000000000 |
The percentage of uncategorised calls is calculated to be 5.5000000000000000.
Note: It's essential to multiply by "100.0" instead of "100" because division operations require at least operand to be of the data type. Otherwise, any digits AFTER the decimal point will be truncated, leading to inaccurate results.
Step 4: Round the Percentage Output
Finally, we'll round the percentage to one decimal place using the function for better readability.
PostgreSQL 14