Back to questions
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.
Write a query to get the patients who made a call within 7 days of their previous call. If a patient called more than twice in a span of 7 days, count them as once.
If you like this question, try out Patient Support Analysis (Part 4)!
Column Name | Type |
---|---|
policy_holder_id | integer |
case_id | varchar |
call_category | varchar |
call_received | timestamp |
call_duration_secs | integer |
original_order | integer |
policy_holder_id | case_id | call_category | call_received | call_duration_secs | original_order |
---|---|---|---|---|---|
50837000 | dc63-acae-4f39-bb04 | claims | 3/9/2022 2:51 | 205 | 130 |
50837000 | 41be-bebe-4bd0-a1ba | IT_support | 3/12/2022 5:37 | 254 | 129 |
50837000 | bab1-3ec5-4867-90ae | benefits | 5/13/2022 18:19 | 228 | 339 |
50936674 | 12c8-b35c-48a3-b38d | claims | 5/31/2022 7:27 | 240 | 31 |
50886837 | d0b4-8ea7-4b8c-aa8b | IT_support | 3/11/2022 3:38 | 276 | 16 |
50886837 | a741-c279-41c0-90ba | 3/19/2022 10:52 | 131 | 325 |
patient_count |
---|
1 |
Only patient 50837000 made another call (on March 12th, 2022) within 7 days of the last call (on March 9th, 2022).
The dataset you are querying against may have different input & output - this is just an example!
The following steps will be used to find patients who made calls within 7 days of their previous call.
For each logged call, we have to retrieve the previous call as well using the column.
To get data from the previous row, the row before the previous row, and so on, we can use the window function. Take a quick look here to read about the function.
Displaying a few randomly selected records for patients 51435044 and 50986511:
policy_holder_id | call_received | previous_call |
---|---|---|
51435044 | 01/18/2022 02:46:00 | |
51435044 | 04/11/2022 02:41:00 | 01/18/2022 02:46:00 |
51435044 | 04/18/2022 21:58:00 | 04/11/2022 02:41:00 |
50986511 | 01/28/2022 09:46:00 | |
50986511 | 01/31/2022 04:44:00 | 01/28/2022 09:46:00 |
50986511 | 01/31/2022 16:36:00 | 01/31/2022 04:44:00 |
Let's interpret the function:
For each record, subtract the column's values from the column's values.
Output:
policy_holder_id | current_call | previous_call | time_difference |
---|---|---|---|
51435044 | 01/18/2022 02:46:00 | ||
51435044 | 04/11/2022 02:41:00 | 01/18/2022 02:46:00 | "days":82,"hours":23,"minutes":55 |
51435044 | 04/18/2022 21:58:00 | 04/11/2022 02:41:00 | "days":7,"hours":19,"minutes":17 |
50986511 | 01/28/2022 09:46:00 | ||
50986511 | 01/31/2022 04:44:00 | 01/28/2022 09:46:00 | "days":2,"hours":18,"minutes":58 |
50986511 | 01/31/2022 16:36:00 | 01/31/2022 04:44:00 | "hours":11,"minutes":52 |
The column displays the precise time difference in format between the and columns.
For example,
Since the time difference contains multiple units of time, we're only taking the portion using the function.
But there is a catch!
If only the argument is used, we might also wrongly include calls made within 7 hours and xx minutes.
For example:
To solve this, the function can be used with the function to return in the format of seconds.
policy_holder_id | call_received | previous_call | time_difference |
---|---|---|---|
51435044 | 01/18/2022 02:46:00 | ||
51435044 | 04/11/2022 02:41:00 | 01/18/2022 02:46:00 | 7170900 |
51435044 | 04/18/2022 21:58:00 | 04/11/2022 02:41:00 | 674220 |
50986511 | 01/28/2022 09:46:00 | ||
50986511 | 01/31/2022 04:44:00 | 01/28/2022 09:46:00 | 241080 |
50986511 | 01/31/2022 16:36:00 | 01/31/2022 04:44:00 | 42720 |
The column now displays the difference between two calls in seconds. To convert it into days, we multiply by the following:
1 day = 24 hours x 60 minutes x 60 seconds
policy_holder_id | call_received | previous_call | day_difference |
---|---|---|---|
51435044 | 01/18/2022 02:46:00 | ||
51435044 | 04/11/2022 02:41:00 | 01/18/2022 02:46:00 | 82.99 |
51435044 | 04/18/2022 21:58:00 | 04/11/2022 02:41:00 | 7.80 |
50986511 | 01/28/2022 09:46:00 | ||
50986511 | 01/31/2022 04:44:00 | 01/28/2022 09:46:00 | 2.79 |
50986511 | 01/31/2022 16:36:00 | 01/31/2022 04:44:00 | 0.49 |
Finally, we filter the records to only include calls with no more than seven days difference.
Create a common table expression (CTE) around our previous query and add a clause to it.
Patients will be counted using the function.
However, as we can see from the preceding output, patient 50986511 made all three calls within a 7-day period; hence, the patient should only be taken into account once. When counting the patients, the keyword is the best to use.
Result:
patient_count |
---|
1 |
Remove the unnecessary column which was used for demonstration purposes in the CTE. And there you have it - our final query!
Solution #1: Using EPOCH()
Solution #2: Using INTERVAL
PostgreSQL 14