Round #789

Write a SQL query that returns customers with more than 3 orders.

**SQL Query: Customers with More Than 3 Orders** ```sql SELECT c.* FROM Customers c JOIN ( SELECT CustomerID, COUNT(OrderID) as OrderCount FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 3 ) o ON c.CustomerID = o.CustomerID; ``` **Explanation:** 1. We use a subquery to count the number of orders for each customer. 2. We group the orders by `CustomerID` and use the `HAVING` clause to filter customers with more than 3 orders. 3. We join the `Customers` table with the subquery result on the `CustomerID` column. 4. We select all columns (`*`) from the `Customers` table for the filtered customers. **Assumptions:** * `Customers` table has a column named `CustomerID`. * `Orders` table has columns named `OrderID` and `CustomerID`. * The database management system supports subqueries and joins. **Example Use Case:** Suppose we have the following tables: `Customers` table: | CustomerID | Name | Email | | --- | --- | --- | | 1 | John Doe | johndoe@example.com | | 2 | Jane Doe | janedoe@example.com | | 3 | Bob Smith | bobsmith@example.com | `Orders` table: | OrderID | CustomerID | OrderDate | | --- | --- | --- | | 1 | 1 | 2022-01-01 | | 2 | 1 | 2022-01-15 | | 3 | 1 | 2022-02-01 | | 4 | 1 | 2022-03-01 | | 5 | 2 | 2022-01-01 | | 6 | 2 | 2022-02-01 | | 7 | 3 | 2022-01-01 | | 8 | 3 | 2022-02-01 | | 9 | 3 | 2022-03-01 | | 10 | 3 | 2022-04-01 | | 11 | 3 | 2022-05-01 | Running the query will return: | CustomerID | Name | Email | | --- | --- | --- | | 1 | John Doe | johndoe@example.com | | 3 | Bob Smith | bobsmith@example.com | These are the customers with more than 3 orders.

```csharp using Microsoft.EntityFrameworkCore; using System; using System.Linq; using Microsoft.Extensions.Logging; public class CustomerOrderQuery { private readonly ILogger<CustomerOrderQuery> _logger; private readonly DbContext _context; public CustomerOrderQuery(ILogger<CustomerOrderQuery> logger, DbContext context) { _logger = logger; _context = context; } public async Task<IEnumerable<Customer>> GetCustomersWithMoreThanThreeOrdersAsync() { try { var customers = await _context.Customers .Include(c => c.Orders) .Where(c => c.Orders.Count > 3) .ToListAsync(); return customers; } catch (Exception ex) { _logger.LogError(ex, "Error retrieving customers with more than three orders"); 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 int CustomerId { get; set; } public Customer Customer { get; set; } } ``` The equivalent SQL query would be: ```sql SELECT c.* FROM Customers c JOIN ( SELECT CustomerId, COUNT(*) as OrderCount FROM Orders GROUP BY CustomerId HAVING COUNT(*) > 3 ) o ON c.Id = o.CustomerId; ```