weekly_share

SELECT
    bucket,
    count()                                                          AS underlyings
FROM
(
    SELECT
        underlying_symbol,
        arrayJoin([
            'A. All underlyings with a traded contract',
            multiIf(non_monthly_dates = 0, 'B. Third Friday only (monthly)',
                    weekdays_used >= 5,    'D. Weekly, every weekday Mon to Fri',
                                           'C. Weekly or quarterly, not every weekday')
        ])                                                           AS bucket
    FROM
    (
        SELECT
            underlying_symbol,
            uniqExactIf(expiration_date,
                NOT (toDayOfWeek(expiration_date) = 5
                     AND toDayOfMonth(expiration_date) BETWEEN 15 AND 21)) AS non_monthly_dates,
            uniqExact(toDayOfWeek(expiration_date))                        AS weekdays_used
        FROM global_markets.options_greeks
        WHERE date >= today() - 35
          AND date < today()
          AND expiration_date < today() + 60
          AND toDayOfWeek(expiration_date) <= 5
          AND volume > 0
          AND underlying_symbol NOT IN ('SPCX')
        GROUP BY underlying_symbol
    )
)
GROUP BY bucket
ORDER BY bucket