Patient Support Analysis (Part 4)

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: callers


Solution:

with cte as
(
select month(call_received) as month,year(call_received) as year,
count(case_id) AS curr_month_call,
lag(count(case_id),1) over (order by month(call_received)) as prev_month_call
 from callers
where call_duraton_secs >300
group by month(call_received),year(call_received)
)
SELECT
  year,
  month,
  convert(decimal(7,1),100.0 * (curr_month_call
- prev_month_call)/prev_month_call) AS growth_pct
FROM cte
ORDER BY year, month;

 Output:


SQL Script:

CREATE TABLE [dbo].[callers](
       [policy_holder_id]
[int] NULL,
       [case_id]
[varchar](50) NULL,
       [call_category]
[varchar](50) NULL,
       [call_received]
[datetime] NULL,
       [call_duraton_secs]
[int] NULL,
       [original_order]
[int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50986511, N'b274-c8f0-4d5c-8704', N'', CAST(N'2022-01-28T09:46:00.000' AS DateTime), 252, 456)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54026568, N'405a-b9be-45c2-b311', N'n/a', CAST(N'2022-01-29T16:19:00.000' AS DateTime), 397, 217)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54026568, N'c4cc-fd40-4780-8a53', N'benefits', CAST(N'2022-01-30T08:18:00.000' AS DateTime), 320, 134)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54026568, N'81e8-6abf-425b-add2', N'n/a', CAST(N'2022-02-20T17:26:00.000' AS DateTime), 1324, 83)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54475101, N'5919-b9c2-49a5-8091', N'', CAST(N'2022-02-24T18:07:00.000' AS DateTime), 206, 498)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54624612, N'a17f-a415-4727-9a3f', N'benefits', CAST(N'2022-02-27T10:56:00.000' AS DateTime), 435, 19)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (53777383, N'dfa9-e5a7-4a9b-a756', N'benefits', CAST(N'2022-03-19T00:10:00.000' AS DateTime), 318, 69)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (52880317, N'cf00-56c4-4e76-963a', N'claims', CAST(N'2022-03-21T01:12:00.000' AS DateTime), 340, 254)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (52680969, N'0c3c-7b87-489a-9857', N'', CAST(N'2022-03-21T14:00:00.000' AS DateTime), 310, 213)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54574775, N'ca73-bf99-46b2-a79b', N'billing', CAST(N'2022-04-18T14:09:00.000' AS DateTime), 181, 312)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (51435044, N'6546-61b4-4a05-9a5e', N'', CAST(N'2022-04-18T21:58:00.000' AS DateTime), 354, 439)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (52780643, N'e35a-a7c2-4718-a65d', N'n/a', CAST(N'2022-05-06T14:31:00.000' AS DateTime), 318, 186)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54026568, N'61ac-eee7-42fa-a674', N'', CAST(N'2022-05-07T01:27:00.000' AS DateTime), 404, 341)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54674449, N'3d9d-e6e2-49d5-a1a0', N'billing', CAST(N'2022-05-09T11:00:00.000' AS DateTime), 107, 450)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54026568, N'c516-0063-4b8f-aa74', N'', CAST(N'2022-05-13T01:06:00.000' AS DateTime), 404, 270)
GO


Comments (0)
Questions List