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.

callers Table:

callers Example Input:

Example Output:

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!

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 call_category column containing either "n/a" or NULL values. We'll then count the number of such calls using the COUNT() function.

SELECT COUNT(case_id) AS uncategorised_calls
FROM callers
WHERE call_category IS NULL
  OR call_category = 'n/a';

Need a refresher about IS NULL? Explore our tutorial here!

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 callers table.

Psst, want to learn how to generate a CTE? We have a tutorial with examples here!

WITH uncategorised_callers AS (
  SELECT COUNT(case_id) AS uncategorised_calls
  FROM callers
  WHERE call_category IS NULL
    OR call_category = 'n/a'
)

SELECT 
  uncategorised_calls,
  (SELECT COUNT(*) FROM callers) AS total_calls
FROM uncategorised_callers;

We found 11 uncategorised calls out of a total of 200.

Step 3: Calculate Uncategorised Calls Percentage

Next, we'll use the uncategorised_callers 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.

WITH uncategorised_callers AS (
  SELECT COUNT(case_id) AS uncategorised_calls
  FROM callers
  WHERE call_category IS NULL
    OR call_category = 'n/a'
)

SELECT 
  (100.0 * uncategorised_calls 
    / (SELECT COUNT(*) FROM callers)) AS uncategorised_call_pct
FROM uncategorised_callers;

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 DECIMAL data type. Otherwise, any digits AFTER the decimal point5. 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 ROUND() function for better readability.

WITH uncategorised_callers AS (
  SELECT COUNT(case_id) AS uncategorised_calls
  FROM callers
  WHERE call_category IS NULL
    OR call_category = 'n/a'
)

SELECT 
  ROUND(100.0 * uncategorised_calls 
    / (SELECT COUNT(*) FROM callers), 1) AS uncategorised_call_pct
FROM uncategorised_callers;