WITH daily_metrics AS (
    SELECT 
        DATE(created_at) AS trade_date,
        symbol,
        COUNT(trade_id) AS total_trades,
        SUM(pnl_usd) AS net_pnl,
        AVG(pnl_usd) OVER(PARTITION BY symbol ORDER BY DATE(created_at) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg_pnl,
        DENSE_RANK() OVER(PARTITION BY DATE(created_at) ORDER BY SUM(pnl_usd) DESC) AS rank_in_day
    FROM trades
    WHERE status = 'FILLED'
    GROUP BY 1, 2, created_at, trade_id, pnl_usd
)
SELECT * FROM daily_metrics WHERE rank_in_day <= 3 ORDER BY trade_date DESC;