Analyzing Frequent Callers in Advocate4Me Program: SQL Interview Problem

In this problem, we’re analyzing call data for UnitedHealth Group’s (UHG) Advocate4Me program to find out how many policyholders made three or more calls. Each call is uniquely identified by a case_id. Our goal is to count the number of unique policyholders who have made at least three calls.

Problem Statement

Given the callers table, write a query to find how many UHG policyholders made three or more calls. The table contains the following columns:

Table: callers

Column Name	Type
policy_holder_id	integer
case_id	varchar
call_category	varchar
call_date	timestamp
call_duration_secs	integer

Example Input:

policy_holder_id	case_id	call_category	call_date	call_duration_secs
1	f1d012f9-9d02-4966-a968-bf6c5bc9a9fe	emergency assistance	2023-04-13T19:16:53Z	144
1	41ce8fb6-1ddd-4f50-ac31-07bfcce6aaab	authorisation	2023-05-25T09:09:30Z	815
2	9b1af84b-eedb-4c21-9730-6f099cc2cc5e	claims assistance	2023-01-26T01:21:27Z	992
2	8471a3d4-6fc7-4bb2-9fc7-4583e3638a9e	emergency assistance	2023-03-09T10:58:54Z	128
2	38208fae-bad0-49bf-99aa-7842ba2e37bc	benefits	2023-06-05T07:35:43Z	619

Example Output:

policy_holder_count
1

Explanation:

Only policyholder ID 2 made three or more calls (total of 3 calls).
Policyholder ID 1 made only 2 calls.

Step-by-Step Solution

Count Calls Per Policyholder: First, count the number of calls each policyholder has made.
Filter Policyholders: Filter for policyholders who made three or more calls.
Count Policyholders: Finally, count the number of unique policyholders who meet this criterion.

SQL Query

Here’s the SQL query to find the number of UHG policyholders who made three or more calls:

SELECT 
    COUNT(DISTINCT policy_holder_id) AS policy_holder_count
FROM (
    SELECT 
        policy_holder_id, 
        COUNT(case_id) AS call_count
    FROM 
        callers
    GROUP BY 
        policy_holder_id
    HAVING 
        COUNT(case_id) >= 3
) AS subquery;

Explanation of the Query

Inner Query (Subquery):

GROUP BY policy_holder_id: Groups the records by policy_holder_id.
COUNT(case_id): Counts the number of calls (case_id) for each policyholder.
HAVING COUNT(case_id) >= 3: Filters for policyholders who have made three or more calls.

Outer Query:

COUNT(DISTINCT policy_holder_id): Counts the number of unique policyholders returned by the inner query.
AS policy_holder_count: Gives the final result column a meaningful name.

Conclusion

This SQL query effectively identifies the number of policyholders who have made three or more calls using UHG’s Advocate4Me program. By utilizing SQL’s aggregation functions (COUNT and HAVING), we can efficiently filter and count specific records in a dataset, a critical skill in data analysis and business reporting.

Boost My Skill