SQL query for Weekly Sales Report

Question

• Retrieve three columns labeled day, qty_sold and total_profit in that order. day should be displayed in the 3-character format Sun, Mon, Tue, Wed, Thu, Fri, Sat and sorted ascending in that order.
• Only retrieve sales from the past week.
• If there are no sales on a particular day of the week (as might be the case on a holiday), the row should still be included in the report with qty_sold and total_profit columns as 0.
• Net profit per item is calculated as qty_sold * (price - cost). Total profit for a day is the sum of the net profit of every item sold on that day. Do not format any numbers as currency.

Here's what i got so far any help would be greatly appreciated

SELECT DATE_FORMAT(s.order_time,"%a") AS day
FROM sales_order s 
INNER JOIN order_item o on o.sales_order_id=s.id 
INNER JOIN menu_item m on m.id =o.menu_item_id
GROUP BY day;

This is supposed to be the output

Answer 1

You can try something like this

SELECT day,COUNT(menu_item_id) AS qty_sold,SUM(price - cost) AS total_profit
FROM 
(SELECT 1 AS id,"Sun" AS day
UNION ALL
SELECT 2,"Mon"
UNION ALL
SELECT 3,"Tue"
UNION ALL
SELECT 4,"Wed"
UNION ALL
SELECT 5,"Thu"
UNION ALL
SELECT 6,"Fri"
UNION ALL
SELECT 7,"Sat") AS dow
LEFT OUTER JOIN sales_order AS s ON dow.id = DAYOFWEEK(s.order_time)
INNER JOIN order_item AS o ON o.sales_order_id=s.id 
INNER JOIN menu_item AS m ON m.id=o.menu_item_id
WHERE s.order_time >= DATE_SUB(NOW(),INTERVAL 7 + WEEKDAY(current_date) DAY) AND s.order_time < CONCAT(current_date," 00:00:00")
GROUP BY day
ORDER BY dow.id