Round #581

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 System; using System.Data.SqlClient; using Microsoft.Extensions.Logging; public class ProductSalesRepository { private readonly ILogger _logger; private readonly string _connectionString; public ProductSalesRepository(ILogger<ProductSalesRepository> logger, string connectionString) { _logger = logger; _connectionString = connectionString; } public void GetCumulativeProductSales() { try { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var query = @" SELECT p.ProductName, SUM(s.SalesAmount) AS CumulativeSales FROM Products p JOIN Sales s ON p.ProductId = s.ProductId GROUP BY p.ProductName ORDER BY CumulativeSales DESC"; using (var command = new SqlCommand(query, connection)) { using (var reader = command.ExecuteReader()) { while (reader.Read()) { var productName = reader["ProductName"].ToString(); var cumulativeSales = Convert.ToDecimal(reader["CumulativeSales"]); Console.WriteLine($"Product: {productName}, Cumulative Sales: {cumulativeSales}"); } } } } } catch (SqlException ex) { _logger.LogError(ex, "Error retrieving cumulative product sales"); } catch (Exception ex) { _logger.LogError(ex, "An error occurred"); } } } ``` This C# code defines a `ProductSalesRepository` class that retrieves the cumulative sales of products from a database. It uses the `Microsoft.Extensions.Logging` package for logging and the `System.Data.SqlClient` package for database operations. The `GetCumulativeProductSales` method executes a SQL query that joins the `Products` and `Sales` tables, groups the results by product name, and calculates the cumulative sales for each product. The results are then printed to the console. Error handling is implemented using try-catch blocks to catch `SqlException` and general `Exception` instances. If an error occurs, an error message is logged using the `ILogger` instance. To use this code, you would need to register the `ProductSalesRepository` class in the dependency injection container and provide the necessary connection string and logger instances. For example: ```csharp services.AddTransient<ProductSalesRepository>(provider => { var logger = provider.GetService<ILogger<ProductSalesRepository>>(); var connectionString = Configuration.GetConnectionString("DefaultConnection"); return new ProductSalesRepository(logger, connectionString); }); ``` You can then inject the `ProductSalesRepository` instance into a controller or other class and call the `GetCumulativeProductSales` method to retrieve the cumulative product sales.