logo

Back to questions

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

Hard

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.

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:

yrmthgrowth_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, the percentage is 0% as there is no previous month's call info.
  • In February, the percentage is 0% as there is no increase/decrease from January.
  • In March, the growth is +50.0% as there is an increase of 1 call from February.
  • In April, drop by 66.7% as there is a reduction of 2 calls from March.
  • Finally, in May, there were 3 calls thus a 200.0% growth from April.

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

PostgreSQL 14