For the best coding experience, use a desktop browser.

Medium

Databases

High-Frequency Claimants

Problem

You are working as a data analyst for an insurance company. The risk management team wants to flag policyholders who are filing claims at an unusually high rate, as these individuals may need further review.

Write a SQL query to identify all policyholders who have submitted more than 3 claims within the last 12 months (relative to today's date). Return their policyholder ID, full name, and the total number of claims filed in that period.

Schema

Table: `policyholders`

Column	Type	Description
`id`	INT	Primary key
`name`	VARCHAR	Full name
`email`	VARCHAR	Email address

Table: `claims`

Column	Type	Description
`id`	INT	Primary key
`policyholder_id`	INT	Foreign key to policyholders
`claim_date`	DATE	Date the claim was filed
`amount`	DECIMAL	Claim amount in USD
`status`	VARCHAR	'approved', 'pending', or 'rejected'

Output Format

Return a result set with columns:

policyholder_id — the ID of the policyholder
name — the full name of the policyholder
total_claims — count of claims filed in the last 12 months

Order the results by total_claims descending, then by policyholder_id ascending.

Notes

"Last 12 months" means claim_date >= CURRENT_DATE - INTERVAL '12 months' (or equivalent in your SQL dialect)
Include claims of all statuses (approved, pending, rejected)
Only return policyholders with more than 3 claims (i.e., COUNT > 3)
If no policyholders qualify, return an empty result set

Approach Hints

JOIN the two tables on policyholder_id
Filter claims by date using a WHERE clause with a date range condition
GROUP BY policyholder to aggregate claim counts
Use (not WHERE) to filter groups with more than 3 claims

Constraints

- 1 <= number of policyholders <= 10^6
- 0 <= number of claims per policyholder <= 10^4
- claim_date is a valid DATE in the past or present
- All policyholder_id values in claims reference an existing policyholder
- amount >= 0
- status is one of: 'approved', 'pending', 'rejected'

Examples

Input: policyholders: [(1, 'Alice Johnson'), (2, 'Bob Smith'), (3, 'Carol White')] claims (all within last 12 months): [(1,1,'2024-03-01',500,'approved'), (2,1,'2024-05-15',300,'pending'), (3,1,'2024-07-20',800,'approved'), (4,1,'2024-09-10',200,'rejected'), (5,2,'2024-04-01',400,'approved'), (6,2,'2024-06-01',600,'approved'), (7,3,'2024-08-01',150,'approved')]

Output: policyholder_id | name | total_claims 1 | Alice Johnson | 4

Alice Johnson has 4 claims in the last 12 months (more than 3), so she appears in results. Bob Smith has 2 claims and Carol White has 1 — both are excluded.

Input: policyholders: [(1, 'Dan Lee'), (2, 'Eva Green')] claims (all within last 12 months): [(1,1,'2024-02-01',100,'approved'), (2,1,'2024-04-01',200,'approved'), (3,1,'2024-06-01',300,'approved'), (4,1,'2024-08-01',400,'approved'), (5,1,'2024-10-01',500,'approved'), (6,2,'2024-01-01',700,'approved'), (7,2,'2024-03-01',800,'approved'), (8,2,'2024-05-01',900,'approved'), (9,2,'2024-07-01',1000,'approved'), (10,2,'2024-09-01',1100,'approved'), (11,2,'2024-11-01',1200,'approved')]

Both policyholders have more than 3 claims. Eva Green leads with 6, Dan Lee has 5. Results are ordered by total_claims descending.

Input: policyholders: [(1, 'Frank Moore')] claims: [(1,1,'2022-01-01',500,'approved'), (2,1,'2022-06-01',300,'approved'), (3,1,'2022-11-01',400,'approved'), (4,1,'2023-01-01',200,'approved')]

Output: (empty result set)

All of Frank Moore's claims are older than 12 months from today, so his recent claim count is 0. No policyholder qualifies, so the result is empty.

Sample Test Cases

Case 1 Input: policyholders: [(1, 'Alice Johnson'), (2, 'Bob Smith'), (3, 'Carol White')] claims (all within last 12 months): [(1,1,4,'approved'), (2,1,4,'pending'), (3,1,4,'approved'), (4,1,4,'rejected'), (5,2,2,'approved'), (6,2,2,'approved'), (7,3,1,'approved')]

Expected: policyholder_id=1, name=Alice Johnson, total_claims=4

Case 2 Input: policyholders: [(1, 'Dan Lee'), (2, 'Eva Green')] claims (all within last 12 months): [Dan Lee: 5 claims, Eva Green: 6 claims]

Expected: policyholder_id=2, name=Eva Green, total_claims=6; policyholder_id=1, name=Dan Lee, total_claims=5

Case 3 Input: policyholders: [(1, 'Frank Moore')] claims: [all claims older than 12 months, 4 total]

Expected: (empty result set)

Loading editor...

Expected: policyholder_id=1, name=Alice Johnson, total_claims=4

Case 2 Input: policyholders: [(1, 'Dan Lee'), (2, 'Eva Green')] claims (all within last 12 months): [Dan Lee: 5 claims, Eva Green: 6 claims]

Expected: policyholder_id=2, name=Eva Green, total_claims=6; policyholder_id=1, name=Dan Lee, total_claims=5

Case 3 Input: policyholders: [(1, 'Frank Moore')] claims: [all claims older than 12 months, 4 total]

Expected: (empty result set)

Problem

Schema

Table: policyholders

Column

Type

Description

id

INT

Primary key

name

VARCHAR

Full name

email

VARCHAR

Email address

Table: claims

Column

Type

Description

id

INT

Primary key

policyholder_id

INT

Foreign key to policyholders

claim_date

DATE

Date the claim was filed

amount

DECIMAL

Claim amount in USD

status

VARCHAR

'approved', 'pending', or 'rejected'

- 1 <= number of policyholders <= 10^6 - 0 <= number of claims per policyholder <= 10^4 - claim_date is a valid DATE in the past or present - All policyholder_id values in claims reference an existing policyholder - amount >= 0 - status is one of: 'approved', 'pending', 'rejected'

High-Frequency Claimants

Problem

Schema

Table: policyholders

Table: claims

Output Format

Notes

Approach Hints

Constraints

Examples

Sample Test Cases

High-Frequency Claimants

Problem

Schema

Table: policyholders

Table: claims

Output Format

Notes

Approach Hints

Constraints

Examples

Sample Test Cases

Table: `policyholders`

Table: `claims`

Table: `policyholders`

Table: `claims`