Not a comprehensive list — just the ones that give me real lift on real projects. Each pattern is a tool; pick the one that fits the problem.
You are a senior SQL engineer.
Your task: rewrite the query below to use a window function instead of a self-join.
Constraints: preserve the exact column order. Do not change column names.