2,236 answered market questions

WITH px AS (
    SELECT toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(close, window_start) AS price,
           max(toDate(toTimeZone(window_start, 'America/New_York'))) AS last_day
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'INTC'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2021-01-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2024-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY month_start
),
dv AS (
    SELECT ex_dividend_date, cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'INTC'
      AND distribution_type = 'recurring'
      AND frequency = 4
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2020-01-01')
)
SELECT formatDateTime(px.month_start, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(px.month_start, 'MMMM yyyy') AS month_label,
       round(any(px.price), 2) AS price,
       round(argMax(dv.cash_amount, dv.ex_dividend_date), 4) AS quarterly_dividend_usd,
       round(argMax(dv.cash_amount, dv.ex_dividend_date) * 4 / any(px.price) * 100, 2) AS dividend_yield_pct
FROM px, dv
WHERE dv.ex_dividend_date <= px.last_day
GROUP BY px.month_start
ORDER BY px.month_start