Back to questions
Patient Support Analysis (Part 1) [UnitedHealth SQL Interview Question]
UnitedHealth 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.
Write a query to find how many UHG members made 3 or more calls. column uniquely identifies each call made.
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_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 |
|---|---|---|---|---|---|
| 50837000 | dc63-acae-4f39-bb04 | claims | 03/09/2022 02:51:00 | 205 | 130 |
| 50837000 | 41be-bebe-4bd0-a1ba | IT_support | 03/12/2022 05:37:00 | 254 | 129 |
| 50936674 | 12c8-b35c-48a3-b38d | claims | 05/31/2022 7:27:00 | 240 | 31 |
| 50886837 | d0b4-8ea7-4b8c-aa8b | IT_support | 03/11/2022 3:38:00 | 276 | 16 |
| 50886837 | a741-c279-41c0-90ba | 03/19/2022 10:52:00 | 131 | 325 | |
| 50837000 | bab1-3ec5-4867-90ae | benefits | 05/13/2022 18:19:00 | 228 | 339 |
Example Output:
| member_count |
|---|
| 1 |
Explanation:
The only caller who made 3 or more calls is member ID 50837000.
The dataset you are querying against may have different input & output - this is just an example!
Solution
First, we identify who called and how frequently.
clause can be used to generate the groups. Since we need the information for members, we group them based on the column.
Note that members are used interchangeably with policy holders but they mean the same.
Next, we apply an aggregate function , which counts the number of values in the column for each policy holder-group.
Displaying records for policy holder IDs 53578035 and 54126242:
| policy_holder_id | call_count |
|---|---|
| 53578035 | 1 |
| 54126242 | 5 |
In contrast to member 54126242, who has reportedly made five calls, member 51983251 has only made one call.
Then, a conditional clause with the keyword can be applied to keep rows with members who called 3 or more times. clause is used to filter group rows. This sets it apart from a clause that filters individual rows.
| policy_holder_id | call_count |
|---|---|
| 54126242 | 5 |
Only member 54126242 is in the result because this member made five calls.
Finally, we obtain the count of members using another function. Before the function can be used, the previous query must first be encapsulated in a subquery.
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.
Output based on the table above:
| member_count |
|---|
| 1 |
Solution #2: Using CTE
Learn how to create a CTE in this tutorial.
PostgreSQL 14