Hubble by Deck
Search
⌃K

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
​