Find Revenue by Department
MediumUnlock detailed company stats for this questionUpgrade
You are given the following tables:
orders table:
departments table:
customers table:
Order the departments from highest to lowest revenue over the most recent 12 months in the data: December 1, 2022 through November 30, 2023. Your output should have the following columns: department_name, total_revenue
The data ends in November 2023, so the 12-month window is fixed: December 1, 2022 through November 30, 2023. Filter orders to that window, join to departments for the name, then sum order_amount per department.
In a live interview the table would hold current data, and you would write the window relative to today instead: CURRENT_DATE - INTERVAL '12 months' in PostgreSQL or date('now', '-12 months') in SQLite. Ask the interviewer which they want.