Monthly Sales Report
MediumPremium
You are given the following tables:
transactions table:
products table:
users table:
Modify the previous query to report for multiple years. The answer should have the results by month-year. Your output should have the following columns: month_year, num_customers, num_orders, order_amt. Order the result in descending month_year
SELECT
strftime('%Y-%m', created_at) AS month_year,
COUNT(DISTINCT user_id) AS num_customers,
COUNT(txn.id) AS num_orders,
SUM(prod.price * txn.quantity) AS order_amt
FROM transactions AS txn
JOIN products AS prod
ON txn.product_id = prod.id
GROUP BY
strftime('%Y-%m', created_at)
ORDER BY
strftime('%Y-%m', created_at);
Test case is wrong. It expects to sort in asc order of month_year.
-- Write your query here SELECT strftime('%Y-%m', created_at) AS month_year, COUNT(DISTINCT user_id) AS num_customers, COUNT(t.id) AS num_orders, SUM(price * quantity) AS order_amt FROM transactions t INNER JOIN products p ON t.product_id = p.id GROUP BY month_year ORDER BY month_year ;