Find Monthly Revenue Growth
Hard
You are given the following tables:
orders table:
departments table:
customers table:
Find the department with the highest month-on-month increase (order amount) in December 2022. Your output should have the following columns: department_id, department_name, mom_increase
WITH nov_dec_order_amounts AS (
SELECT
department_id,
SUM(order_amount) AS order_amount_per_month,
strftime('%Y-%m', order_date) AS y_m_date
FROM orders
WHERE strftime('%Y-%m', order_date) IN ('2022-11', '2022-12')
GROUP BY department_id, strftime('%Y-%m', order_date)
),
mom_totals AS (
SELECT
department_id,
y_m_date,
(order_amount_per_month - LAG(order_amount_per_month) OVER (PARTITION BY department_id ORDER BY y_m_date ASC)) AS mom_increase
FROM nov_dec_order_amounts
ORDER BY y_m_date ASC, department_id
)
SELECT
d.department_id,
d.department_name,
m.mom_increase
FROM mom_totals m
JOIN departments d ON m.department_id = d.department_id
WHERE m.y_m_date = '2022-12' AND m.mom_increase = (SELECT MAX(mom_increase) FROM mom_totals WHERE y_m_date = '2022-12');
I would avoid converting
order_dateWITH monthly_totals AS ( SELECT department_id, SUM(CASE WHEN DATE_TRUNC('month', order_date) = '2022-11-01' THEN order_amount ELSE 0 END) AS nov_total, SUM(CASE WHEN DATE_TRUNC('month', order_date) = '2022-12-01' THEN order_amount ELSE 0 END) AS dec_total FROM orders WHERE order_date BETWEEN '2022-11-01' AND '2022-12-31' GROUP BY department_id ), mom_increases AS ( SELECT department_id, dec_total - nov_total AS mom_increase FROM monthly_totals ) SELECT d.department_id, d.department_name, m.mom_increase FROM mom_increases m JOIN departments d ON m.department_id = d.department_id ORDER BY m.mom_increase DESC LIMIT 1;