SQL Joins and Subqueries | Dataford Interview Questions

Write a simple SQL query involving JOINs and subqueries.

Use the provided customer, policy, and claim tables. Return customers whose total approved claim amount is greater than the average approved claim amount across all approved claims.

Output

One row per qualifying customer
Columns: customer_id, customer_name, total_approved_claims
Sort by total_approved_claims descending, then customer_id ascending

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
customer_id	INT	Customer who owns the policy
policy_number	VARCHAR(30)	Business policy number
policy_status	VARCHAR(20)	Current policy status

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	Policy associated with the claim
claim_status	VARCHAR(20)	Current claim status
claim_amount	DECIMAL(12,2)	Claim amount

Your question is SQL Joins and Subqueries. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem