Filtering forecasts

Navigating timestamped forecasts in the forecasts dataset can be tricky. In the following query, we show how to select the most recent forecast for the 2024 Presidential campaign across all states.
SELECT
  *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY forecast_timestamp DESC) AS timestamp_rank,
  FROM (
    SELECT
      campaign_id,
      MAX(timestamp) AS forecast_timestamp,
      election_date,
      office_name,
      candidate_name_last,
      projected_vote_share,
      margin_of_error,
      probability_of_winning
    FROM
      `deck-236019.hubble_forecast.forecasted_outcomes`
    WHERE
      election_stage = 'general'
      AND candidate_party = 'DEMOCRATIC'
      AND office_name = 'US PRESIDENT'
    GROUP BY
      campaign_id,
      election_date,
      office_name,
      candidate_name_last,
      projected_vote_share,
      margin_of_error,
      probability_of_winning))
WHERE
  timestamp_rank = 1
​

Voters to districts

District rollup examples

Last modified 2mo ago