Challenge
Consider two tables: Orders and Products.
The Orders table has columns: id (int), product_id (int), quantity (int), and order_date (date).
The Products table has columns: id (int), name (varchar), and price (decimal).
Write a SQL query to calculate the total revenue (quantity times price) for each quarter of a year.
DB Fiddle Instructions
Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.
Explanation
First, we create a CTE QuarterlyOrders to calculate the total quantity ordered for each product in each quarter. In the main query, we join this CTE with the Products table and calculate the total revenue (total quantity times price) for each quarter.