Objective

Write a SQL query to find the number of policy holders who made three or more calls to customer support.

Table Schema:

• callers

Task Requirements:

• Count the number of calls per policy holder using case_id
• Filter for policy holders with 3 or more calls
• Return the total count of such policy holders
  
  

Output columns: policy_holder_count

SELECT 
    COUNT(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 frequent_callers;

Explanation

Step 1: Inner Query - Counting Calls Per Policy Holder

SELECT policy_holder_id, COUNT(case_id) AS call_count
FROM callers
GROUP BY policy_holder_id

Groups all call records by policy_holder_id and counts how many calls each policy holder made.

Step 2: Filtering with HAVING Clause

HAVING COUNT(case_id) >= 3

Filters after aggregation to include only policy holders with 3 or more calls. HAVING is used instead of WHERE because it filters on aggregate results.

Step 3: Outer Query - Counting Qualifying Policy Holders

SELECT COUNT(policy_holder_id) AS policy_holder_count
FROM frequent_callers

Counts how many policy holders made 3 or more calls, giving the final answer.