New Analysis Impact | Dataford Interview Questions

Tell me about a time that you created a new piece of analysis and how it contributed to the organization.

For this SQL exercise, demonstrate the analysis with the provided data. Write a query that summarizes paid claims and active-member coverage for every active plan.

Output

One row per active plan, including plans without qualifying claims or members.
Return plan_name, active_member_count, claim_count, paid_amount, members_with_claims, and claims_per_100_members.
Include paid claims dated from January 1 through March 31, 2025, and order by plan_name ascending.

Schema

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
plan_name	VARCHAR(100)	Plan name
state	VARCHAR(2)	Two-letter plan state
status	VARCHAR(20)	Current plan status

members

Column	Type	Description
member_idPK	INT	Unique member identifier
plan_id	INT	Plan assigned to the member
member_status	VARCHAR(20)	Current member status

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
member_id	INT	Member associated with the claim
claim_date	DATE	Date the claim was submitted
claim_status	VARCHAR(20)	Processing status of the claim
paid_amount	DECIMAL(12,2)	Amount paid for the claim

Your question is New Analysis Impact. 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