Round #784

Write a SQL query that returns the top 5 customers by total spend in 2025.

**SQL Query: Top 5 Customers by Total Spend in 2025** ```sql SELECT c.customer_name, SUM(o.order_total) AS total_spend FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE EXTRACT(YEAR FROM o.order_date) = 2025 GROUP BY c.customer_name ORDER BY total_spend DESC LIMIT 5; ``` **Explanation:** 1. We join the `customers` table with the `orders` table on the `customer_id` column. 2. We filter the orders to only include those made in the year 2025 using the `EXTRACT` function. 3. We group the results by the `customer_name` column. 4. We calculate the total spend for each customer using the `SUM` aggregation function. 5. We sort the results in descending order by total spend using the `ORDER BY` clause. 6. We limit the results to the top 5 customers using the `LIMIT` clause. **Assumptions:** * The `customers` table has a column named `customer_name`. * The `orders` table has columns named `customer_id`, `order_date`, and `order_total`. * The `order_date` column is of a date or timestamp data type. **Example Use Case:** Suppose we have the following data: `customers` table: | customer_id | customer_name | | --- | --- | | 1 | John Smith | | 2 | Jane Doe | | 3 | Bob Johnson | `orders` table: | order_id | customer_id | order_date | order_total | | --- | --- | --- | --- | | 1 | 1 | 2025-01-01 | 100.00 | | 2 | 1 | 2025-02-01 | 200.00 | | 3 | 2 | 2025-03-01 | 50.00 | | 4 | 3 | 2025-04-01 | 300.00 | | 5 | 1 | 2025-05-01 | 400.00 | Running the query would return: | customer_name | total_spend | | --- | --- | | John Smith | 700.00 | | Bob Johnson | 300.00 | | Jane Doe | 50.00 | | ... | ... | Note that the actual results would depend on the data in your tables.

```csharp using Microsoft.EntityFrameworkCore; using System; using System.Linq; using System.Threading.Tasks; using Microsoft.Extensions.Logging; public class CustomerRepository { private readonly DbContext _context; private readonly ILogger<CustomerRepository> _logger; public CustomerRepository(DbContext context, ILogger<CustomerRepository> logger) { _context = context; _logger = logger; } public async Task<string> GetTopCustomersBySpendAsync() { try { var query = @" SELECT TOP 5 c.CustomerName, SUM(o.OrderTotal) AS TotalSpend FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId WHERE YEAR(o.OrderDate) = 2025 GROUP BY c.CustomerName ORDER BY TotalSpend DESC"; var results = await _context.Database.SqlQuery(query).ToListAsync(); return string.Join(Environment.NewLine, results); } catch (Exception ex) { _logger.LogError(ex, "Error retrieving top customers by spend"); throw; } } } ```