Can you walk through a complex SQL query you have written recently in your career?

Sure. In my last role I wrote a query to generate a monthly sales report for a retail chain. The query used a Common Table Expression (CTE) to first aggregate daily sales per store, then joined that CTE to a promotions table to flag discounted days. I applied a window function to calculate a rolling 7‑day average of sales, and used a CASE expression to categorize stores into performance tiers. Indexes on the sales date and store_id columns were critical for performance; the final execution plan showed a single index seek followed by a small hash join. I also added a LIMIT clause to return only the top 10 stores, which reduced the result set size and improved response time. This example demonstrates how to combine CTEs, joins, window functions, and indexing to solve a real business problem efficiently.