Interview › SQL Aggregates › Monthly Revenue Trend
⌨ Programming Medium SQL Aggregates

Monthly Revenue Trend

Question

Calculate the total revenue for each month. Return year-month (e.g. 2024-01) and total revenue, sorted chronologically.

💡 Use orders. Use strftime('%Y-%m', order_date) to extract year-month in SQLite.
SQL Editor
Solution
SELECT
  strftime('%Y-%m', order_date) AS month,
  SUM(total_amount)             AS monthly_revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;
Explanation: Use strftime to extract year-month as a string, GROUP BY it, then SUM the amounts. ORDER BY month gives chronological order.