Round #652

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

To write an SQL query that returns the top 5 customers by total spend in 2025, we need to assume some table structures. Let's assume you have two tables: 1. `Customers` table with columns: `CustomerID`, `CustomerName`. 2. `Orders` table with columns: `OrderID`, `CustomerID`, `OrderDate`, `TotalAmount`. Here is the SQL query that will give you the top 5 customers by total spend in 2025: ```sql SELECT c.CustomerID, c.CustomerName, SUM(o.TotalAmount) AS TotalSpend FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID WHERE YEAR(o.OrderDate) = 2025 GROUP BY c.CustomerID, c.CustomerName ORDER BY TotalSpend DESC LIMIT 5; ``` ### Explanation: 1. **SELECT**: We select the `CustomerID`, `CustomerName`, and the sum of `TotalAmount` as `TotalSpend`. 2. **FROM Customers c**: We start from the `Customers` table and alias it as `c`. 3. **JOIN Orders o ON c.CustomerID = o.CustomerID**: We join the `Orders` table (aliased as `o`) on the condition that the `CustomerID` matches. 4. **WHERE YEAR(o.OrderDate) = 2025**: We filter the orders to include only those from the year 2025. 5. **GROUP BY c.CustomerID, c.CustomerName**: We group the results by `CustomerID` and `CustomerName` so that we can calculate the total spend per customer. 6. **ORDER BY TotalSpend DESC**: We order the results in descending order based on the total spend. 7. **LIMIT 5**: We limit the result to the top 5 customers. Make sure your database system supports the `YEAR()` function and `LIMIT` clause, as these are standard SQL functions but may have slight variations depending on the specific database management system (e.g., MySQL, PostgreSQL, SQL Server).

To implement the request, we'll write a SQL query to return the top 5 customers by their total spend in the year 2025. We'll assume there is a database table named `Orders` with columns `CustomerID`, `OrderDate`, and `TotalAmount`. Here's the SQL query: ```sql SELECT CustomerID, SUM(TotalAmount) AS TotalSpend FROM Orders WHERE YEAR(OrderDate) = 2025 GROUP BY CustomerID ORDER BY TotalSpend DESC LIMIT 5; ``` Explanation: 1. **SELECT CustomerID, SUM(TotalAmount) AS TotalSpend**: This selects the `CustomerID` and calculates the total spend for each customer. 2. **FROM Orders**: Specifies the table from which to retrieve data. 3. **WHERE YEAR(OrderDate) = 2025**: Filters the orders to include only those from the year 2025. 4. **GROUP BY CustomerID**: Groups the results by `CustomerID` so that the total spend is calculated for each customer. 5. **ORDER BY TotalSpend DESC**: Orders the results in descending order based on the total spend. 6. **LIMIT 5**: Limits the result to the top 5 customers. This query will return the top 5 customers who spent the most in the year 2025, along with their total spend.