MORSE needs a product-level revenue view for monitoring performance across its analytics surfaces. Revenue should include completed sales only, while products without completed sales should not appear in the result.
Write a PostgreSQL query that calculates daily revenue and a chronological running total for each product from January 1 through January 5, 2024.
products to sales using product_id.status is completed and whose sale_date is within the requested date range.daily_revenue, treating NULL revenue as zero.running_total independently for each product, ordered by sale date. Return products by name and date.| Column | Type | Description |
|---|---|---|
| product_idPK | INTEGER | Unique product identifier |
| product_name | VARCHAR(100) | Name displayed for the product |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INTEGER | Unique sale identifier |
| product_id | INTEGER | Product associated with the sale |
| sale_date | DATE | Calendar date on which the sale occurred |
| revenue | NUMERIC(12,2) | Revenue credited to the sale |
| status | VARCHAR(20) | Lifecycle status of the sale |