杠杆ETF收盘前再平衡机制

SELECT
    toString(date)                                                                                AS session_date,
    concat(monthName(date), ' ', toString(toDayOfMonth(date)), ', ', toString(toYear(date)))      AS move_label,
    if(move_pct >= 0, 'higher', 'lower')                                                          AS direction,
    abs(move_pct)                                                                                 AS abs_move_pct,
    round(abs(move_pct) * 2, 2)                                                                   AS trade_2x_pct_of_assets,
    round(abs(move_pct) * 6, 2)                                                                   AS trade_3x_pct_of_assets
FROM
(
    SELECT
        date,
        round(100 * (close_px / lagInFrame(close_px)
              OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1), 2)          AS move_pct
    FROM
    (
        SELECT
            date,
            max(toFloat64(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'QQQ'
          AND date >= '2024-12-16'
          AND date <= '2025-12-31'
        GROUP BY date
    )
)
WHERE date >= '2025-01-02'
ORDER BY abs_move_pct DESC
LIMIT 12

SELECT
    multiIf(abs(move_pct) < 0.5, 'under 0.5%',
            abs(move_pct) < 1.0, '0.5% to 1%',
            abs(move_pct) < 2.0, '1% to 2%',
            abs(move_pct) < 3.0, '2% to 3%',
                                 '3% or more')   AS move_bucket,
    count()                                      AS bucket_count,
    round(avg(abs(move_pct)) * 6, 2)             AS avg_3x_trade_pct
FROM
(
    SELECT
        date,
        round(100 * (close_px / lagInFrame(close_px)
              OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1), 2) AS move_pct
    FROM
    (
        SELECT
            date,
            max(toFloat64(close)) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'QQQ'
          AND date >= '2024-12-16'
          AND date <= '2025-12-31'
        GROUP BY date
    )
)
WHERE date >= '2025-01-02'
GROUP BY move_bucket
ORDER BY min(abs(move_pct))

SELECT
    b.ticker                                                                                     AS ticker,
    multiIf(b.ticker IN ('TQQQ', 'SQQQ', 'SOXL', 'SPXL', 'UPRO'),
            'daily reset fund', 'plain index fund')                                              AS fund_type,
    round(100 * sum(p.closing_print) / sum(b.session_shares + p.closing_print), 2)              AS closing_print_share_pct,
    round(100 * sum(b.stretch_shares + p.closing_print) / sum(b.session_shares + p.closing_print), 2) AS closing_stretch_share_pct
FROM
(
    SELECT
        ticker,
        d,
        sum(v)                    AS session_shares,
        sumIf(v, et_minute >= 930) AS stretch_shares
    FROM
    (
        SELECT
            ticker,
            toDate(toTimeZone(window_start, 'America/New_York'))        AS d,
            toFloat64(volume)                                           AS v,
            toHour(toTimeZone(window_start, 'America/New_York')) * 60
              + toMinute(toTimeZone(window_start, 'America/New_York')) AS et_minute
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker IN ('TQQQ', 'SQQQ', 'SOXL', 'SPXL', 'UPRO', 'QQQ', 'SPY', 'XLK')
          AND window_start >= '2025-07-01 04:00:00'
          AND window_start <  '2026-01-01 05:00:00'
    )
    WHERE et_minute >= 570
      AND et_minute <= 959
    GROUP BY ticker, d
) AS b
INNER JOIN
(
    SELECT
        ticker,
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
        toFloat64(maxIf(size, has(conditions, 8)))            AS closing_print
    FROM global_markets.stocks_trades
    WHERE ticker IN ('TQQQ', 'SQQQ', 'SOXL', 'SPXL', 'UPRO', 'QQQ', 'SPY', 'XLK')
      AND sip_timestamp >= '2025-07-01 04:00:00'
      AND sip_timestamp <  '2026-01-01 05:00:00'
      AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
      AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) < 10
    GROUP BY ticker, d
    HAVING countIf(has(conditions, 8)) > 0
) AS p ON p.ticker = b.ticker AND p.d = b.d
GROUP BY ticker
ORDER BY closing_print_share_pct DESC

WITH
(
    SELECT date
    FROM
    (
        SELECT
            date,
            abs(close_px / lagInFrame(close_px)
                OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1) AS abs_move
        FROM
        (
            SELECT
                date,
                max(toFloat64(close)) AS close_px
            FROM global_markets.stocks_daily_aggs
            WHERE ticker = 'QQQ'
              AND date >= '2024-12-16'
              AND date <= '2025-12-31'
            GROUP BY date
        )
    )
    WHERE date >= '2025-01-02'
    ORDER BY abs_move DESC
    LIMIT 1
) AS reset_date
SELECT
    et_time,
    round(sumIf(shares, d = reset_date) / 1e6, 2) AS big_move_shares_m,
    round(avg(shares) / 1e6, 2)                   AS typical_shares_m
FROM
(
    SELECT
        formatDateTime(toTimeZone(window_start, 'America/New_York'), '%H:%i') AS et_time,
        toDate(toTimeZone(window_start, 'America/New_York'))                  AS d,
        toFloat64(volume)                                                     AS shares
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'QQQ'
      AND window_start >= '2025-01-01 05:00:00'
      AND window_start <  '2026-01-01 05:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 930 AND 959
    UNION ALL
    SELECT
        '16:00'                                               AS et_time,
        toDate(toTimeZone(sip_timestamp, 'America/New_York')) AS d,
        toFloat64(maxIf(size, has(conditions, 8)))            AS shares
    FROM global_markets.stocks_trades
    WHERE ticker = 'QQQ'
      AND sip_timestamp >= '2025-01-01 05:00:00'
      AND sip_timestamp <  '2026-01-01 05:00:00'
      AND toHour(toTimeZone(sip_timestamp, 'America/New_York')) = 16
      AND toMinute(toTimeZone(sip_timestamp, 'America/New_York')) < 10
    GROUP BY d
    HAVING countIf(has(conditions, 8)) > 0
)
GROUP BY et_time
ORDER BY et_time