Project: the order report

Five of the queries you wrote are saved as views, one per module: top_products, product_values, customer_orders, order_lines, and availability. They are created before your query runs (the workspace lists them and says which version it is using; a lesson you skipped contributes the reference version until you do it), so you query them like tables. That is how reporting works in practice: the hard joins live in views and the report reads them.

Build it up: start with SELECT * FROM order_lines, run, then join one view at a time and add the computed columns. The checks compare column names, values, and order, so read the failed check for what differs. If a view is wrong, fix it in its own lesson; the project uses the new version on the next run. That is the end of the course: window functions, indexes, and transactions are the natural next topics.

Your task

Write one query with one row per order and these columns, in this order: order_id, ordered_at, customer, product, and quantity from order_lines; line_total, the quantity times the price rounded to two decimals; availability from the availability view of that product; customer_orders, the order_count from customer_orders for that customer; featured, yes when the product is in top_products and no otherwise; and stock_value, the product's inventory_value from product_values. Sort by ordered_at and then order_id.

This is the final project. It is built from the 5 parts you made along the way; the workspace lists them and which version it is using.

SELECT o.order_id, o.ordered_at, o.customer, o.product, o.quantity,
  ROUND(o.quantity * o.price, 2) AS line_total,
  a.availability,
  c.order_count AS customer_orders,
  CASE WHEN o.product_id IN (SELECT id FROM top_products) THEN 'yes' ELSE 'no' END AS featured,
  v.inventory_value AS stock_value
FROM order_lines o
JOIN availability a ON a.id = o.product_id
JOIN customer_orders c ON c.customer_id = o.customer_id
JOIN product_values v ON v.id = o.product_id
ORDER BY o.ordered_at, o.order_id;