Round #567

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 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<List<Customer>> GetTopCustomersBySpendAsync(int year, int topN) { try { var query = _context.Customers .Include(c => c.Orders) .ThenInclude(o => o.OrderDetails) .Where(c => c.Orders.Any(o => o.OrderDate.Year == year)) .OrderByDescending(c => c.Orders .Where(o => o.OrderDate.Year == year) .Sum(o => o.OrderDetails.Sum(od => od.UnitPrice * od.Quantity))) .Take(topN); return await query.ToListAsync(); } catch (Exception ex) { _logger.LogError(ex, "Error retrieving top customers by spend"); throw; } } } public class Customer { public int Id { get; set; } public string Name { get; set; } public ICollection<Order> Orders { get; set; } } public class Order { public int Id { get; set; } public DateTime OrderDate { get; set; } public ICollection<OrderDetail> OrderDetails { get; set; } } public class OrderDetail { public int Id { get; set; } public decimal UnitPrice { get; set; } public int Quantity { get; set; } } ``` This C# code defines a `CustomerRepository` class that uses Entity Framework Core to query a database for the top 5 customers by total spend in a given year. The `GetTopCustomersBySpendAsync` method takes two parameters: `year` and `topN`, which specify the year and the number of top customers to return, respectively. The method uses LINQ to query the database, joining the `Customers`, `Orders`, and `OrderDetails` tables to calculate the total spend for each customer in the specified year. The results are ordered in descending order by total spend and limited to the top N customers. The method also includes error handling, logging any exceptions that occur during the query using the `ILogger` interface. To use this code, you would need to create a DbContext class that defines the database context and the DbSets for the `Customers`, `Orders`, and `OrderDetails` tables. You would also need to register the `CustomerRepository` class in the dependency injection container and provide an instance of the `ILogger` interface. For example, you could use the following SQL query to create the tables: ```sql CREATE TABLE Customers ( Id INT PRIMARY KEY, Name NVARCHAR(50) ); CREATE TABLE Orders ( Id INT PRIMARY KEY, CustomerId INT, OrderDate DATE, FOREIGN KEY (CustomerId) REFERENCES Customers(Id) ); CREATE TABLE OrderDetails ( Id INT PRIMARY KEY, OrderId INT, UnitPrice DECIMAL(10, 2), Quantity INT, FOREIGN KEY (OrderId) REFERENCES Orders(Id) ); ``` Note that this is just an example and you would need to modify the code and the database schema to fit your specific requirements.