Rolling Average Debt-to-Equity | Dataford Interview Questions

Given a table of financial metrics, write a query using window functions to calculate a rolling average of debt-to-equity ratios.

Use a three-reporting-period rolling average separately for each issuer, including the current row and two preceding chronological rows. Include rows with NULL ratios, allowing the average calculation to follow PostgreSQL NULL behavior.

Output

One row per issuer and reporting date.
Columns: issuer_id, reporting_date, debt_to_equity_ratio, and rolling_avg_debt_to_equity.
Sort by issuer_id, then reporting_date ascending.

Schema

financial_metrics

Column	Type	Description
metric_idPK	INT	Unique financial metric record identifier
issuer_id	VARCHAR(20)	Identifier for the rated issuer
reporting_date	DATE	Period-ending date for the metric
debt_to_equity_ratio	DECIMAL(10,4)	Issuer debt-to-equity ratio for the reporting period

Your question is Rolling Average Debt-to-Equity. Start with the requirements and the one table 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

Given a table of financial metrics, write a query using window functions to calculate a rolling average of debt-to-equity ratios.

Output

One row per issuer and reporting date.
Columns: issuer_id, reporting_date, debt_to_equity_ratio, and rolling_avg_debt_to_equity.
Sort by issuer_id, then reporting_date ascending.

Schema

financial_metrics

Column	Type	Description
metric_idPK	INT	Unique financial metric record identifier
issuer_id	VARCHAR(20)	Identifier for the rated issuer
reporting_date	DATE	Period-ending date for the metric
debt_to_equity_ratio	DECIMAL(10,4)	Issuer debt-to-equity ratio for the reporting period

Your question is Rolling Average Debt-to-Equity. Start with the requirements and the one table 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.