Figure out predict on multiple series

[sipemu]

When fitting a regression model on a training set with an ID column unique_id, then multiple scenarios may occur in the prediction step:

• There may be IDs in the test set with no equivalents in the training set or vice versa -> An INNER JOIN approach could solve this.

[sipemu]

A possible solution would be using lateral joins:

CREATE OR REPLACE TABLE insnample_predictions AS (
    WITH x_matrix AS (
        SELECT
            unique_id,
            LIST([trend, COLUMNS('months_*')])::DOUBLE[][] AS x 
        FROM forecast_features
        GROUP BY unique_id
    )
    SELECT
        unique_id,
        predicted AS y_hat,
        ci_lower AS lower_bound_95,
        ci_upper AS upper_bound_95,
        observation_id
    FROM ols_coefficients AS m
    JOIN x_matrix AS x USING (unique_id)
    CROSS JOIN LATERAL model_predict(
        m.model.intercept,
        m.model.coefficients,
        m.model.mse,
        m.model.x_train_means,
        m.model.coefficient_std_errors,
        m.model.intercept_std_error,
        m.model.df_residual,
        x.x,            -- <- the matrix from the CTE
        0.95,
        'prediction'
    ) AS p ORDER BY unique_id, observation_id
);