Revenue per Product in TypeScript

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:

const query: string = [
  "SELECT category, SUM(price * stock) AS stock_value",
  "FROM products",
  "GROUP BY category",
].join(" ");

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, each clause one string of the array: 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.