Sales Report
MediumPremium
You are given the following tables:
transactions table:
products table:
users table:
Write a query to report the number of users, number of transactions placed and total order amount per month in the year 2020. Your output should have the following columns: month_name, num_customers, num_orders, order_amt. Order the result in descending month.
SELECT
strftime('%m', created_at) AS month_name,
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 prod
ON txn.product_id = prod.id
WHERE
strftime('%Y', created_at) = '2020'
GROUP BY
strftime('%m', created_at);
Order the result in descending month is not applied in the solution