Analyze Time Series Data | Dataford Interview Questions

How do you approach the analysis of time series data?

Write a PostgreSQL query that summarizes the supplied time series observations by calendar month. Include only valid observations with non-null values, while retaining series that have no qualifying observations. Mark the latest available month for each series.

Output

One row per series and available month, plus one null-month row for series without qualifying observations.
Columns: series_id, series_name, month_start, average_value, observation_count, and is_latest_month.
Order by series_id, then month_start ascending, with null months last.

Schema

time_series

Column	Type	Description
series_idPK	INT	Unique identifier for a time series
series_name	VARCHAR(100)	Human-readable name of the time series
frequency	VARCHAR(20)	Expected observation frequency
is_active	BOOLEAN	Whether the series is currently active

observations

Column	Type	Description
observation_idPK	INT	Unique identifier for an observation
series_id	INT	Referenced time series identifier
observed_at	DATE	Date on which the value was observed
value	NUMERIC(10,2)	Observed numeric value
quality_status	VARCHAR(20)	Validation status of the observation

Your question is Analyze Time Series Data. 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