Writing SQL for Membership and Claims | Dataford Interview Questions

Write a statement to get total membership for x service dates, and another to get count of claims and total members for x service dates when the dataset has duplicates.

For this exercise, use the distinct service dates present in the supplied data. Repeated records should be represented by the most recent ingestion for the same business record.

Output

Return one row per service date with service_date, total_members, claim_count, and claim_members.
Include only dates represented in either table, exclude duplicate earlier ingestions, and sort by service_date ascending.

Schema

membership

Column	Type	Description
ingestion_idPK	INTEGER	Unique ingestion row identifier; higher values are more recent.
member_id	INTEGER	Centene member identifier.
service_date	DATE	Date for which membership coverage is reported.
plan_id	VARCHAR(30)	Health plan identifier.
coverage_status	VARCHAR(20)	Coverage state for the member on the service date.

claims

Column	Type	Description
ingestion_idPK	INTEGER	Unique ingestion row identifier; higher values are more recent.
claim_id	VARCHAR(30)	Business identifier for a claim, repeated across duplicate ingestions.
member_id	INTEGER	Centene member associated with the claim.
service_date	DATE	Date on which the claim service occurred.
claim_status	VARCHAR(20)	Current status recorded for the claim ingestion.

Your question is Writing SQL for Membership and 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