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.
Calls to the Advocate4Me call centre are
categorised, but sometimes they can't fit neatly into a category. These
uncategorised calls are labelled 'n/a', or are just empty (when a support agent
enters nothing into the category field).
Write a query to find the percentage of calls
that cannot be categorised. Round your answer to 1 decimal place.
table name: callers
Solution:
SELECT
format((100.00 * convert(decimal,sum(case when call_category IS NULL OR call_category = 'n/a' then 1 else 0 end)))/count(case_id),'N1') as
call_percentage
FROM callers;
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 (52481621, N'a94c-2213-4ba5-812d', N'', NULL, 286, 161)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (51435044, N'f0b5-0eb0-4c49-b21e', N'n/a', NULL, 208, 225)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (52082925, N'289b-d7e8-4527-bdf5', N'benefits', NULL, 291, 352)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54624612, N'62c2-d9a3-44d2-9065', N'IT_support', NULL, 273, 358)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (54624612, N'9f57-164b-4a36-934e', N'claims', NULL, 157, 362)
GO