Muesli - ERD & SQL 1¶
1. ERD Understanding and Operational Analysis¶
Muesli Pty Ltd is a medium-sized company that manufactures and sells breakfast cereals to retailers.
Refer to the ERD provided in the company's background section to answer the following 3 sub-questions.
(1) Assertions about the ERD¶
Q:
For each of the assertions below, critically discuss to what extent the database structure would imply that the assertion is true or false.
Explain with reference to specific parts of the ERD to support your answers.
(a) Customers pay entire invoices at once. There are no split or partial payments.
(b) Production output is recorded only once, at the end of the production run.
A:
(a) Yes.
Although the ERD does not explicitly show the cardinality from Sales Orders to Sales Invoices, the fact that the Sales Invoices entity has a single PK Document_Number which is also a FK to the Sales Orders entity, implies that there is impossible for multiple sales invoices to have the same document number.
Therefore, we can infer that the relationship between Sales Orders and Sales Invoices is one-to-one, which would imply that each sales order can only be associated with one sales invoice, and thus customers would have to pay entire invoices at once, with no split or partial payments.
If there were a possibility of split or partial payments, we would expect that different sales invoices could be associated with the same sales order, which would require another PK in the Sales Invoices entity to make each invoice unique.
(b) No.
The cardinality between Production Orders and Production Yields is required one-to-many, which implies that one production order can be associated with multiple production yields.
This suggests that production output can be recorded multiple times for a single production run, which would allow for recording production output at different stages of the production process, rather than only at the end of the production run.
(2) Split Pricing Scenario¶
Q:
The edible foodstuffs vendor is offering the company fixed-volume pricing.
For example:
- If we order in multiples of 10,000 kg, the price would be $4 per kg.
- If we order less than 10,000 kg, the price would be $4.20 per kg.
- However, if we order 24,320 kg, then the first 20,000 kg would cost $4 per kg, and the remaining 4,320 kg would cost $4.20 per kg.
Under this new split pricing scenario, can the existing database structure record these variations in quantity and pricing, or would modifications to the schema be required for the company to take advantage of this? Explain why or why not.
A:
The current database structure CAN record the variations in quantity and pricing under the new split pricing scenario, without requiring modifications to the schema. Since the Purchase_Order_Items entity has combined PKs of Document_Number and Item_Number, but Material_Code is not part of the PK, it allows for multiple purchase order items to have the same material code but different item numbers.
For example, if we order 24,320 kg of a particular product, this could be recorded as two separate purchase order items within the same purchase order for the same material code, which is allowed by the current schema:
| Document Number | Item Number | Material Code | Price | Order_Quantity | Receive_Quantity | Unit |
|---|---|---|---|---|---|---|
| 0000001 | 1 | BB-R02 | $4.00 | 20,000 | 20,000 | kg |
| 0000001 | 2 | BB-R02 | $4.20 | 4,320 | 4,320 | kg |
However, current schema is not perfect for this scenario, as it also allows the same price for the same material code within the same purchase order, as long as the item numbers are different. For example:
| Document Number | Item Number | Material Code | Price | Order_Quantity | Receive_Quantity | Unit |
|---|---|---|---|---|---|---|
| 0000002 | 1 | BB-R02 | $4.00 | 10,000 | 10,000 | kg |
| 0000002 | 2 | BB-R02 | $4.00 | 10,000 | 10,000 | kg |
So, it is better to remove the Item_Number from the PK, and add Material_Code and Price to the PK, i.e., the new combined PK would be Document_Number, Material_Code, and Price. This would allow for multiple purchase order items to have the same material code but different prices, which is necessary to accurately record the split pricing scenario. For example, the 24,320 kg and 20,000 kg orders could be recorded as follows:
| Document Number | Material Code | Price | Order_Quantity | Receive_Quantity | Unit |
|---|---|---|---|---|---|
| 0000001 | BB-R02 | $4.00 | 20,000 | 20,000 | kg |
| 0000001 | BB-R02 | $4.20 | 4,320 | 4,320 | kg |
| 0000002 | BB-R02 | $4.00 | 20,000 | 20,000 | kg |
(3) Employee Tracking¶
Q:
Some inventory recently went missing from the warehouse. The supplier confirms that it was sent and the shipping company confirms it was delivered. Both provided documented evidence supporting these facts.
The system shows the inventory as having been delivered, but warehouse counts have not reflected this inventory being in stock.
In order to increase accountability, the company wants to add an Employee entity and associated relationships to track who received deliveries from suppliers, and also who enters warehouse counts.
Assuming an Employee entity has already been created with PK "Employee ID" and other relevant information about employees, describe how to correctly add relationships with the other existing entities to achieve the desired accountability.
Drawing a new ERD (or part of the ERD) is optional. You will be scored primarily on your explanation - your new ERD is only there to support your explanation.
A:
The parts of new ERD:
First, in Inventory_Counts entity, we can add a new required FK Counted_By that references the PK of the Employee entity - Employee_ID. This would make a required one-to-many relationship between Employee and Inventory_Counts, which would allow us to track which employee is responsible for each inventory count. The Inventory_Counts entity clearly lables the material, quantity, and date of the inventory count. So, when employees sign their name to the inventory count, they are confirming that they have verified the material, quantity, and date of the inventory count, which would enhance accountability.
Second, in Purchase_Orders entity, we can add a new required FK Received_By that references the PK of the Employee entity - Employee_ID. This would make a required one-to-many relationship between Employee and Purchase_Orders, which would allow us to track which employee is responsible for receiving deliveries from suppliers. By joining the Purchase_Orders entity with the Purchase_Order_Items entity, employees can check the specific materials, quantities, and date of each purchase order they received. So, when employees sign their name to the purchase order, they are confirming that they have verified the materials, quantities, and received date of the purchase order, which would enhance accountability.
2. SQL Querying¶
Using the database, develop and test ONE SQL query for each of the following business intents to support Muesli Pty Ltd. The information retrieved when running each SQL, should be easily usable to inform the stated intent (i.e., minimal additional sorting or calculations would be required to determine an answer).
Use SQL comments before or within your query to:
- Document any assumptions
- Cite any part of the code obtained from a 3rd party source
(1) Purchasing Costs¶
Q:
Weighted average purchase cost of each raw material
- Only include purchases made in the prior 13 weeks (a rolling quarter), excluding the current week.
- Per kg for edible food items.
- Per piece for packaging boxes and bags.
A:
WITH
Purchases_Quarter AS (
SELECT
POI.Material_Code,
SUM(POI.Order_Quantity) AS Total_Quantity,
SUM(POI.Order_Quantity * POI.Price) / SUM(POI.Order_Quantity) AS Weighted_Average_Cost
FROM Purchase_Order_Items AS POI
JOIN Purchase_Orders AS PO ON POI.Document_Number = PO.Document_Number
WHERE PO.Order_Date >= DATEADD(WEEK, -13, DATETRUNC(WEEK, CURRENT_TIMESTAMP))
AND PO.Order_Date < DATETRUNC(WEEK, CURRENT_TIMESTAMP)
GROUP BY POI.Material_Code
)
SELECT
M.Material_Code,
M.Material_Name,
M.Unit_Size,
COALESCE(PQ.Total_Quantity, 0) AS Total_Quantity,
PQ.Weighted_Average_Cost
FROM Materials AS M
LEFT JOIN Purchases_Quarter AS PQ ON M.Material_Code = PQ.Material_Code
WHERE M.Type_Code = 'ROH'
ORDER BY M.Material_Code
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
==================================================================================
Material_Code Material_Name Unit_Size Total_Quantity Weighted_Average_Cost
------------- ---------------- ---------- -------------- ---------------------
BB-P01 Large Box (1kg) 1 piece 724000.000 0.340000
BB-P02 Large Bag (1kg) 1 piece 724000.000 0.160000
BB-P03 Small Box (500g) 1 piece 770000.000 0.260000
BB-P04 Small Bag (500g) 1 piece 770000.000 0.140000
BB-R01 Nuts 1 kg 69700.000 2.091090
BB-R02 Blueberries 1 kg 66250.000 5.547335
BB-R03 Strawberries 1 kg 88000.000 5.496965
BB-R04 Raisins 1 kg 95800.000 1.266210
BB-R05 Wheat 1 kg 394630.000 1.101060
BB-R06 Oats 1 kg 394630.000 1.072052
((10 rows affected))
(2) Expenditures¶
Q:
Year-to-date and month-to-date expenditure for each expense category.
A:
WITH
Account_Expenditure AS (
SELECT
GLA.Account_Number,
GLA.Account_Group,
GLA.Account_Name,
GLP.Posting_Date,
CASE
WHEN GLPE.DR_or_CR = 'DR' THEN GLPE.Amount
ELSE GLPE.Amount * -1
END AS Amount
FROM GL_Accounts AS GLA
JOIN GL_Posting_Entries AS GLPE ON GLA.Account_Number = GLPE.Account_Number
JOIN GL_Postings AS GLP ON GLPE.Document_Number = GLP.Document_Number
WHERE GLA.Account_Group = 'IS-Expenses'
),
Account_Expenditure_Year AS (
SELECT
Account_Number,
Account_Name,
SUM(Amount) AS Total_Expenditure
FROM Account_Expenditure
WHERE Posting_Date >= DATETRUNC(YEAR, CURRENT_TIMESTAMP)
GROUP BY Account_Number, Account_Name
),
Account_Expenditure_Month AS (
SELECT
Account_Number,
Account_Name,
SUM(Amount) AS Total_Expenditure
FROM Account_Expenditure
WHERE Posting_Date >= DATETRUNC(MONTH, CURRENT_TIMESTAMP)
GROUP BY Account_Number, Account_Name
)
SELECT
GLA.Account_Number,
GLA.Account_Name,
COALESCE(AEW.Total_Expenditure, 0) AS Total_Expenditure,
'Year-to-Date' AS Period
FROM GL_Accounts AS GLA
LEFT JOIN Account_Expenditure_Year AS AEW ON GLA.Account_Number = AEW.Account_Number
WHERE GLA.Account_Group = 'IS-Expenses'
UNION ALL
SELECT
GLA.Account_Number,
GLA.Account_Name,
COALESCE(AEM.Total_Expenditure, 0) AS Total_Expenditure,
'Month-to-Date' AS Period
FROM GL_Accounts AS GLA
LEFT JOIN Account_Expenditure_Month AS AEM ON GLA.Account_Number = AEM.Account_Number
WHERE GLA.Account_Group = 'IS-Expenses'
ORDER BY Account_Number, Period DESC
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
================================================================================================
Account_Number Account_Name Total_Expenditure Period
-------------- ---------------------------------------------- ----------------- -------------
0000211120 Depreciation Expenses: Building 15000.00 Year-to-Date
0000211120 Depreciation Expenses: Building 3750.00 Month-to-Date
0000211130 Depreciation Expenses: Machinery and Equipment 665833.32 Year-to-Date
0000211130 Depreciation Expenses: Machinery and Equipment 167499.99 Month-to-Date
0000400000 Consumption Raw Materials 2104738.50 Year-to-Date
0000400000 Consumption Raw Materials 521277.34 Month-to-Date
0000472000 Shipping costs 57000.00 Year-to-Date
0000472000 Shipping costs 15000.00 Month-to-Date
0000476900 Interest expenses 91416.90 Year-to-Date
0000476900 Interest expenses 18242.80 Month-to-Date
0000477001 Advertising North-01 0.00 Year-to-Date
0000477001 Advertising North-01 0.00 Month-to-Date
0000477002 Advertising South-01 0.00 Year-to-Date
0000477002 Advertising South-01 0.00 Month-to-Date
0000477003 Advertising West-01 0.00 Year-to-Date
0000477003 Advertising West-01 0.00 Month-to-Date
0000477004 Advertising North-02 0.00 Year-to-Date
0000477004 Advertising North-02 0.00 Month-to-Date
0000477005 Advertising South-02 0.00 Year-to-Date
0000477005 Advertising South-02 0.00 Month-to-Date
0000477006 Advertising West-02 0.00 Year-to-Date
0000477006 Advertising West-02 0.00 Month-to-Date
0000477007 Advertising North-03 0.00 Year-to-Date
0000477007 Advertising North-03 0.00 Month-to-Date
0000477008 Advertising South-03 0.00 Year-to-Date
0000477008 Advertising South-03 0.00 Month-to-Date
0000477009 Advertising West-03 0.00 Year-to-Date
0000477009 Advertising West-03 0.00 Month-to-Date
0000477010 Advertising North-04 0.00 Year-to-Date
0000477010 Advertising North-04 0.00 Month-to-Date
0000477011 Advertising South-04 0.00 Year-to-Date
0000477011 Advertising South-04 0.00 Month-to-Date
0000477012 Advertising West-04 0.00 Year-to-Date
0000477012 Advertising West-04 0.00 Month-to-Date
0000477013 Advertising North-05 0.00 Year-to-Date
0000477013 Advertising North-05 0.00 Month-to-Date
0000477014 Advertising South-05 0.00 Year-to-Date
0000477014 Advertising South-05 0.00 Month-to-Date
0000477015 Advertising West-05 0.00 Year-to-Date
0000477015 Advertising West-05 0.00 Month-to-Date
0000477016 Advertising North-06 0.00 Year-to-Date
0000477016 Advertising North-06 0.00 Month-to-Date
0000477017 Advertising South-06 0.00 Year-to-Date
0000477017 Advertising South-06 0.00 Month-to-Date
0000477018 Advertising West-06 0.00 Year-to-Date
0000477018 Advertising West-06 0.00 Month-to-Date
0000477019 Advertising North-11 0.00 Year-to-Date
0000477019 Advertising North-11 0.00 Month-to-Date
0000477020 Advertising South-11 0.00 Year-to-Date
0000477020 Advertising South-11 0.00 Month-to-Date
0000477021 Advertising WEST-11 0.00 Year-to-Date
0000477021 Advertising WEST-11 0.00 Month-to-Date
0000477022 Advertising North-12 0.00 Year-to-Date
0000477022 Advertising North-12 0.00 Month-to-Date
0000477023 Advertising South-12 0.00 Year-to-Date
0000477023 Advertising South-12 0.00 Month-to-Date
0000477024 Advertising West-12 0.00 Year-to-Date
0000477024 Advertising West-12 0.00 Month-to-Date
0000477025 Advertising North-13 0.00 Year-to-Date
0000477025 Advertising North-13 0.00 Month-to-Date
0000477026 Advertising South-13 0.00 Year-to-Date
0000477026 Advertising South-13 0.00 Month-to-Date
0000477027 Advertising West-13 0.00 Year-to-Date
0000477027 Advertising West-13 0.00 Month-to-Date
0000477028 Advertising North-14 0.00 Year-to-Date
0000477028 Advertising North-14 0.00 Month-to-Date
0000477029 Advertising South-14 0.00 Year-to-Date
0000477029 Advertising South-14 0.00 Month-to-Date
0000477030 Advertising West-14 0.00 Year-to-Date
0000477030 Advertising West-14 0.00 Month-to-Date
0000477031 Advertising North-15 0.00 Year-to-Date
0000477031 Advertising North-15 0.00 Month-to-Date
0000477032 Advertising South-15 0.00 Year-to-Date
0000477032 Advertising South-15 0.00 Month-to-Date
0000477033 Advertising West-15 0.00 Year-to-Date
0000477033 Advertising West-15 0.00 Month-to-Date
0000477034 Advertising North-16 0.00 Year-to-Date
0000477034 Advertising North-16 0.00 Month-to-Date
0000477035 Advertising South-16 0.00 Year-to-Date
0000477035 Advertising South-16 0.00 Month-to-Date
0000477036 Advertising West-16 0.00 Year-to-Date
0000477036 Advertising West-16 0.00 Month-to-Date
0000478000 Lean Manufacturing Program Expenses 0.00 Year-to-Date
0000478000 Lean Manufacturing Program Expenses 0.00 Month-to-Date
0000478100 Warehousing costs 47500.00 Year-to-Date
0000478100 Warehousing costs 14000.00 Month-to-Date
0000500000 Direct Labor Costs 240000.00 Year-to-Date
0000500000 Direct Labor Costs 60000.00 Month-to-Date
0000510000 Factory Overhead Expenses 180000.00 Year-to-Date
0000510000 Factory Overhead Expenses 45000.00 Month-to-Date
0000520000 Sales, General and Administrative Expenses 480000.00 Year-to-Date
0000520000 Sales, General and Administrative Expenses 120000.00 Month-to-Date
((92 rows affected))
(3) Current Inventory¶
Q:
Currently available inventory for each product.
A:
/*
Assumptions:According to the background infromation, staff count inventory of each product at the end of each day.
So, it is reasonable to assume that at each day and each location, the inventory amounts are recorded for all products.
Therefore, we can find the most recent inventory date for each product at each location.
Also, we don't need to left join with the material table, because all the products are recorded in the inventory counts, even the ones with zero quantity.
*/
SELECT
M.Material_Code,
M.Material_Name,
SUM(IC.Closing_Quantity) AS Available_Inventory
FROM Materials AS M
JOIN Inventory_Counts AS IC ON M.Material_Code = IC.Material_Code
WHERE Inventory_Date = (SELECT MAX(Inventory_Date) FROM Inventory_Counts)
GROUP BY M.Material_Code, M.Material_Name
HAVING SUM(IC.Closing_Quantity) > 0
ORDER BY M.Material_Code
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
==========================================================
Material_Code Material_Name Available_Inventory
------------- ---------------------- -------------------
BB-F01 500g Nut Muesli 18230.000
BB-F02 500g Blueberry Muesli 37002.000
BB-F05 500g Original Muesli 14346.000
BB-F13 1kg Strawberry Muesli 30210.000
BB-F14 1kg Raisin Muesli 19709.000
BB-F16 1kg Mixed Fruit Muesli 24486.000
((6 rows affected))
(4) Weekly Sales¶
Q:
Revenue and Units sold for each product, for each week:
- Show the most recent weeks first
- Show whole weeks only, i.e., exclude the current week
- Identify each week with a date (e.g., the first day in the week), NOT a week number (1, 2, 3, ..., 52)
A:
Version 1:
WITH
Sales_Data AS (
SELECT
DATETRUNC(WEEK, Delivery_Date) AS Week_Start_Date,
M.Material_Code,
M.Material_Name,
SOI.Quantity,
SOI.Price
FROM Materials AS M
JOIN Sales_Order_Items AS SOI ON M.Material_Code = SOI.Material_Code
JOIN Sales_Orders AS SO ON SOI.Document_Number = SO.Document_Number
)
SELECT
Week_Start_Date,
Material_Code,
Material_Name,
SUM(Quantity) AS Units_Sold,
SUM(Quantity * Price) AS Revenue
FROM Sales_Data
WHERE Week_Start_Date < DATETRUNC(WEEK, CURRENT_TIMESTAMP) -- Exclude current week
GROUP BY Week_Start_Date, Material_Code, Material_Name
ORDER BY Week_Start_Date DESC, Units_Sold DESC
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
=================================================================================
Week_Start_Date Material_Code Material_Name Units_Sold Revenue
--------------- ------------- ---------------------- ---------- -------------
2026-03-08 BB-F05 500g Original Muesli 33217.000 89685.900000
2026-03-08 BB-F01 500g Nut Muesli 32122.000 120136.280000
2026-03-08 BB-F16 1kg Mixed Fruit Muesli 14094.000 83295.540000
2026-03-08 BB-F02 500g Blueberry Muesli 13447.000 74630.850000
2026-03-08 BB-F13 1kg Strawberry Muesli 6561.000 45664.560000
2026-03-08 BB-F14 1kg Raisin Muesli 716.000 3529.880000
2026-03-01 BB-F16 1kg Mixed Fruit Muesli 35006.000 206535.400000
2026-03-01 BB-F05 500g Original Muesli 25612.000 69408.520000
2026-03-01 BB-F01 500g Nut Muesli 23710.000 91994.800000
2026-03-01 BB-F13 1kg Strawberry Muesli 13300.000 90839.000000
2026-03-01 BB-F02 500g Blueberry Muesli 10457.000 59395.760000
2026-03-01 BB-F14 1kg Raisin Muesli 2207.000 11012.930000
2026-02-22 BB-F02 500g Blueberry Muesli 25822.000 146668.960000
2026-02-22 BB-F05 500g Original Muesli 25723.000 69709.330000
2026-02-22 BB-F14 1kg Raisin Muesli 16077.000 80224.230000
2026-02-22 BB-F01 500g Nut Muesli 5514.000 21394.320000
2026-02-22 BB-F16 1kg Mixed Fruit Muesli 1639.000 9670.100000
2026-02-15 BB-F01 500g Nut Muesli 30263.000 118630.960000
2026-02-15 BB-F16 1kg Mixed Fruit Muesli 28730.000 166346.700000
2026-02-15 BB-F02 500g Blueberry Muesli 24084.000 134388.720000
2026-02-15 BB-F14 1kg Raisin Muesli 21748.000 104825.360000
2026-02-15 BB-F05 500g Original Muesli 16695.000 44575.650000
2026-02-15 BB-F13 1kg Strawberry Muesli 8909.000 60848.470000
... ... ... ... ...
2025-08-03 BB-F16 1kg Mixed Fruit Muesli 17718.000 89830.260000
2025-08-03 BB-F01 500g Nut Muesli 14750.000 50150.000000
2025-08-03 BB-F14 1kg Raisin Muesli 11477.000 49465.870000
2025-08-03 BB-F02 500g Blueberry Muesli 11154.000 49523.760000
2025-08-03 BB-F13 1kg Strawberry Muesli 10590.000 55068.000000
2025-08-03 BB-F05 500g Original Muesli 7792.000 30388.800000
2025-07-27 BB-F16 1kg Mixed Fruit Muesli 12750.000 64642.500000
2025-07-27 BB-F05 500g Original Muesli 12095.000 47170.500000
2025-07-27 BB-F14 1kg Raisin Muesli 11293.000 48672.830000
2025-07-27 BB-F13 1kg Strawberry Muesli 8500.000 44200.000000
2025-07-27 BB-F02 500g Blueberry Muesli 4230.000 18781.200000
2025-07-20 BB-F02 500g Blueberry Muesli 24026.000 110279.340000
2025-07-20 BB-F13 1kg Strawberry Muesli 17000.000 81600.000000
2025-07-20 BB-F01 500g Nut Muesli 16441.000 60831.700000
2025-07-20 BB-F05 500g Original Muesli 16025.000 66664.000000
2025-07-20 BB-F14 1kg Raisin Muesli 11353.000 47228.480000
2025-07-13 BB-F13 1kg Strawberry Muesli 25500.000 122400.000000
2025-07-13 BB-F02 500g Blueberry Muesli 25500.000 117045.000000
2025-07-13 BB-F01 500g Nut Muesli 23850.000 88245.000000
2025-07-13 BB-F05 500g Original Muesli 8500.000 35360.000000
2025-07-06 BB-F01 500g Nut Muesli 16970.000 84850.000000
2025-07-06 BB-F02 500g Blueberry Muesli 13242.000 57867.540000
2025-06-29 BB-F01 500g Nut Muesli 8500.000 42500.000000
((198 rows affected))
Version 2:
WITH
Week_Count AS (
SELECT DATEDIFF(WEEK, (SELECT MIN(Delivery_Date) FROM Sales_Orders), CURRENT_TIMESTAMP) AS Weeks
),
Week_Numbers AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Week_Numbers
WHERE n < (SELECT Weeks FROM Week_Count)
),
Week_Dates AS (
SELECT
DATEADD(WEEK, -n, DATETRUNC(WEEK, CURRENT_TIMESTAMP)) AS Week_Start_Date
FROM Week_Numbers
),
Week_Products AS (
SELECT
WD.Week_Start_Date,
M.Material_Code,
M.Material_Name
FROM Week_Dates AS WD
CROSS JOIN Materials AS M
WHERE M.Material_Code IN (SELECT DISTINCT Material_Code FROM Sales_Order_Items)
),
Sales_Data AS (
SELECT
DATETRUNC(WEEK, Delivery_Date) AS Week_Start_Date,
M.Material_Code,
M.Material_Name,
SOI.Quantity,
SOI.Price
FROM Materials AS M
JOIN Sales_Order_Items AS SOI ON M.Material_Code = SOI.Material_Code
JOIN Sales_Orders AS SO ON SOI.Document_Number = SO.Document_Number
),
Sales_Weekly AS (
SELECT
Week_Start_Date,
Material_Code,
Material_Name,
SUM(Quantity) AS Units_Sold,
SUM(Quantity * Price) AS Revenue
FROM Sales_Data
WHERE Week_Start_Date < DATETRUNC(WEEK, CURRENT_TIMESTAMP) -- Exclude current week
GROUP BY Week_Start_Date, Material_Code, Material_Name
)
SELECT
WD.Week_Start_Date,
WD.Material_Code,
WD.Material_Name,
COALESCE(SW.Units_Sold, 0) AS Units_Sold,
COALESCE(SW.Revenue, 0) AS Revenue
FROM Week_Products AS WD
LEFT JOIN Sales_Weekly AS SW ON WD.Week_Start_Date = SW.Week_Start_Date AND WD.Material_Code = SW.Material_Code AND WD.Material_Name = SW.Material_Name
ORDER BY WD.Week_Start_Date DESC, SW.Units_Sold DESC
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
=========================================================================================
Week_Start_Date Material_Code Material_Name Units_Sold Revenue
----------------------- ------------- ---------------------- ---------- -------------
2026-03-08 00:00:00.000 BB-F05 500g Original Muesli 33217.000 89685.900000
2026-03-08 00:00:00.000 BB-F01 500g Nut Muesli 32122.000 120136.280000
2026-03-08 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 14094.000 83295.540000
2026-03-08 00:00:00.000 BB-F02 500g Blueberry Muesli 13447.000 74630.850000
2026-03-08 00:00:00.000 BB-F13 1kg Strawberry Muesli 6561.000 45664.560000
2026-03-08 00:00:00.000 BB-F14 1kg Raisin Muesli 716.000 3529.880000
2026-03-01 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 35006.000 206535.400000
2026-03-01 00:00:00.000 BB-F05 500g Original Muesli 25612.000 69408.520000
2026-03-01 00:00:00.000 BB-F01 500g Nut Muesli 23710.000 91994.800000
2026-03-01 00:00:00.000 BB-F13 1kg Strawberry Muesli 13300.000 90839.000000
2026-03-01 00:00:00.000 BB-F02 500g Blueberry Muesli 10457.000 59395.760000
2026-03-01 00:00:00.000 BB-F14 1kg Raisin Muesli 2207.000 11012.930000
2026-02-22 00:00:00.000 BB-F02 500g Blueberry Muesli 25822.000 146668.960000
2026-02-22 00:00:00.000 BB-F05 500g Original Muesli 25723.000 69709.330000
2026-02-22 00:00:00.000 BB-F14 1kg Raisin Muesli 16077.000 80224.230000
2026-02-22 00:00:00.000 BB-F01 500g Nut Muesli 5514.000 21394.320000
2026-02-22 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 1639.000 9670.100000
2026-02-22 00:00:00.000 BB-F13 1kg Strawberry Muesli 0.000 0.000000
2026-02-15 00:00:00.000 BB-F01 500g Nut Muesli 30263.000 118630.960000
2026-02-15 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 28730.000 166346.700000
2026-02-15 00:00:00.000 BB-F02 500g Blueberry Muesli 24084.000 134388.720000
2026-02-15 00:00:00.000 BB-F14 1kg Raisin Muesli 21748.000 104825.360000
2026-02-15 00:00:00.000 BB-F05 500g Original Muesli 16695.000 44575.650000
2026-02-15 00:00:00.000 BB-F13 1kg Strawberry Muesli 8909.000 60848.470000
2026-02-08 00:00:00.000 BB-F01 500g Nut Muesli 35402.000 138775.840000
2026-02-08 00:00:00.000 BB-F05 500g Original Muesli 32561.000 86937.870000
2026-02-08 00:00:00.000 BB-F14 1kg Raisin Muesli 21253.000 102439.460000
2026-02-08 00:00:00.000 BB-F13 1kg Strawberry Muesli 19812.000 135315.960000
2026-02-08 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 17992.000 104173.680000
2026-02-08 00:00:00.000 BB-F02 500g Blueberry Muesli 0.000 0.000000
... ... ... ... ...
2025-08-03 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 17718.000 89830.260000
2025-08-03 00:00:00.000 BB-F01 500g Nut Muesli 14750.000 50150.000000
2025-08-03 00:00:00.000 BB-F14 1kg Raisin Muesli 11477.000 49465.870000
2025-08-03 00:00:00.000 BB-F02 500g Blueberry Muesli 11154.000 49523.760000
2025-08-03 00:00:00.000 BB-F13 1kg Strawberry Muesli 10590.000 55068.000000
2025-08-03 00:00:00.000 BB-F05 500g Original Muesli 7792.000 30388.800000
2025-07-27 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 12750.000 64642.500000
2025-07-27 00:00:00.000 BB-F05 500g Original Muesli 12095.000 47170.500000
2025-07-27 00:00:00.000 BB-F14 1kg Raisin Muesli 11293.000 48672.830000
2025-07-27 00:00:00.000 BB-F13 1kg Strawberry Muesli 8500.000 44200.000000
2025-07-27 00:00:00.000 BB-F02 500g Blueberry Muesli 4230.000 18781.200000
2025-07-27 00:00:00.000 BB-F01 500g Nut Muesli 0.000 0.000000
2025-07-20 00:00:00.000 BB-F02 500g Blueberry Muesli 24026.000 110279.340000
2025-07-20 00:00:00.000 BB-F13 1kg Strawberry Muesli 17000.000 81600.000000
2025-07-20 00:00:00.000 BB-F01 500g Nut Muesli 16441.000 60831.700000
2025-07-20 00:00:00.000 BB-F05 500g Original Muesli 16025.000 66664.000000
2025-07-20 00:00:00.000 BB-F14 1kg Raisin Muesli 11353.000 47228.480000
2025-07-20 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 0.000 0.000000
2025-07-13 00:00:00.000 BB-F13 1kg Strawberry Muesli 25500.000 122400.000000
2025-07-13 00:00:00.000 BB-F02 500g Blueberry Muesli 25500.000 117045.000000
2025-07-13 00:00:00.000 BB-F01 500g Nut Muesli 23850.000 88245.000000
2025-07-13 00:00:00.000 BB-F05 500g Original Muesli 8500.000 35360.000000
2025-07-13 00:00:00.000 BB-F14 1kg Raisin Muesli 0.000 0.000000
2025-07-13 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 0.000 0.000000
2025-07-06 00:00:00.000 BB-F01 500g Nut Muesli 16970.000 84850.000000
2025-07-06 00:00:00.000 BB-F02 500g Blueberry Muesli 13242.000 57867.540000
2025-07-06 00:00:00.000 BB-F05 500g Original Muesli 0.000 0.000000
2025-07-06 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 0.000 0.000000
2025-07-06 00:00:00.000 BB-F13 1kg Strawberry Muesli 0.000 0.000000
2025-07-06 00:00:00.000 BB-F14 1kg Raisin Muesli 0.000 0.000000
2025-06-29 00:00:00.000 BB-F01 500g Nut Muesli 8500.000 42500.000000
2025-06-29 00:00:00.000 BB-F13 1kg Strawberry Muesli 0.000 0.000000
2025-06-29 00:00:00.000 BB-F14 1kg Raisin Muesli 0.000 0.000000
2025-06-29 00:00:00.000 BB-F05 500g Original Muesli 0.000 0.000000
2025-06-29 00:00:00.000 BB-F02 500g Blueberry Muesli 0.000 0.000000
2025-06-29 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 0.000 0.000000
((222 rows affected))
(5) Planned Future Production¶
Q:
The remaining, yet to be produced, production schedule.
A:
WITH
Production_Process AS (
SELECT
PO.Document_Number,
PO.Scheduled_Start,
PO.Scheduled_End,
PO.Planned_Quantity,
CASE
WHEN PY.Yield IS NOT NULL THEN PY.Yield
ELSE 0
END AS Produced_Quantity
FROM Production_Orders AS PO
LEFT JOIN Production_Yields AS PY ON PO.Document_Number = PY.Document_Number
),
Production_Process_Total AS (
SELECT
Document_Number,
Scheduled_Start,
Scheduled_End,
Planned_Quantity,
SUM(Produced_Quantity) AS Total_Produced,
Planned_Quantity - SUM(Produced_Quantity) AS Remaining_Quantity
FROM Production_Process
GROUP BY Document_Number, Scheduled_Start, Scheduled_End, Planned_Quantity
)
SELECT
Document_Number,
Scheduled_Start,
Scheduled_End,
Planned_Quantity,
Total_Produced,
Remaining_Quantity
FROM Production_Process_Total
WHERE Remaining_Quantity > 0 -- Only include production orders that are not yet completed
ORDER BY Scheduled_Start
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
=====================================================================================================
Document_Number Scheduled_Start Scheduled_End Planned_Quantity Total_Produced Remaining_Quantity
--------------- --------------- ------------- ---------------- -------------- ------------------
000001000953 2026-03-20 2026-03-24 51000.000 14345.000 36655.000
000001000955 2026-03-24 2026-03-25 18000.000 0.000 18000.000
000001000957 2026-03-25 2026-03-26 19000.000 0.000 19000.000
000001000959 2026-03-26 2026-03-30 37000.000 0.000 37000.000
((4 rows affected))
(6) Undelivered Raw Materials¶
Q:
Relevant details about currently undelivered raw materials, including the number of days it's been since the items were ordered.
A:
SELECT
PO.Document_Number,
M.Material_Code,
M.Material_Name,
PO.Order_Date,
PO.Delivery_Date,
PO.Vendor,
POI.Order_Quantity,
DATEDIFF(DAY, PO.Order_Date, CURRENT_TIMESTAMP) AS Days_Since_Ordered
FROM Purchase_Orders AS PO
JOIN Purchase_Order_Items AS POI ON PO.Document_Number = POI.Document_Number
JOIN Materials AS M ON POI.Material_Code = M.Material_Code
WHERE PO.Delivery_Date > CURRENT_TIMESTAMP OR PO.Delivery_Date IS NULL
ORDER BY PO.Order_Date DESC;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
========================================================================================================================
Document_Number Material_Code Material_Name Order_Date Delivery_Date Vendor Order_Quantity Days_Since_Ordered
--------------- ------------- ------------- ---------- ------------- ---------- -------------- ------------------
((0 rows affected))
(7) Balance Sheet Balances as of a Certain Date¶
Q:
The balance of each account as of close of business December 31st, for all balance sheet accounts (include the date of the last recorded transaction prior to, or on, December 31 st for each account).
A:
WITH
BS_Journals1 AS (
SELECT
GLA.Account_Number,
GLA.Account_Name,
GLA.Account_Group,
GLP.Posting_Date,
GLPE.DR_or_CR,
GLPE.Amount,
CASE
WHEN
GLA.Account_Group = 'BS-Assets'
AND
LOWER(GLA.Account_Name) NOT LIKE '%accumulated depreciation%'
THEN 1
ELSE -1
END AS Direction
FROM GL_Accounts AS GLA
JOIN GL_Posting_Entries AS GLPE ON GLA.Account_Number = GLPE.Account_Number
JOIN GL_Postings AS GLP ON GLPE.Document_Number = GLP.Document_Number
WHERE Account_Group LIKE 'BS-%'
)
,
BS_Journals2 AS (
SELECT
Account_Number,
Account_Name,
Account_Group,
Posting_Date,
CASE
WHEN Direction = 1 AND DR_or_CR = 'DR' THEN Amount
WHEN Direction = 1 AND DR_or_CR = 'CR' THEN Amount * -1
WHEN Direction = -1 AND DR_or_CR = 'DR' THEN Amount * -1
WHEN Direction = -1 AND DR_or_CR = 'CR' THEN Amount
ELSE 0
END AS Signed_Amount
FROM BS_Journals1
),
BS_Balance AS (
SELECT
Account_Number,
Account_Name,
Account_Group,
SUM(Signed_Amount) AS Account_Balance,
MAX(Posting_Date) AS Last_Transaction_Date
FROM BS_Journals2
WHERE Posting_Date <= '2025-12-31'
GROUP BY Account_Number, Account_Name, Account_Group
)
SELECT
GLA.Account_Number,
GLA.Account_Name,
GLA.Account_Group,
COALESCE(BSB.Account_Balance, 0) AS Account_Balance,
BSB.Last_Transaction_Date
FROM GL_Accounts AS GLA
LEFT JOIN BS_Balance AS BSB ON GLA.Account_Number = BSB.Account_Number
WHERE GLA.Account_Group LIKE 'BS-%'
ORDER BY GLA.Account_Group, GLA.Account_Number
;
Note: This query is executed on 2026-03-21. If you run this query on a different date, the results may vary, because the database is continuously updated.
==========================================================================================================================
Account_Number Account_Name Account_Group Account_Balance Last_Transaction_Date
-------------- -------------------------------------------------- -------------- --------------- ---------------------
0000001000 Land BS-Assets 500000.00 2025-06-30
0000002000 Buildings BS-Assets 1500000.00 2025-06-30
0000002010 Accumulated Depreciation - Buildings BS-Assets 32500.00 2025-12-26
0000011000 Machinery and equipment BS-Assets 26200000.00 2025-12-15
0000011010 Accumulated depreciation - machinery and equipment BS-Assets 1366666.62 2025-12-26
0000113300 Bank Cash Account BS-Assets 777078.93 2025-12-31
0000140000 Customers - Domestic Receivables BS-Assets 1234644.93 2025-12-31
0000300000 Raw materials BS-Assets 492414.22 2025-12-31
0000792000 Finished goods BS-Assets 226240.18 2025-12-31
0000070000 Common stock BS-Equity 20000000.00 2025-06-30
0000113101 Bank Loan BS-Liabilities 5786528.09 2025-12-15
0000160000 Accounts payable - Domestic BS-Liabilities 850241.50 2025-12-30
0000191100 GR/IR Clearing - External procurement BS-Misc 0.00 2025-12-30
((13 rows affected))
3. Analysis¶
Use the information obtained from running your queries from Question 2, to answer the following questions.
Make explicit reference to specific values retrieved from the data to support each answer.
You may optionally include screenshots of the output from one or more queries if you feel that's easier; however a screenshot of the whole output without making reference to which of the specific values are relevant (which column and row), is not an answer.
DO NOT write any new queries, or alter your queries from Question 2.
If you cannot support an answer based on the output from your Question 2 queries, your Question 2 queries probably aren't as useful as you thought - go improve them!
(1) Popular products¶
Q:
Which product(s) seem to be the most popular?
A:
If we look at the units sold of each product for the most recent 4 weeks, we can see that the popularity of each product varies a lot across different weeks. However, the 500g Original Muesli (BB-F05), 500g Nut Muesli (BB-F01), and 1kg Mixed Fruit Muesli (BB-F16) have relatively consistent performance across different weeks. They are in the top 3 for at least 3 out of the most recent 4 weeks, with respect to units sold. Also, in the most recent week, they are the top 3 products in terms of units sold, with 33,217 units sold for 500g Original Muesli (BB-F05), 32,122 units sold for 500g Nut Muesli (BB-F01), and 14,094 units sold for 1kg Mixed Fruit Muesli (BB-F16). Although the popularity varies, these three products can maintain relatively stable popularity.
Note, this result is based on query excecution results as of 2026-03-21. If you run the same query on a different date, the results may vary, because the database is continuously updated.
=========================================================================================
Week_Start_Date Material_Code Material_Name Units_Sold Revenue
----------------------- ------------- ---------------------- ---------- -------------
2026-03-08 00:00:00.000 BB-F05 500g Original Muesli 33217.000 89685.900000
2026-03-08 00:00:00.000 BB-F01 500g Nut Muesli 32122.000 120136.280000
2026-03-08 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 14094.000 83295.540000
2026-03-08 00:00:00.000 BB-F02 500g Blueberry Muesli 13447.000 74630.850000
2026-03-08 00:00:00.000 BB-F13 1kg Strawberry Muesli 6561.000 45664.560000
2026-03-08 00:00:00.000 BB-F14 1kg Raisin Muesli 716.000 3529.880000
2026-03-01 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 35006.000 206535.400000
2026-03-01 00:00:00.000 BB-F05 500g Original Muesli 25612.000 69408.520000
2026-03-01 00:00:00.000 BB-F01 500g Nut Muesli 23710.000 91994.800000
2026-03-01 00:00:00.000 BB-F13 1kg Strawberry Muesli 13300.000 90839.000000
2026-03-01 00:00:00.000 BB-F02 500g Blueberry Muesli 10457.000 59395.760000
2026-03-01 00:00:00.000 BB-F14 1kg Raisin Muesli 2207.000 11012.930000
2026-02-22 00:00:00.000 BB-F02 500g Blueberry Muesli 25822.000 146668.960000
2026-02-22 00:00:00.000 BB-F05 500g Original Muesli 25723.000 69709.330000
2026-02-22 00:00:00.000 BB-F14 1kg Raisin Muesli 16077.000 80224.230000
2026-02-22 00:00:00.000 BB-F01 500g Nut Muesli 5514.000 21394.320000
2026-02-22 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 1639.000 9670.100000
2026-02-22 00:00:00.000 BB-F13 1kg Strawberry Muesli 0.000 0.000000
2026-02-15 00:00:00.000 BB-F01 500g Nut Muesli 30263.000 118630.960000
2026-02-15 00:00:00.000 BB-F16 1kg Mixed Fruit Muesli 28730.000 166346.700000
2026-02-15 00:00:00.000 BB-F02 500g Blueberry Muesli 24084.000 134388.720000
2026-02-15 00:00:00.000 BB-F14 1kg Raisin Muesli 21748.000 104825.360000
2026-02-15 00:00:00.000 BB-F05 500g Original Muesli 16695.000 44575.650000
2026-02-15 00:00:00.000 BB-F13 1kg Strawberry Muesli 8909.000 60848.470000
(2) Inventory Planning¶
Q:
Which product(s) are likely to have enough stock in inventory to support a week of sales, at the start of next week (April 6)?
A:
Combining the outputs from Question 2 (3) Current Inventory and Question 2 (4) Weekly Sales, we can see that the currently available inventory and the units sold of most recent week for each product are as follows:
==========================================================
Material_Code Material_Name Available_Inventory
------------- ---------------------- -------------------
BB-F01 500g Nut Muesli 18230.000
BB-F02 500g Blueberry Muesli 37002.000
BB-F05 500g Original Muesli 14346.000
BB-F13 1kg Strawberry Muesli 30210.000
BB-F14 1kg Raisin Muesli 19709.000
BB-F16 1kg Mixed Fruit Muesli 24486.000
((6 rows affected))
=================================================================================
Week_Start_Date Material_Code Material_Name Units_Sold Revenue
--------------- ------------- ---------------------- ---------- -------------
2026-03-08 BB-F05 500g Original Muesli 33217.000 89685.900000
2026-03-08 BB-F01 500g Nut Muesli 32122.000 120136.280000
2026-03-08 BB-F16 1kg Mixed Fruit Muesli 14094.000 83295.540000
2026-03-08 BB-F02 500g Blueberry Muesli 13447.000 74630.850000
2026-03-08 BB-F13 1kg Strawberry Muesli 6561.000 45664.560000
2026-03-08 BB-F14 1kg Raisin Muesli 716.000 3529.880000
Therefore, using the units sold of the most recent week as a proxy for the demand for each product, we can calculate the inventory coverage for each product by dividing the currently available inventory by the units sold of the most recent week:
| Material_Code | Material_Name | Available_Inventory | Units_Sold_Per_Week | Inventory Coverage (weeks) |
|---|---|---|---|---|
| BB-F01 | 500g Nut Muesli | 18230.000 | 32122.000 | 0.57 |
| BB-F02 | 500g Blueberry Muesli | 37002.000 | 13447.000 | 2.75 |
| BB-F05 | 500g Original Muesli | 14346.000 | 33217.000 | 0.43 |
| BB-F13 | 1kg Strawberry Muesli | 30210.000 | 6561.000 | 4.60 |
| BB-F14 | 1kg Raisin Muesli | 19709.000 | 716.000 | 27.53 |
| BB-F16 | 1kg Mixed Fruit Muesli | 24486.000 | 14094.000 | 1.74 |
Based on the inventory coverage calculated above, we can see that, the 500g Blueberry Muesli (BB-F02), 1kg Strawberry Muesli (BB-F13), 1kg Raisin Muesli (BB-F14), and 1kg Mixed Fruit Muesli (BB-F16) are likely to have enough stock in inventory to support a week of sales at the start of next week (April 6), as their inventory coverage is greater than 1 week. However, the 500g Nut Muesli (BB-F01) and 500g Original Muesli (BB-F05) are likely to not have enough stock in inventory to support a week of sales at the start of next week (April 6), as their inventory coverage is less than 1 week.
Note, this result is based on query excecution results as of 2026-03-21. If you run the same query on a different date, the results may vary, because the database is continuously updated.