Members With More Than Three Claims | Dataford Interview Questions

Highmark Health analysts need to identify members whose claims activity exceeds three claims within a calendar month. Write a PostgreSQL query that summarizes qualifying member-month combinations.

Requirements

Group claims by member and calendar month using DATE_TRUNC.
Return only member-month groups with more than three claims using HAVING.
Include the member's name and plan type from members.
Sort the results by month and then member ID.

Claims with a missing service date cannot be assigned to a month and should not qualify. Claims whose member ID does not match members should not appear in the final output.

Schema

members

Column	Type	Description
member_idPK	INTEGER	Unique Highmark Health member identifier
member_name	VARCHAR(100)	Member display name
plan_type	VARCHAR(40)	Member health plan type

claims

Column	Type	Description
claim_idPK	INTEGER	Unique claim identifier
member_id	INTEGER	Member associated with the claim
service_date	DATE	Date medical services were provided
claim_status	VARCHAR(20)	Claim processing status

Your question is Members With More Than Three Claims. Start with the requirements and the two 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