Revenue per Product in C#

Each row of order_items is one order line: one product in one order, with how many were bought (quantity) and the price paid for each one (unit_price). So one line brought in quantity * unit_price, and a product's revenue is that amount added up over all its lines.

SUM can add up a calculation, not only a column, and with GROUP BY it adds up each group on its own. For example, the value of the stock in each category:

var query = "SELECT category, SUM(price * stock) AS stock_value " +
    "FROM products " +
    "GROUP BY category ";

An order line only knows its product_id, so you JOIN the products table to give each line its product's name. To sort, you can use the name you gave the sum with AS. The check runs show(db, query);, which must print:

name | revenue
Mug | 84
Lamp | 58
Teapot | 44
Notebook | 35
Pen | 30
Plant | 15
Stapler | 9
(7 rows)

The gift card was never ordered, so it has no lines and no row.

Your task: write query so it lists every product that was ordered with its revenue, the sum of quantity * unit_price over its order lines, best sellers first.

Assignment
Follow the Coding Tutorial to build the query one clause per step: SELECT, FROM, JOIN, GROUP BY and ORDER BY.

Hint
Read the rows from order_items and join products on the product whose id is the line's product_id. Group by the product's name, and sort by revenue with DESC. Every piece of the string ends with +, except the last one, which ends the statement with ;.