האלף דולר הטובים והגרועים של יוני 2026

SELECT toString(toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York')))) AS period_start,
    round((argMaxIf(toFloat64(close), window_start, ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / argMinIf(toFloat64(open), window_start, ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) - 1) * 100, 1) AS spy_month_pct,
    round((maxIf(toFloat64(high), ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) - minIf(toFloat64(low), ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / argMinIf(toFloat64(open), window_start, ticker = 'SPY' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) * 100, 1) AS spy_range_pct,
    round((argMaxIf(toFloat64(close), window_start, ticker = 'NVDA' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / argMinIf(toFloat64(open), window_start, ticker = 'NVDA' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) - 1) * 100, 1) AS nvda_month_pct,
    round((maxIf(toFloat64(high), ticker = 'NVDA' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) - minIf(toFloat64(low), ticker = 'NVDA' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / argMinIf(toFloat64(open), window_start, ticker = 'NVDA' AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) * 100, 1) AS nvda_range_pct,
    uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), ticker = 'SPY') AS trading_days
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker IN ('SPY', 'NVDA')
  AND window_start >= toDateTime('2026-01-01 05:00:00') AND window_start < toDateTime('2026-07-01 04:00:00')
GROUP BY period_start
HAVING uniqExactIf(toDate(toTimeZone(window_start, 'America/New_York')), ticker = 'SPY') >= 17
ORDER BY period_start ASC

SELECT ticker AS contract,
    substring(ticker, -9, 1) = 'P' AS is_put,
    round(first_px, 2) AS first_price,
    round(last_px, 2) AS last_price,
    round(last_px / first_px, 1) AS multiple,
    toUInt32(floor(1000 / (first_px * 100))) AS contracts_for_a_thousand,
    round(floor(1000 / (first_px * 100)) * last_px * 100, 0) AS bought_end_value_usd,
    round(1000 * (last_px / first_px) - 1000, 0) AS sold_net_loss_usd,
    trades
FROM (
    SELECT ticker,
        toFloat64(argMin(price, (sip_timestamp, price))) AS first_px,
        toFloat64(argMax(price, (sip_timestamp, price))) AS last_px,
        count() AS trades
    FROM global_markets.options_trades
    WHERE ((startsWith(ticker, 'O:MU') AND length(ticker) = 19) OR (startsWith(ticker, 'O:NVDA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:TSLA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:SPY') AND length(ticker) = 20) OR (startsWith(ticker, 'O:QQQ') AND length(ticker) = 20) OR (startsWith(ticker, 'O:AAPL') AND length(ticker) = 21))
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY ticker
    HAVING count() >= 50 AND argMin(price, (sip_timestamp, price)) > 0
)
ORDER BY multiple DESC, contract ASC
LIMIT 5

SELECT
    toString(toDate(min(sip_timestamp))) AS first_print_date,
    formatDateTime(toTimeZone(min(sip_timestamp), 'America/New_York'), '%H:%i:%S') AS first_print_et,
    round(anyIf(price, rn = 1), 2) AS first_price,
    toUInt64(anyIf(size, rn = 1)) AS first_print_contracts,
    countIf(price <= 0.011) AS penny_prints_in_june,
    toUInt64(sumIf(size, price <= 0.011)) AS penny_contracts_in_june,
    round(anyIf(price, rn = 2), 2) AS second_price,
    toUInt32(dateDiff('second', min(sip_timestamp), anyIf(sip_timestamp, rn = 2))) AS seconds_to_second_print,
    round(argMax(price, (sip_timestamp, price)), 2) AS month_last_price,
    round(argMax(price, (sip_timestamp, price)) / anyIf(price, rn = 2), 1) AS second_print_multiple_to_last,
    round(floor(1000 / (anyIf(price, rn = 2) * 100)) * argMax(price, (sip_timestamp, price)) * 100, 0) AS thousand_at_second_print_end_usd
FROM (
    SELECT sip_timestamp, toFloat64(price) AS price, size,
        row_number() OVER (ORDER BY sip_timestamp, price) AS rn
    FROM global_markets.options_trades
    WHERE ticker = 'O:NVDA260629P00200000'
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
)

WITH (
    SELECT (ticker, first_px)
    FROM (
    SELECT ticker,
        toFloat64(argMin(price, (sip_timestamp, price))) AS first_px,
        toFloat64(argMax(price, (sip_timestamp, price))) AS last_px,
        count() AS trades
    FROM global_markets.options_trades
    WHERE ((startsWith(ticker, 'O:MU') AND length(ticker) = 19) OR (startsWith(ticker, 'O:NVDA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:TSLA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:SPY') AND length(ticker) = 20) OR (startsWith(ticker, 'O:QQQ') AND length(ticker) = 20) OR (startsWith(ticker, 'O:AAPL') AND length(ticker) = 21))
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY ticker
    HAVING count() >= 50 AND argMin(price, (sip_timestamp, price)) > 0
    )
    ORDER BY last_px / first_px DESC, ticker ASC
    LIMIT 1
) AS winner
SELECT toDate(sip_timestamp) AS date,
    toUInt64(sum(size)) AS contracts_traded,
    round(sum(toFloat64(price) * toFloat64(size)) * 100 / 1e6, 2) AS day_premium_usd_m,
    round(toFloat64(argMax(price, (sip_timestamp, price))), 2) AS day_last_price,
    round(floor(1000 / (winner.2 * 100)) * toFloat64(argMax(price, (sip_timestamp, price))) * 100, 0) AS position_value_usd,
    round(100 * toFloat64(argMax(price, (sip_timestamp, price))) / max(toFloat64(argMax(price, (sip_timestamp, price)))) OVER (), 1) AS pct_of_peak
FROM global_markets.options_trades
WHERE ticker = winner.1
  AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
GROUP BY date
ORDER BY date ASC

SELECT
    count() AS contracts_with_50_trades,
    countIf(substring(ticker, -9, 1) = 'P') AS put_contracts,
    countIf(last_px / first_px >= 100) AS up_100x_plus,
    countIf(last_px / first_px >= 10) AS up_10x_plus,
    countIf(last_px / first_px >= 10 AND substring(ticker, -9, 1) = 'P') AS up_10x_puts,
    countIf(last_px / first_px >= 10 AND substring(ticker, -9, 1) = 'C') AS up_10x_calls,
    countIf(last_px / first_px <= 0.1) AS down_90_pct_plus,
    countIf(last_px / first_px <= 0.1 AND substring(ticker, -9, 1) = 'C') AS down_90_calls,
    round(100.0 * countIf(last_px / first_px <= 0.1 AND substring(ticker, -9, 1) = 'C') / countIf(last_px / first_px <= 0.1), 1) AS down_90_call_share_pct,
    countIf(last_px <= 0.02) AS ended_at_two_cents_or_less
FROM (
    SELECT ticker,
        toFloat64(argMin(price, (sip_timestamp, price))) AS first_px,
        toFloat64(argMax(price, (sip_timestamp, price))) AS last_px,
        count() AS trades
    FROM global_markets.options_trades
    WHERE ((startsWith(ticker, 'O:MU') AND length(ticker) = 19) OR (startsWith(ticker, 'O:NVDA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:TSLA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:SPY') AND length(ticker) = 20) OR (startsWith(ticker, 'O:QQQ') AND length(ticker) = 20) OR (startsWith(ticker, 'O:AAPL') AND length(ticker) = 21))
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY ticker
    HAVING count() >= 50 AND argMin(price, (sip_timestamp, price)) > 0
)

SELECT ticker AS contract,
    round(first_px, 2) AS first_price,
    round(last_px, 2) AS last_price,
    toUInt32(floor(1000 / (first_px * 100))) AS contracts_a_thousand_bought,
    round(floor(1000 / (first_px * 100)) * last_px * 100, 0) AS ending_value_usd
FROM (
    SELECT ticker,
        toFloat64(argMin(price, (sip_timestamp, price))) AS first_px,
        toFloat64(argMax(price, (sip_timestamp, price))) AS last_px,
        count() AS trades
    FROM global_markets.options_trades
    WHERE ((startsWith(ticker, 'O:MU') AND length(ticker) = 19) OR (startsWith(ticker, 'O:NVDA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:TSLA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:SPY') AND length(ticker) = 20) OR (startsWith(ticker, 'O:QQQ') AND length(ticker) = 20) OR (startsWith(ticker, 'O:AAPL') AND length(ticker) = 21))
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY ticker
    HAVING count() >= 50 AND argMin(price, (sip_timestamp, price)) > 0
)
WHERE last_px <= 0.02 AND floor(1000 / (first_px * 100)) >= 1
ORDER BY first_px DESC, contract ASC
LIMIT 1

SELECT
    multiIf(startsWith(ticker, 'O:NVDA'), 'NVDA', startsWith(ticker, 'O:TSLA'), 'TSLA', startsWith(ticker, 'O:AAPL'), 'AAPL', startsWith(ticker, 'O:SPY'), 'SPY', startsWith(ticker, 'O:QQQ'), 'QQQ', 'MU') AS root,
    count() AS contracts,
    countIf(last_px / first_px <= 0.1) AS down_90_pct_plus,
    round(100.0 * countIf(last_px / first_px <= 0.1) / count(), 1) AS pct_of_root_wiped,
    round(100.0 * countIf(last_px / first_px <= 0.1) / sum(countIf(last_px / first_px <= 0.1)) OVER (), 1) AS share_of_all_down_90_pct,
    countIf(last_px / first_px >= 10) AS up_10x_plus
FROM (
    SELECT ticker,
        toFloat64(argMin(price, (sip_timestamp, price))) AS first_px,
        toFloat64(argMax(price, (sip_timestamp, price))) AS last_px,
        count() AS trades
    FROM global_markets.options_trades
    WHERE ((startsWith(ticker, 'O:MU') AND length(ticker) = 19) OR (startsWith(ticker, 'O:NVDA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:TSLA') AND length(ticker) = 21) OR (startsWith(ticker, 'O:SPY') AND length(ticker) = 20) OR (startsWith(ticker, 'O:QQQ') AND length(ticker) = 20) OR (startsWith(ticker, 'O:AAPL') AND length(ticker) = 21))
      AND sip_timestamp >= toDateTime64('2026-06-01 00:00:00', 9) AND sip_timestamp < toDateTime64('2026-07-01 00:00:00', 9)
    GROUP BY ticker
    HAVING count() >= 50 AND argMin(price, (sip_timestamp, price)) > 0
)
GROUP BY root
ORDER BY down_90_pct_plus DESC, root ASC