A Function for Line Totals
MediumDescription
An order line's amount is quantity × unit_price, less discount_pct percent, rounded to 2
decimal places. For example, 2 × 899 with a 10% discount is 1618.20.
Create a function f_line_total that takes a line's quantity, unit_price and
discount_pct, in that order, and returns its amount.
Then, using the function, show for each category with lines in delivered orders:
category_nameorders: how many delivered orders included the category's productsrevenue: the total amount of the category's lines in delivered orders
End your query with ORDER BY revenue DESC.
Tables
| orders |
|---|
| order_id (PK) |
| customer_id (FK) |
| order_date |
| status |
| total_amount |
| order_items |
|---|
| item_id (PK) |
| order_id (FK) |
| product_id (FK) |
| quantity |
| unit_price |
| discount_pct |
| products |
|---|
| product_id (PK) |
| product_name |
| category_id (FK) |
| categories |
|---|
| category_id (PK) |
| category_name |