logo

Back to questions

Patient Support Analysis (Part 4) [UnitedHealth SQL Interview Question]

Hard

UnitedHealth Group offers the Advocate4Me program, providing members with access to advocates who offer assistance and support for their healthcare needs, such as behavioural, clinical, well-being, health care financing, benefits, claims, and pharmacy-related inquiries.

To analyze the performance of the program, write a query to determine the month-over-month growth rate specifically for long-calls. A long-call is defined as any call lasting more than 5 minutes (300 seconds).

Output the year, month in numerical format and chronological order, along with the growth percentage rounded to 1 decimal place.

Table:

Column NameType
policy_holder_idinteger
case_idvarchar
call_categoryvarchar
call_receivedtimestamp
call_duration_secsinteger
original_orderinteger

Example Input:

policy_holder_idcase_idcall_categorycall_receivedcall_duration_secsoriginal_order
50986511b274-c8f0-4d5c-87042022-01-28T09:46:00252456
54026568405a-b9be-45c2-b311n/a2022-01-29T16:19:00397217
54026568c4cc-fd40-4780-8a53benefits2022-01-30T08:18:00320134
5402656881e8-6abf-425b-add2n/a2022-02-20T17:26:00132483
544751015919-b9c2-49a5-80912022-02-24T18:07:00206498
54624612a17f-a415-4727-9a3fbenefits2022-02-27T10:56:0043519
53777383dfa9-e5a7-4a9b-a756benefits2022-03-19T00:10:0031869
52880317cf00-56c4-4e76-963aclaims2022-03-21T01:12:00340254
526809690c3c-7b87-489a-98572022-03-21T14:00:00310213
54574775ca73-bf99-46b2-a79bbilling2022-04-18T14:09:00181312
514350446546-61b4-4a05-9a5e2022-04-18T21:58:00354439
52780643e35a-a7c2-4718-a65dn/a2022-05-06T14:31:00318186
5402656861ac-eee7-42fa-a6742022-05-07T01:27:00404341
546744493d9d-e6e2-49d5-a1a0billing2022-05-09T11:00:00107450
54026568c516-0063-4b8f-aa742022-05-13T01:06:00404270

Example Output:

yrmthlong_calls_growth_pct
202210
202220
2022350.0
20224-66.7
20225200.0

Explanation:

Call counts: Jan - 2 calls; Feb - 2 calls; Mar - 3 calls; Apr - 1 call; May - 3 calls

  • In January, since there is no previous month's call information, the growth percentage is 0%.
  • In February, there is no change in the number of calls compared to January, resulting in a growth percentage of 0%.
  • In March, the number of calls increased by 1 compared to February, resulting in a growth percentage of +50.0%.
  • In April, there is a decrease of 2 calls compared to March, resulting in a drop of 66.7%.
  • Finally, in May, there were 3 calls, indicating a growth of 200.0% compared to April.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14