BigQuery - Table "stations" must be qualified with a dataset (e.g. dataset.table)

Question

I have a task: run a SQL query on BigQuery that will return sudden breakdowns in weather stations in Poland in 2000.

Sudden weather breakdowns mean the fulfillment of three conditions:

• previous day without precipitation,
• rainfall greater than the moving average for the last week
• temperature at least 5 degrees lower than the average temperature for the last 4 days.

Here is my code:

WITH weather_data AS (
  SELECT
    w.usaf AS station_usaf,
    w.wban AS station_wban,
    w.date AS observation_date,
    w.prcp AS precipitation,
    w.temp AS temperature,
    LAG(w.prcp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date) AS prev_day_prcp,
    AVG(w.prcp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS avg_prcp_last_week,
    AVG(w.temp) OVER (PARTITION BY w.usaf, w.wban ORDER BY w.date ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS avg_temp_last_4_days

FROM `bigquery-public-data.noaa_gsod.gsod2000` w
LEFT JOIN `bigquery-public-data.noaa_gsod.stations` s
ON w.stn = s.usaf AND w.wban = s.wban
WHERE s.country = 'PL'
)

SELECT 
  s.station_name,
  s.country AS station_country,
  COUNT(*) AS number_of_occurrences
FROM
  weather_data AS wd
JOIN
  stations AS s
ON
  wd.station_usaf = s.usaf AND wd.station_wban = s.wban
WHERE
  wd.prev_day_prcp = 0 
  AND wd.precipitation > wd.avg_prcp_last_week 
  AND wd.temperature < wd.avg_temp_last_4_days - 5 
GROUP BY
  s.station_name,
  s.country
ORDER BY
  ROW_NUMBER() OVER (PARTITION BY s.station_name, s.country ORDER BY COUNT(*) DESC);

Error message:

Table "stations" must be qualified with a dataset (e.g. dataset.table)

I would need help on what needs to be changed in the code to make it work properly. Thank you

Answer 1

The database system can not find the table, you need to repeat the complte text for stations like

FROM
  weather_data AS wd
JOIN
  `bigquery-public-data.noaa_gsod.stations` AS s
ON
  wd.station_usaf = s.usaf AND wd.station_wban = s.wban