SQLmediumSubqueries~15m

MegaShield Insurance wants to identify high-value policyholders who have both...

Problem

MegaShield Insurance wants to identify high-value policyholders who have both premium policies and exceptional claim behavior. Find all policyholders who have at least one policy with annual premium >= $5000 AND whose average claim amount (across all their claims) is below the company-wide average claim amount. Return policyholder_id, policyholder_name, their personal average claim amount, and the company-wide average claim amount. Order by policyholder_id.

Examples

Example 1

Example 2

Constraints

›Return policyholder_id, policyholder_name, personal average claim amount, and company-wide average claim amount
›Include only policyholders with at least one policy having annual_premium >= 5000
›Include only policyholders whose personal average claim amount is below the company average
›Order results by policyholder_id ascending

Reference solution

Reference solution available after you attempt the question.

Ready to solve it?

Start a session on Mockbit #59. Write your SQL, run it against real data, and get graded on correctness, efficiency, query structure, and business understanding.