-- DECLARE start_date STRING DEFAULT '2021-09-01';
-- DECLARE end_date STRING DEFAULT '2021-09-30';

SELECT DISTINCT
  START_DATE AS Date,
  COUNT AS Count,
  AVERAGE_WT AS WaitingTime,
  cm.iso3  AS ISO3,
  cm.country_name_en AS Country,
  UNLOCODE AS UNLOCODE,
  LOCATION_NAME AS Port,
  LAT AS Lat,
  LON AS Lon
FROM
  `fao-maps.fao_marcura.waiting_time_joined` wt,
  `fao_common_dimensions.dim_country`  cm
WHERE
  wt.ISO3CODE = cm.iso3
  AND LAT IS NOT NULL
  AND LON IS NOT NULL
  AND COUNTRY IS NOT NULL
  AND UNLOCODE IS NOT NULL
  AND UNLOCODE <> "NULL"
  AND UNLOCODE <> "NA"
  AND START_DATE >= DATE(@start_date)
  AND START_DATE <= DATE(@end_date)
ORDER BY cm.country_name_en , wt.LOCATION_NAME, wt.START_DATE

