3,256 answered market questions

WITH sess AS (
    SELECT ticker, toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), toTimeZone(window_start, 'America/New_York')) AS o,
        argMax(toFloat64(close), toTimeZone(window_start, 'America/New_York')) AS c,
        sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 12 DAY
      AND ticker NOT IN ('KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits WHERE execution_date BETWEEN today() - 12 AND today())
      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 ticker, d
),
wk AS (
    SELECT ticker, argMin(o, d) AS wo, argMax(c, d) AS wc, sum(dollars) AS wd, count() AS n
    FROM sess WHERE d >= (SELECT max(d) FROM sess) - 6 GROUP BY ticker
)
SELECT ticker,
    round((wc / wo - 1) * 100, 1) AS week_return_pct,
    round(wd / 1e9, 1) AS week_dollar_bn,
    (SELECT formatDateTime(max(d), '%b %e') FROM sess) AS week_ending
FROM wk
WHERE wd >= 1000000000 AND wo >= 10 AND n >= 4
ORDER BY (wc / wo - 1) ASC
LIMIT 10