/*
Meta SQL Interview Question

The sales department has given you the sales figures for the first two months of 2023.

You've been tasked with determining the percentage of weekly sales on the first and last day of every week. Consider Sunday as last day of week and Monday as first day of week.
In your output, include the week number, percentage sales for the first day of the week, and percentage sales for the last day of the week. Both proportions should be rounded to the nearest whole number.
*/
    
-- Schema Setup
CREATE TABLE early_sales ( invoicedate DATETIME, invoiceno BIGINT, quantity BIGINT, stockcode NVARCHAR(50), unitprice FLOAT);

INSERT INTO early_sales (invoicedate, invoiceno, quantity, stockcode, unitprice) VALUES ('2023-01-01 10:00:00', 1001, 5, 'A001', 20.0), ('2023-01-01 15:30:00', 1002, 3, 'A002', 30.0), ('2023-01-02 09:00:00', 1003, 10, 'A003', 15.0), ('2023-01-02 11:00:00', 1004, 2, 'A004', 50.0), ('2023-01-08 10:30:00', 1005, 4, 'A005', 25.0), ('2023-01-08 14:45:00', 1006, 7, 'A006', 18.0), ('2023-01-15 08:00:00', 1007, 6, 'A007', 22.0), ('2023-01-15 16:00:00', 1008, 8, 'A008', 12.0), ('2023-01-22 09:30:00', 1009, 3, 'A009', 40.0), ('2023-01-22 18:00:00', 1010, 5, 'A010', 35.0), ('2023-02-01 10:00:00', 1011, 9, 'A011', 20.0), ('2023-02-01 12:00:00', 1012, 2, 'A012', 60.0), ('2023-02-05 09:30:00', 1013, 4, 'A013', 25.0), ('2023-02-05 13:00:00', 1014, 6, 'A014', 18.0), ('2023-02-12 10:00:00', 1015, 7, 'A015', 22.0), ('2023-02-12 14:00:00', 1016, 5, 'A016', 28.0);

-- Solution
WITH weekly_sales AS
(
SELECT
    WEEK(invoicedate) AS week_number,
    SUM(quantity * unitprice) AS total_weekly_sales
FROM early_sales
WHERE YEAR(invoicedate) = 2023 AND MONTH(invoicedate) IN(1,2)
GROUP BY week_number
)
, day_sales AS
(
 SELECT
    week_number,
    SUM(CASE WHEN day_of_week = 2 THEN total_weekly_sales ELSE 0 END) AS monday_sales,
    SUM(CASE WHEN day_of_week = 1 THEN total_weekly_sales ELSE 0 END) AS sunday_sales
 FROM 
 (SELECT
     WEEK(invoicedate) week_number ,
     DAYOFWEEK(invoicedate) day_of_week,
     SUM(quantity * unitprice) AS total_weekly_sales
  FROM early_sales
  WHERE YEAR(invoicedate) = 2023 AND MONTH(invoicedate) IN(1,2)
  GROUP BY 1,2) SUBQUERY
 GROUP BY week_number
 )
    
SELECT
    w.week_number,
    ROUND(d.monday_sales / w.total_weekly_sales * 100) AS monday_sales_percentage,
    ROUND(d.sunday_sales / w.total_weekly_sales * 100) AS sunday_sales_percentage
FROM weekly_sales w
JOIN day_sales d
ON d.week_number = w.week_number;