ratiba_ya_wiki

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.

WITH
    dakika AS
    (
        SELECT
            window_start,
            toDate(toTimeZone(window_start, 'America/New_York')) AS siku,
            toHour(toTimeZone(window_start, 'America/New_York')) AS saa_et,
            volume
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime(today() - 371)
          AND window_start <  toDateTime(today() - 2)
          AND volume > 0
    ),
    mipaka AS
    (
        SELECT
            siku,
            argMaxIf(window_start, volume, saa_et < 12)  AS kufungua,
            argMaxIf(window_start, volume, saa_et >= 12) AS kufunga
        FROM dakika
        GROUP BY siku
        HAVING countIf(saa_et < 12) > 0
           AND countIf(saa_et >= 12) > 0
    ),
    eat AS
    (
        SELECT
            siku,
            toUInt32(round((toHour(toTimeZone(kufungua, 'Africa/Nairobi')) * 60
                          + toMinute(toTimeZone(kufungua, 'Africa/Nairobi'))) / 30) * 30) % 1440 AS dk_kufungua,
            toUInt32(round((toHour(toTimeZone(kufunga, 'Africa/Nairobi')) * 60
                          + toMinute(toTimeZone(kufunga, 'Africa/Nairobi'))) / 30) * 30) % 1440  AS dk_kufunga
        FROM mipaka
    )
SELECT
    toString(toMonday(siku)) AS week,
    concat(toString(toDayOfMonth(toMonday(siku))), ' ',
           ['Januari','Februari','Machi','Aprili','Mei','Juni','Julai','Agosti','Septemba','Oktoba','Novemba','Desemba'][toMonth(toMonday(siku))],
           ' ', toString(toYear(toMonday(siku)))) AS lebo,
    round(min(dk_kufungua) / 60, 2) AS saa_ya_kufunguliwa,
    round(max(dk_kufunga + if(dk_kufunga < dk_kufungua, 1440, 0)) / 60, 2) AS saa_ya_kufungwa
FROM eat
GROUP BY week, lebo
ORDER BY week