Rolling 7-Day Returns by Segment | Dataford Interview Questions

Write a SQL query using window functions to calculate a rolling 7-day average of portfolio returns by user segment.

Use the provided user and portfolio return data. Exclude returns whose user has no known segment and ignore NULL return values.

Output

One row per user segment and return date with at least one non-NULL return.
Columns: segment, return_date, daily_avg_return, and rolling_7_day_avg.
Calculate the rolling average over the current date and the preceding six calendar days. Sort by segment, then return_date ascending.

users

portfolio_returns

Column	Type	Description
return_idPK	INT	Unique return record identifier
user_id	INT	User associated with the return
return_date	DATE	Date of the portfolio return
return_pct	NUMERIC(8,5)	Portfolio return expressed as a decimal

Your question is Rolling 7-Day Returns by Segment. 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.

Write a SQL query using window functions to calculate a rolling 7-day average of portfolio returns by user segment.

Use the provided user and portfolio return data. Exclude returns whose user has no known segment and ignore NULL return values.

One row per user segment and return date with at least one non-NULL return.
Columns: segment, return_date, daily_avg_return, and rolling_7_day_avg.
Calculate the rolling average over the current date and the preceding six calendar days. Sort by segment, then return_date ascending.

users

portfolio_returns

Column	Type	Description
return_idPK	INT	Unique return record identifier
user_id	INT	User associated with the return
return_date	DATE	Date of the portfolio return
return_pct	NUMERIC(8,5)	Portfolio return expressed as a decimal

Your question is Rolling 7-Day Returns by Segment. 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.