権利落ち日とは？配当を受け取れる仕組みと日程

SELECT declaration_date,
       ex_dividend_date,
       record_date,
       pay_date,
       cash_amount
FROM global_markets.stocks_dividends
WHERE ticker = 'AAPL'
  AND distribution_type = 'recurring'
  AND ex_dividend_date <= today()
ORDER BY ex_dividend_date DESC
LIMIT 8

SELECT toYear(ex_dividend_date) AS year,
       count() AS dividends,
       round(100.0 * countIf(ex_dividend_date = record_date) / count(), 1) AS pct_ex_equals_record
FROM global_markets.stocks_dividends
WHERE currency = 'USD'
  AND ex_dividend_date >= '2021-01-01'
  AND ex_dividend_date <= today()
  AND record_date IS NOT NULL
GROUP BY year
ORDER BY year

SELECT count() AS dividends,
       round(quantile(0.5)(dateDiff('day', declaration_date, ex_dividend_date)), 0) AS median_declared_to_ex_days,
       round(quantile(0.5)(dateDiff('day', ex_dividend_date, pay_date)), 0) AS median_ex_to_pay_days,
       round(avg(dateDiff('day', ex_dividend_date, pay_date)), 1) AS avg_ex_to_pay_days
FROM global_markets.stocks_dividends
WHERE currency = 'USD'
  AND distribution_type = 'recurring'
  AND ex_dividend_date >= today() - INTERVAL 12 MONTH
  AND ex_dividend_date <= today()
  AND declaration_date IS NOT NULL
  AND pay_date IS NOT NULL
  AND pay_date >= ex_dividend_date

SELECT multiIf(dateDiff('day', ex_dividend_date, pay_date) <= 7, '1 week or less',
               dateDiff('day', ex_dividend_date, pay_date) <= 14, '8-14 days',
               dateDiff('day', ex_dividend_date, pay_date) <= 30, '15-30 days',
               '31+ days') AS wait_from_ex_to_pay,
       count() AS dividends,
       round(100.0 * count() / sum(count()) OVER (), 1) AS pct_of_dividends
FROM global_markets.stocks_dividends
WHERE currency = 'USD'
  AND distribution_type = 'recurring'
  AND ex_dividend_date >= today() - INTERVAL 12 MONTH
  AND ex_dividend_date <= today()
  AND pay_date IS NOT NULL
  AND pay_date >= ex_dividend_date
GROUP BY wait_from_ex_to_pay
ORDER BY min(dateDiff('day', ex_dividend_date, pay_date))

SELECT multiIf(frequency = 4, 'Quarterly',
               frequency = 12, 'Monthly',
               frequency = 1, 'Annual',
               frequency = 2, 'Semi-annual',
               frequency = 52, 'Weekly',
               'Other') AS payout_schedule,
       uniqExact(ticker) AS payers,
       round(100.0 * uniqExact(ticker) / sum(uniqExact(ticker)) OVER (), 1) AS pct_of_payers
FROM global_markets.stocks_dividends
WHERE currency = 'USD'
  AND distribution_type = 'recurring'
  AND ex_dividend_date >= today() - INTERVAL 12 MONTH
  AND ex_dividend_date <= today()
GROUP BY payout_schedule
ORDER BY payers DESC

WITH divs AS (
    SELECT 1 AS k, ex_dividend_date AS ex_date, cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'AAPL' AND distribution_type = 'recurring' AND ex_dividend_date <= today()
    ORDER BY ex_dividend_date DESC
    LIMIT 8
),
rth AS (
    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           toTimeZone(window_start, 'America/New_York') AS et_ts,
           open, close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'AAPL'
      AND window_start >= now() - INTERVAL 800 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
),
opens AS (SELECT 1 AS k, et_date, argMin(open, et_ts) AS day_open FROM rth GROUP BY et_date),
closes AS (SELECT 1 AS k, et_date, argMax(close, et_ts) AS day_close FROM rth GROUP BY et_date)
SELECT d.ex_date,
       c.day_close AS prior_session_close,
       round(c.day_close - d.cash_amount, 2) AS adjusted_reference,
       o.day_open AS ex_date_open,
       d.cash_amount AS dividend,
       round(o.day_open - c.day_close, 2) AS actual_overnight_change
FROM divs d
INNER JOIN opens o ON o.et_date = d.ex_date
ASOF INNER JOIN closes c ON c.k = d.k AND c.et_date < d.ex_date
ORDER BY d.ex_date DESC