Round #798

Write a SQL query that returns products and their cumulative sales over time.

**Cumulative Sales SQL Query** ================================ To calculate the cumulative sales of products over time, you can use the following SQL query: ```sql SELECT p.product_name, s.sale_date, SUM(s.sale_amount) OVER (PARTITION BY p.product_id ORDER BY s.sale_date) AS cumulative_sales FROM sales s JOIN products p ON s.product_id = p.product_id ORDER BY p.product_name, s.sale_date; ``` **Explanation** --------------- This query uses the following components: * `JOIN`: Combines rows from the `sales` and `products` tables based on the `product_id` column. * `SUM` with `OVER` clause: Calculates the cumulative sum of `sale_amount` for each product over time. * `PARTITION BY`: Divides the result set into partitions based on the `product_id` column. * `ORDER BY`: Sorts the result set by `product_name` and `sale_date`. **Assumptions** -------------- * The `sales` table has columns `sale_id`, `product_id`, `sale_date`, and `sale_amount`. * The `products` table has columns `product_id` and `product_name`. * The `sale_date` column is of a date or datetime type. **Example Use Case** -------------------- Suppose you have the following data: `sales` table: | sale_id | product_id | sale_date | sale_amount | | --- | --- | --- | --- | | 1 | 1 | 2022-01-01 | 100 | | 2 | 1 | 2022-01-15 | 200 | | 3 | 2 | 2022-02-01 | 50 | | 4 | 1 | 2022-03-01 | 300 | | 5 | 2 | 2022-04-01 | 150 | `products` table: | product_id | product_name | | --- | --- | | 1 | Product A | | 2 | Product B | The query will return: | product_name | sale_date | cumulative_sales | | --- | --- | --- | | Product A | 2022-01-01 | 100 | | Product A | 2022-01-15 | 300 | | Product A | 2022-03-01 | 600 | | Product B | 2022-02-01 | 50 | | Product B | 2022-04-01 | 200 | This result set shows the cumulative sales for each product over time.

```csharp using Microsoft.EntityFrameworkCore; using System; using System.Linq; using Microsoft.Extensions.Logging; public class ProductSalesRepository { private readonly DbContext _context; private readonly ILogger<ProductSalesRepository> _logger; public ProductSalesRepository(DbContext context, ILogger<ProductSalesRepository> logger) { _context = context; _logger = logger; } public async Task<IEnumerable<ProductSales>> GetCumulativeSalesAsync() { try { var query = @" SELECT p.Name, SUM(oi.Quantity * oi.UnitPrice) AS CumulativeSales FROM Products p JOIN OrderItems oi ON p.Id = oi.ProductId JOIN Orders o ON oi.OrderId = o.Id GROUP BY p.Name, o.OrderDate ORDER BY o.OrderDate"; var results = await _context.Database.SqlQuery<ProductSales>(query).ToListAsync(); return results; } catch (Exception ex) { _logger.LogError(ex, "Error retrieving cumulative sales"); throw; } } } public class ProductSales { public string Name { get; set; } public decimal CumulativeSales { get; set; } } ``` This implementation uses Entity Framework Core to execute a SQL query that retrieves products and their cumulative sales over time. The `GetCumulativeSalesAsync` method returns a list of `ProductSales` objects, each containing the product name and cumulative sales. The query joins the `Products`, `OrderItems`, and `Orders` tables to calculate the cumulative sales for each product. The results are ordered by the order date. If an error occurs during the query execution, it is logged using the `ILogger` instance. Note: The SQL query assumes that the database schema includes the `Products`, `OrderItems`, and `Orders` tables with the corresponding columns. The actual table and column names may vary depending on the database schema. This implementation requires more text to provide a clear explanation of the code and its functionality.