Find Trends and Insights | Dataford Interview Questions

How would you analyze a dataset to find trends and actionable insights?

Using the supplied policies and claims tables, write a PostgreSQL query that summarizes monthly claim activity by state and identifies meaningful changes between reported months.

Output

One row per state and claim month, excluding claims that cannot be matched to a policy.
Columns: state, claim_month, claim_count, total_claim_amount, avg_claim_amount, previous_claim_count, and trend_action.
Sort by state ascending, with NULL states last, then claim month ascending.

Schema

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
state	VARCHAR(2)	Two-character policy state
policy_type	VARCHAR(30)	Type of insurance policy
effective_date	DATE	Policy effective date

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
policy_id	INT	Referenced policy identifier
claim_date	DATE	Date the claim was reported
claim_amount	DECIMAL(10,2)	Claimed dollar amount
status	VARCHAR(20)	Current claim status

Your question is Find Trends and Insights. 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