2,358 answered market questions

SELECT
    sector,
    quantileExact(0.5)(gap_days)   AS median_gap_days,
    min(gap_days)                  AS shortest_gap_days,
    max(gap_days)                  AS longest_gap_days,
    count()                        AS payouts_counted
FROM
(
    SELECT
        multiIf(
            ticker IN ('O', 'PLD', 'AMT', 'SPG', 'WELL', 'PSA', 'EQIX', 'DLR', 'VICI', 'CCI', 'AVB', 'EQR'),      'REITs',
            ticker IN ('DUK', 'SO', 'NEE', 'D', 'AEP', 'XEL', 'ED', 'EXC', 'SRE', 'PEG', 'WEC', 'ETR'),          'Utilities',
            ticker IN ('AAPL', 'MSFT', 'AVGO', 'CSCO', 'ORCL', 'TXN', 'QCOM', 'IBM', 'ADP', 'ACN', 'ADI', 'INTU'), 'Tech',
            ticker IN ('JPM', 'BAC', 'WFC', 'C', 'GS', 'MS', 'USB', 'PNC', 'TFC', 'BLK', 'AXP', 'SCHW'),          'Banks and financials',
            ticker IN ('KO', 'PEP', 'PG', 'WMT', 'COST', 'MDLZ', 'CL', 'KMB', 'PM', 'MO', 'GIS', 'HSY'),          'Consumer staples',
            ticker IN ('JNJ', 'PFE', 'MRK', 'ABBV', 'ABT', 'LLY', 'AMGN', 'BMY', 'MDT', 'GILD', 'CVS', 'UNH'),    'Healthcare',
            ticker IN ('XOM', 'CVX', 'COP', 'EOG', 'SLB', 'KMI', 'WMB', 'OKE', 'PSX', 'VLO', 'MPC', 'DVN'),       'Energy',
            'other')                                         AS sector,
        dateDiff('day', ex_dividend_date, pay_date)          AS gap_days
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= today() - 730
      AND ex_dividend_date <  today()
      AND pay_date > ex_dividend_date
      AND cash_amount > 0
    GROUP BY ticker, ex_dividend_date, pay_date
)
WHERE sector != 'other'
GROUP BY sector
ORDER BY indexOf(['REITs', 'Utilities', 'Tech', 'Banks and financials', 'Consumer staples', 'Healthcare', 'Energy'], sector)