Aggregate sql tables in queries in different ways

Question

I have three tables in sql

order_table (order_id, product_id, amount)

products_table (product_id, name, price_usd),

all_orders (order_id, customer_id, order_date, total_amount),

I want to generate a SQL query that outputs the total revenue for each product by month for the past 12 months.

I somehow need to separate out the different products, but I'm not sure how to structure a query like this.

Any pointers would be great

Answer 1

The

answer given is to use WITH...., which is not needed:

select 
   DATE_FORMAT(all_orders.order_date, '%Y-%m') as order_date, 
   name as product_name, 
   sum(total_amount) as total_quantity, 
   sum(price_usd * total_amount) as total_revenue 
from all_orders
LEFT JOIN order_table USING (order_id)
LEFT JOIN products_table USING (product_id)
where all_orders.order_date >= date_sub(current_date(), interval 12 month)
group by 1,2 ;

Should do the same thing (untested)