session_clock_lagos

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    formatDateTime(day, '%Y-%m')      AS month,
    argMax(month_label, day)          AS month_label,
    argMax(open_label, day)           AS open_lagos,
    argMax(close_label, day)          AS close_lagos,
    argMax(open_hours, day)           AS open_lagos_hours,
    argMax(close_hours, day)          AS close_lagos_hours
FROM
(
    SELECT
        day,
        formatDateTime(day, '%b %Y')                                    AS month_label,
        formatDateTime(regular_open, '%H:%i')                           AS open_label,
        formatDateTime(regular_close, '%H:%i')                          AS close_label,
        round(toHour(regular_open) + toMinute(regular_open) / 60, 2)    AS open_hours,
        round(toHour(regular_close) + toMinute(regular_close) / 60, 2)  AS close_hours
    FROM
    (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York'))                AS day,
            toTimeZone(min(window_start), 'Africa/Lagos')                       AS regular_open,
            toTimeZone(max(window_start) + toIntervalMinute(1), 'Africa/Lagos') AS regular_close
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 400
          AND window_start <  today() - 3
          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
        GROUP BY day
        HAVING count() >= 380
    )
)
GROUP BY month
ORDER BY month