SQL for Trend Analysis | Dataford Interview Questions

Write a SQL query to analyze a dataset and identify trends that could impact business decisions at MassMutual.

Use the provided policy and product data to evaluate monthly policy activity and premium trends during the first quarter of 2024. Include active and issued policies with non-null premiums, and exclude cancelled policies.

Output

One row per product category and month, with product_category, month, policy_count, total_premium, prior_month_premium, and premium_change_pct.
Include the prior available month for the same category and the percentage change, or NULL when unavailable.
Order by product_category, then month ascending.

Schema

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
product_id	INT	Referenced insurance product
issue_date	DATE	Date the policy was issued
status	VARCHAR(20)	Current policy status
premium	DECIMAL(12,2)	Policy premium amount

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Insurance product name
product_category	VARCHAR(50)	Product category used for trend analysis

Your question is SQL for Trend Analysis. 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