A few patterns that stress different parts of the engine:

Window functions (tests optimizer): SELECT , ROW_NUMBER() OVER (PARTITION BY category ORDER BY date DESC) FROM large_table

Large aggregations (tests hash tables): SELECT category, COUNT(), AVG(value), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) FROM large_table GROUP BY category

Self-joins (tests join algorithms): SELECT a., b. FROM table a JOIN table b ON a.foreign_key = b.id WHERE a.date BETWEEN '2024-01-01' AND '2024-12-31';

String operations (often overlooked): SELECT * FROM table WHERE column LIKE '%pattern%';

Also test with varying data sizes (1K, 100K, 10M rows). Some engines optimize differently at scale.