UnitedHealth 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, heath care
financing, benefits, clamis or pharmacy help.
Write a query to find how many UHG members made 3
or more calls. case_id column uniquely identifies each call made.
table name: callers
Solution:
with cte as
(
select count(distinct
policy_holder_id) as m from callers
group by policy_holder_id
having count(case_id)>2
)
select sum(m) as member_count from cte
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 (50837000, N'dc63-acae-4f39-bb04', N'claims', CAST(N'2022-09-03T02:51:00.000' AS DateTime), 205, 130)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50837000, N'41be-bebe-4bd0-a1ba', N'IT_support', CAST(N'2022-12-03T05:37:00.000' AS DateTime), 254, 129)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50936674, N'12c8-b35c-48a3-b38d', N'claims', CAST(N'2022-05-31T00:00:00.000' AS DateTime), 240, 31)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50886837, N'd0b4-8ea7-4b8c-aa8b', N'IT_support', CAST(N'2022-11-03T03:38:00.000' AS DateTime), 276, 16)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50886837, N'a741-c279-41c0-90ba', N'', CAST(N'2022-03-11T00:00:00.000' AS DateTime), 131, 325)
GO
INSERT [dbo].[callers] ([policy_holder_id], [case_id], [call_category], [call_received], [call_duraton_secs], [original_order]) VALUES (50837000, N'bab1-3ec5-4867-90ae', N'benefits', CAST(N'2022-03-19T00:00:00.000' AS DateTime), 228, 339)
GO