ఈ నెలలో అత్యధికంగా కదిలిన స్టాక్‌లు

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT ticker,
       round((argMax(c, d) / argMin(o, d) - 1) * 100, 1) AS month_return_pct,
       round(sum(dollars) / 1e9, 1) AS month_dollar_bn
FROM (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMin(toFloat64(open), window_start) AS o,
           argMax(toFloat64(close), window_start) AS c,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 34 DAY
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
      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
      AND ticker NOT IN ('SPCX')
      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 IN (SELECT ticker FROM global_markets.stocks_ratios
                     WHERE market_cap > 0)
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 45 AND today())
    GROUP BY ticker, d
)
GROUP BY ticker
HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
ORDER BY argMax(c, d) / argMin(o, d) DESC
LIMIT 10

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT ticker,
       round((argMax(c, d) / argMin(o, d) - 1) * 100, 1) AS month_return_pct,
       round(sum(dollars) / 1e9, 1) AS month_dollar_bn
FROM (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMin(toFloat64(open), window_start) AS o,
           argMax(toFloat64(close), window_start) AS c,
           sum(toFloat64(close) * toFloat64(volume)) AS dollars
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 34 DAY
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
      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
      AND ticker NOT IN ('SPCX')
      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 IN (SELECT ticker FROM global_markets.stocks_ratios
                     WHERE market_cap > 0)
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 45 AND today())
    GROUP BY ticker, d
)
GROUP BY ticker
HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
ORDER BY argMax(c, d) / argMin(o, d) ASC
LIMIT 10

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT session,
       round(anyIf(cum_pct, ticker = 'SPY'), 2) AS sp500_pct,
       round(anyIf(cum_pct, ticker = 'QQQ'), 2) AS nasdaq100_pct,
       round(anyIf(cum_pct, ticker = 'DIA'), 2) AS dow_pct,
       round(anyIf(cum_pct, ticker = 'IWM'), 2) AS russell2000_pct
FROM (
    SELECT ticker,
           formatDateTime(d, '%Y-%m-%d') AS session,
           100 * (c / first_value(o) OVER (PARTITION BY ticker ORDER BY d) - 1) AS cum_pct
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMin(toFloat64(open), window_start) AS o,
               argMax(toFloat64(close), window_start) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 34 DAY
          AND ticker IN ('SPY', 'QQQ', 'DIA', 'IWM')
          AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
          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
    )
)
GROUP BY session
ORDER BY session

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT ticker,
       round((argMax(c, d) / argMin(o, d) - 1) * 100, 1) AS month_return_pct
FROM (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS d,
           argMin(toFloat64(open), window_start) AS o,
           argMax(toFloat64(close), window_start) AS c
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 34 DAY
      AND ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'GOOGL', 'META', 'TSLA', 'AVGO')
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
      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
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 45 AND today())
    GROUP BY ticker, d
)
GROUP BY ticker
HAVING count() >= 15
ORDER BY argMax(c, d) / argMin(o, d) DESC

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
)
SELECT tup.1 AS return_bucket,
       tup.3 AS stocks
FROM (
    SELECT arrayJoin(arrayZip(
               ['down 20% or more', 'down 10% to 20%', 'down 0% to 10%',
                'up 0% to 10%', 'up 10% to 20%', 'up 20% or more'],
               [1, 2, 3, 4, 5, 6],
               [countIf(r <= -20), countIf(r > -20 AND r <= -10), countIf(r > -10 AND r < 0),
                countIf(r >= 0 AND r < 10), countIf(r >= 10 AND r < 20), countIf(r >= 20)])) AS tup
    FROM (
        SELECT ticker,
               (argMax(c, d) / argMin(o, d) - 1) * 100 AS r
        FROM (
            SELECT ticker,
                   toDate(toTimeZone(window_start, 'America/New_York')) AS d,
                   argMin(toFloat64(open), window_start) AS o,
                   argMax(toFloat64(close), window_start) AS c,
                   sum(toFloat64(close) * toFloat64(volume)) AS dollars
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE window_start >= now() - INTERVAL 34 DAY
              AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
              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
              AND ticker NOT IN ('SPCX')
              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 IN (SELECT ticker FROM global_markets.stocks_ratios
                             WHERE market_cap > 0)
              AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                                 WHERE execution_date BETWEEN today() - 45 AND today())
            GROUP BY ticker, d
        )
        GROUP BY ticker
        HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
    )
)
ORDER BY tup.2

WITH window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 DAY
                      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 d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
),
screened AS (
    SELECT ticker
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMin(toFloat64(open), window_start) AS o,
               sum(toFloat64(close) * toFloat64(volume)) AS dollars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 34 DAY
          AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
          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
          AND ticker NOT IN ('SPCX')
          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 IN (SELECT ticker FROM global_markets.stocks_ratios
                         WHERE market_cap > 0)
          AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                             WHERE execution_date BETWEEN today() - 45 AND today())
        GROUP BY ticker, d
    )
    GROUP BY ticker
    HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
)
SELECT formatDateTime(w.first_d, '%b %e') AS first_session_date,
       formatDateTime(w.last_d, '%b %e') AS last_session_date,
       w.n AS sessions,
       (SELECT count() FROM global_markets.stocks_ratios WHERE market_cap > 0) AS companies_on_file,
       (SELECT count() FROM screened) AS companies_screened
FROM (SELECT min(d) AS first_d, max(d) AS last_d, count() AS n FROM window_days) AS w