Back to questions
Patient Support Analysis (Part 1) [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 find how many UHG policy holders made three, or more calls, assuming each call is identified by the column.
If you like this question, try out Patient Support Analysis (Part 2)!
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 | claims assistance | 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:
| policy_holder_count |
|---|
| 1 |
Explanation:
The only caller who made three, or more calls is policy holder ID 2.
The dataset you are querying against may have different input & output - this is just an example!
Solution
First, we categorize the callers and assess their call frequency.
The clause can be used to create distinct groups. In our case, we group the callers based on the column.
It's worth noting that we use the terms "policy holders" and "members" interchangeably to denote the same group.
Next, we apply the aggregate function to count the frequency of values in the column for each policy holder.
Let's consider an example with policy holder IDs 42, 29, and 4:
| policy_holder_id | call_count |
|---|---|
| 42 | 5 |
| 29 | 3 |
| 4 | 1 |
As observed, policy holder ID 4 has made only one call, whereas IDs 42 and 29 have made multiple calls.
Next, to filter out policy holders who have called three or more times, we utilize the clause:
This query returns:
| policy_holder_id | call_count |
|---|---|
| 42 | 5 |
| 29 | 3 |
Only policy holder with IDs 42 and 29 meet the criteria of three or more calls.
Finally, to obtain the count of such policy holders, we will wrap the previous query within a subquery and apply the function.
A subquery is a nested query. It’s a query within a query and can be used within that query only. Read here for more understanding.
This yields the output:
| policy_holder_count |
|---|
| 2 |
This indicates that there are two policy holders who have made three or more calls.
Solution #2: Using CTE
Alternatively, learn how to use a Common Table Expression (CTE) in this tutorial.
PostgreSQL 14