Find Revenue by Department
MediumPremium
You are given the following tables:
orders table:
departments table:
customers table:
Order the departments from highest to lowest revenue in the last 12 months. Your output should have the following columns: department_name, total_revenue
SELECT
d.department_name,
SUM(o.order_amount) AS total_revenue
FROM departments d
JOIN orders o ON d.department_id = o.department_id
WHERE o.order_date >= date('now', '-12 months')
GROUP BY d.department_name
ORDER BY total_revenue DESC;
SELECT d.department_name,SUM(o.order_amount) AS total_revenue FROM orders o JOIN departments d ON d.department_id =o.department_id WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months' GROUP BY d.department_name ORDER BY total_revenue DESC;