SET max_threads = 4;                                   -- keep parallelism reasonable
SET max_bytes_before_external_group_by = 500000000;    -- allow external spill if needed
SET max_bytes_before_external_sort     = 500000000;    -- allow external spill if needed

/* Light monthly summary: July 2025 */
WITH
  toDateTime('2025-07-01 00:00:00') AS month_from,
  toDateTime('2025-08-01 00:00:00') AS month_to

/* 1) Per-coin monthly baselines (cheap) */
, stats AS (
  SELECT
    coin,
    avg(volume_quote)            AS vol_avg_m,
    avg(toFloat64(trades_count)) AS trades_avg_m
  FROM pumpfinder.prices_1m
  WHERE minute_time >= month_from AND minute_time < month_to
  GROUP BY coin
)

/* 2) Candidate signals with tiny 10-row windows */
, candidates AS (
  SELECT
    p.coin,
    p.minute_time AS signal_time,
    p.close_price AS entry_price,
    p.volume_quote,
    toFloat64(p.trades_count) AS trades_now,
    if(p.volume_quote > 0, p.taker_buy_quote_vol / p.volume_quote, NULL) AS buy_pressure,
    countIf(p.diff_3min > 0)
      OVER (PARTITION BY p.coin ORDER BY p.minute_time ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS green10,
    round(sum(p.diff_3min)
      OVER (PARTITION BY p.coin ORDER BY p.minute_time ROWS BETWEEN 9 PRECEDING AND CURRENT ROW), 2) AS sum10_diff3,
    s.vol_avg_m, s.trades_avg_m,
    lagInFrame(p.minute_time, 1) OVER (PARTITION BY p.coin ORDER BY p.minute_time) AS prev_signal_time
  FROM pumpfinder.prices_1m AS p
  INNER JOIN stats AS s USING (coin)
  WHERE p.minute_time >= month_from AND p.minute_time < month_to
)

/* 3) Filter + 12h cooldown */
, signals AS (
  SELECT coin, signal_time, entry_price
  FROM candidates
  WHERE
    green10 = 10
    AND sum10_diff3 < 1.6 AND sum10_diff3 > 0
    AND ifNull(volume_quote / nullIf(vol_avg_m, 0), 0)   >= 1.5
    AND ifNull(trades_now  / nullIf(trades_avg_m, 0), 0) >= 1.3
    AND ifNull(buy_pressure, 0)                           >= 0.55
    AND (prev_signal_time IS NULL OR signal_time - prev_signal_time >= INTERVAL 12 HOUR)
)

/* 4) Compute +15m window results in a subquery */
, outcomes AS (
  SELECT
    s.coin,
    s.signal_time,
    s.entry_price,
    leadInFrame(b.close_price, 15) OVER (PARTITION BY b.coin ORDER BY b.minute_time) AS close_15m
  FROM signals s
  INNER JOIN pumpfinder.prices_1m b
    ON b.coin = s.coin AND b.minute_time = s.signal_time
)

/* 5) Final aggregation (no window funcs here) */
SELECT
  count()                                                     AS total_signals,
  uniqExact(coin)                                             AS coins_covered,
  round(sumIf(((close_15m / entry_price) - 1) * 100, close_15m IS NOT NULL), 4) AS total_profit,
  round(avgIf(((close_15m / entry_price) - 1) * 100, close_15m IS NOT NULL), 4) AS avg_profit,
  round(avgIf((((close_15m / entry_price) - 1) * 100) > 0, close_15m IS NOT NULL) * 100, 2) AS winning_trades_pct
FROM outcomes;
