Saltar al contenido principal

Build Monthly Revenue by Product Line

Construct a joined aggregate query for monthly revenue by product line.

sql-basics5 min read

Report monthly revenue by product line for paid orders in Q2. One output row should equal one month and one product line. Choose the source grain, join only needed dimensions, filter rows before grouping, then aggregate at the output grain. Starting from orders alone hides product_line, while joining order_lines and then grouping only by month would mix product lines together. Before SELECT DATE_TRUNC('month', ordered_at), product_line, SUM(total_amount) FROM orders GROUP BY 1, 2; -- product_line is not available and total_amount is order grain. After SELECT DATE_TRUNC('month', o.ordered_at) AS month, p.product_line, SUM(ol.line_amount) AS revenue FROM order_lines ol JOIN orders o ON o.id…

Read the full lesson

Sign up free: one personalized lesson every day, matched to your role and goals.

← Back to library

Contáctanos