2,170 answered market questions

SELECT
    formatDateTime(cal.day, '%W')   AS weekday,
    countIf(s.sessions > 0)         AS trading_days,
    count()                         AS calendar_days
FROM
(
    SELECT toDate('2019-01-01') + arrayJoin(range(365)) AS day
) AS cal
LEFT JOIN
(
    SELECT
        date     AS d,
        count()  AS sessions
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2019-01-01'
      AND date <  '2020-01-01'
    GROUP BY d
) AS s ON s.d = cal.day
GROUP BY weekday
ORDER BY min(toDayOfWeek(cal.day))