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.
A long-call is categorised as any call that lasts more than 5 minutes (300 seconds). What's the month-over-month growth of long-calls?
Output the year, month (both in numerical and chronological order) and growth percentage rounded to 1 decimal place.
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 |
---|---|---|---|---|---|
50986511 | b274-c8f0-4d5c-8704 | 2022-01-28T09:46:00 | 252 | 456 | |
54026568 | 405a-b9be-45c2-b311 | n/a | 2022-01-29T16:19:00 | 397 | 217 |
54026568 | c4cc-fd40-4780-8a53 | benefits | 2022-01-30T08:18:00 | 320 | 134 |
54026568 | 81e8-6abf-425b-add2 | n/a | 2022-02-20T17:26:00 | 1324 | 83 |
54475101 | 5919-b9c2-49a5-8091 | 2022-02-24T18:07:00 | 206 | 498 | |
54624612 | a17f-a415-4727-9a3f | benefits | 2022-02-27T10:56:00 | 435 | 19 |
53777383 | dfa9-e5a7-4a9b-a756 | benefits | 2022-03-19T00:10:00 | 318 | 69 |
52880317 | cf00-56c4-4e76-963a | claims | 2022-03-21T01:12:00 | 340 | 254 |
52680969 | 0c3c-7b87-489a-9857 | 2022-03-21T14:00:00 | 310 | 213 | |
54574775 | ca73-bf99-46b2-a79b | billing | 2022-04-18T14:09:00 | 181 | 312 |
51435044 | 6546-61b4-4a05-9a5e | 2022-04-18T21:58:00 | 354 | 439 | |
52780643 | e35a-a7c2-4718-a65d | n/a | 2022-05-06T14:31:00 | 318 | 186 |
54026568 | 61ac-eee7-42fa-a674 | 2022-05-07T01:27:00 | 404 | 341 | |
54674449 | 3d9d-e6e2-49d5-a1a0 | billing | 2022-05-09T11:00:00 | 107 | 450 |
54026568 | c516-0063-4b8f-aa74 | 2022-05-13T01:06:00 | 404 | 270 |
yr | mth | growth_pct |
---|---|---|
2022 | 1 | 0 |
2022 | 2 | 0 |
2022 | 3 | 50.0 |
2022 | 4 | -66.7 |
2022 | 5 | 200.0 |
Call counts: Jan - 2 calls; Feb - 2 calls; Mar - 3 calls; Apr - 1 call; May - 3 calls
The dataset you are querying against may have different input & output - this is just an example!
Goal: Find the month-on-month (m-o-m) growth rate for UHG's long calls.
Our multi-step approach for solving the question:
Long calls are defined to be calls of more than 300 seconds. We can filter for long calls on the field.
Next, we obtain the year and month information in numerical format using the function on the field.
Bear in mind not to alias the columns as and as these are SQL's date parts and may confuse SQL whether you're calling the fields or SQL's date parts.
Next, we find the count of calls by year and month using the aggregate function. Additionally, we are also introducing a window function to generate the previous month's calls.
See how we incorporate the and functions directly into the function? It keeps the query short and sweet.
And, here's how we include it in our query:
Showing the output for the first 5 months:
yr | mth | curr_mth_call | prev_mth_call |
---|---|---|---|
2022 | 1 | 2 | |
2022 | 2 | 2 | 2 |
2022 | 3 | 10 | 2 |
2022 | 4 | 5 | 10 |
2022 | 5 | 19 | 5 |
Let's interpret the output together:
So, can you catch the pattern of how the window function works? To better understand the usage of the LAG function, check here for more examples.
We will convert this query to a CTE called .
Here's the formula that we're using:
M-on-M Growth Rate = (Current Month's Calls - Previous Month's Calls) / Previous Month's Calls x 100
We have the current and previous month's calls so let's put them into the query.
Showing the output for the first 5 months:
yr | mth | growth_pct |
---|---|---|
2022 | 1 | |
2022 | 2 | 0.00000000000000000000 |
2022 | 3 | 400.0000000000000000 |
2022 | 4 | -50.0000000000000000 |
2022 | 5 | 280.0000000000000000 |
We want the percentages to be rounded to 1 decimal place, so use a function on the field. The result should be returned in chronological sequence so order the months accordingly.
PostgreSQL 14