WITH monthly_tortilla_prices AS (
  SELECT 
    strftime(CAST(year || '-' || month || '-' || day AS DATE), '%Y-%m') AS date_key,
    AVG(price_per_kilogram) AS monthly_tortilla_price
  FROM 
    @kaggle.richave_tortilla_prices_in_mexico.tortilla_prices
  GROUP BY 
    strftime(CAST(year || '-' || month || '-' || day AS DATE), '%Y-%m')
),
monthly_commodity_prices AS (
  SELECT 
    strftime(date, '%Y-%m') AS date_key,
    AVG(gold) AS avg_gold,
    AVG(wti_crude) AS avg_wti_crude,
    AVG(brent_crude) AS avg_brent_crude,
    AVG(soybeans) AS soybeans_price,
    AVG(corn) AS corn_price,
    AVG(copper) AS avg_copper,
    AVG(silver) AS avg_silver,
    AVG(low_sulphur_gas_oil) AS avg_low_sulphur_gas_oil,
    AVG(live_cattle) AS avg_live_cattle,
    AVG(soybean_oil) AS avg_soybean_oil,
    AVG(aluminium) AS avg_aluminium,
    AVG(soybean_meal) AS avg_soybean_meal,
    AVG(zinc) AS avg_zinc,
    AVG(uls_diesel) AS avg_uls_diesel,
    AVG(nickel) AS avg_nickel,
    AVG(wheat) AS wheat_price,
    AVG(sugar) AS sugar_price,
    AVG(gasoline) AS avg_gasoline,
    AVG(coffee) AS coffee_price,
    AVG(lean_hogs) AS avg_lean_hogs,
    AVG(hrw_wheat) AS hrw_wheat_price,
    AVG(cotton) AS avg_cotton
  FROM 
    @kaggle.debashish311601_commodity_prices.commodity_futures
  GROUP BY 
    strftime(date, '%Y-%m')
)

SELECT 
  mtp.date_key,
  mtp.monthly_tortilla_price,
  mcp.avg_gold,
  mcp.avg_wti_crude,
  mcp.avg_brent_crude,
  mcp.soybeans_price,
  mcp.corn_price,
  mcp.avg_copper,
  mcp.avg_silver,
  mcp.avg_low_sulphur_gas_oil,
  mcp.avg_live_cattle,
  mcp.avg_soybean_oil,
  mcp.avg_aluminium,
  mcp.avg_soybean_meal,
  mcp.avg_zinc,
  mcp.avg_uls_diesel,
  mcp.avg_nickel,
  mcp.wheat_price,
  mcp.sugar_price,
  mcp.avg_gasoline,
  mcp.coffee_price,
  mcp.avg_lean_hogs,
  mcp.hrw_wheat_price,
  mcp.avg_cotton
FROM 
  monthly_commodity_prices mcp
  INNER JOIN monthly_tortilla_prices mtp ON mcp.date_key = mtp.date_key
ORDER BY 
  mtp.date_key;