WITH stock_prices AS (
  SELECT
    date,
    ticker,
    "close"
FROM
@kaggle.eren2222_nasdaq_nyse_nyse_a_otc_daily_stock_1962_2024.nyse_1962_2024
WHERE 
    ticker IN ('CMG', 'YUM', 'EAT', 'SHAK')
),
tortilla_prices AS (
  SELECT 
    CAST(year || '-' || month || '-' || day AS DATE) AS date,
    AVG(price_per_kilogram) AS daily_tortilla_price
  FROM 
    @kaggle.richave_tortilla_prices_in_mexico.tortilla_prices
  GROUP BY 
    CAST(year || '-' || month || '-' || day AS DATE)
),
combined_data AS (
  SELECT 
    sp.date,
    AVG(CASE WHEN sp.ticker = 'CMG' THEN sp."close" END) AS CMG_price,
    AVG(CASE WHEN sp.ticker = 'YUM' THEN sp."close" END) AS YUM_price,
    AVG(CASE WHEN sp.ticker = 'EAT' THEN sp."close" END) AS EAT_price,
    AVG(CASE WHEN sp.ticker = 'SHAK' THEN sp."close" END) AS SHAK_price,
    tp.daily_tortilla_price
  FROM 
    stock_prices sp
  INNER JOIN tortilla_prices tp 
    ON sp.date = tp.date
  GROUP BY 
    sp.date, tp.daily_tortilla_price
  ORDER BY 
    sp.date DESC
  LIMIT 500
)

SELECT 
  date,
  CMG_price,
  YUM_price,
  EAT_price,
  SHAK_price,
  daily_tortilla_price*10 AS tortilla_price
FROM 
  combined_data
ORDER BY 
  date ASC;