High-Volume Cost Variations

Last Updated Nov 25, 10:08 AM

The data provided does not include total consumption volume for each raw material, so it is not possible to identify which items are in the top 10% by consumption volume.
However, among all listed raw materials, the largest variations between max, min, and average unit cost are observed for 'Cocoa Butter - Olam Astra-A Netherlands' (max-min: 24.71, max-avg: 11.96, min-avg: 12.76) and 'Veg Fat - Melano S1000M' (max-min: 29.69, max-avg: 23.93, min-avg: 5.76).
To accurately answer the question, total consumption volume data is required to filter the top 10% of raw materials by usage.

Details

A S

Anthony Scalzitti

2:31 PM

Identify raw materials with the largest variations between max, min, and average unit cost, limited to raw materials in the top 10% by total consumption volume.

Lumi AI

2:31 PM

Starting analysis

Rephrased prompt

Identify raw materials with the largest variations between max, min, and average unit cost, limited to raw materials in the top 10% by total consumption volume.

Next action

Moving into a single analysis.

Isolated context

Class	Label	Content
Table	inventory_consumption	Table that shows how much inventory was consumed on any given day.
Table Field	inventory_consumption.consumption_quantity	Quantity consumed
Table Field	inventory_consumption.item_id	ID for the item
Table	item	item, SKU, product, top item, hierarchy, brand, category.
Table Field	item.item_category_code	Identifies the category or group to which an item belongs. Categories include: "SP", "RM", "FG", "SF", "MK", "FM", "PM". SP - Spare Parts, RM - Raw Materials, FG - Finished Goods, SF - Semi-Finished, MK - Marketing, FM - Factory Consumables, PM - Packing Materials.
Table Field	item.item_id	ID for the item, also known as product or SKU
Table Field	item.item_unit_cost_local_currency	Current cost of a single item in the local currency (AED)
Table	purchase_invoice	all purchase line item invoice details
Table Field	purchase_invoice.item_id	ID for the item, also known as product or SKU
Table Field	purchase_invoice.purchase_quantity	quantity purchased
Table Field	purchase_invoice.purchase_unit_cost_normalized_to_base_unit	This field represents the purchase unit cost normalized to the base unit of measurement.
Table Field	purchase_invoice.quantity_purchased_normalized_to_base_unit	This field captures the purchase volume or quantity in the base unit of measurement

No memories found

Generated query

Explanation

First, filter items to only raw materials (item_category_code = 'RM').
Join inventory_consumption and item tables to get item details and consumption data.
Aggregate total consumption volume per raw material item_id.
Calculate the 90th percentile of total consumption volume to identify the top 10% most consumed raw materials.
Filter to only those raw materials whose total consumption is at or above this threshold.
For these items, join to purchase_invoice to get all purchase unit costs (normalized to base unit).
For each item, calculate max, min, and average purchase unit cost (normalized), and compute the variation as max - min and as the absolute difference from the average.
Order by the largest variation (max - min) descending, and limit to 100 results.

Assumptions

Assumes that 'item_category_code' = 'RM' correctly identifies raw materials.
Assumes that 'purchase_unit_cost_normalized_to_base_unit' is the correct field for unit cost comparison across purchases.
Assumes that only items with purchase data are considered (i.e., items with no purchase records are excluded).

WITH
  item AS (
    SELECT
      description AS description,
      inventory_posting_group AS item_category_code,
      no_ AS item_id
    FROM
      base.chocolate_factory_item
  ),
  inventory_consumption AS (
    SELECTABS(consumption) AS consumption_quantity,
      itemno AS item_id
    FROM
      base.chocolate_factory_inventory_consumption
  ),
  purchase_invoice AS (
    SELECT
      no_ AS item_id,
      "Amount (LCY)" /NULLIF(qty__per_unit_of_measure * quantity, 0) AS purchase_unit_cost_normalized_to_base_unit
    FROM
      base.chocolate_factory_purchase_invoice_v2
  ),
  -- DNM_CTE: Do not modify the preceding local abstraction CTE's
  raw_materials AS (
    SELECT
      item.item_id,
      item.description,
      item.item_category_code
    FROM
      item
    WHERE
      item.item_category_code ='RM'
  ),
  consumption_agg AS (
    SELECT
      inventory_consumption.item_id,
      SUM(
        COALESCE(inventory_consumption.consumption_quantity, 0)
      ) AS total_consumption
    FROM
      inventory_consumption
      INNERJOIN raw_materials ON inventory_consumption.item_id = raw_materials.item_id
    GROUPBY
      inventory_consumption.item_id
  ),
  percentile_threshold AS (
    SELECT
      APPROX_PERCENTILE (total_consumption, 0.9) AS consumption_90th_percentile
    FROM
      consumption_agg
  ),
  top_consumed_raw_materials AS (
    SELECT
      ca.item_id,
      ca.total_consumption
    FROM
      consumption_agg ca
      CROSSJOIN percentile_threshold pt
    WHERE
      ca.total_consumption >= pt.consumption_90th_percentile
  ),
  cost_stats AS (
    SELECT
      pi.item_id,
      MAX(pi.purchase_unit_cost_normalized_to_base_unit) AS max_unit_cost,
      MIN(pi.purchase_unit_cost_normalized_to_base_unit) AS min_unit_cost,
      AVG(pi.purchase_unit_cost_normalized_to_base_unit) AS avg_unit_cost
    FROM
      purchase_invoice pi
      INNERJOIN top_consumed_raw_materials tcrm ON pi.item_id = tcrm.item_id
    WHERE
      pi.purchase_unit_cost_normalized_to_base_unit ISNOTNULLGROUPBY
      pi.item_id
  ),
  variation AS (
    SELECT
      cs.item_id,
      i.description,
      cs.max_unit_cost,
      cs.min_unit_cost,
      cs.avg_unit_cost,
      (cs.max_unit_cost - cs.min_unit_cost) AS max_min_variation,
      ABS(cs.max_unit_cost - cs.avg_unit_cost) AS max_avg_diff,
      ABS(cs.min_unit_cost - cs.avg_unit_cost) AS min_avg_diff
    FROM
      cost_stats cs
      INNERJOIN item i ON cs.item_id = i.item_id
  )
SELECT
  v.item_id,
  v.description,
  v.max_unit_cost,
  v.min_unit_cost,
  v.avg_unit_cost,
  v.max_min_variation,
  v.max_avg_diff,
  v.min_avg_diff
FROM
  variation v
ORDERBY
  v.max_min_variation DESC,
  v.item_id
LIMIT
  100;

No query available

WITH
  raw_materials AS (
    SELECT
      item.item_id,
      item.description,
      item.item_category_code
    FROM
      item
    WHERE
      item.item_category_code ='RM'
  ),
  consumption_agg AS (
    SELECT
      inventory_consumption.item_id,
      SUM(
        COALESCE(inventory_consumption.consumption_quantity, 0)
      ) AS total_consumption
    FROM
      inventory_consumption
      INNERJOIN raw_materials ON inventory_consumption.item_id = raw_materials.item_id
    GROUPBY
      inventory_consumption.item_id
  ),
  percentile_threshold AS (
    SELECT
      APPROX_PERCENTILE (total_consumption, 0.9) AS consumption_90th_percentile
    FROM
      consumption_agg
  ),
  top_consumed_raw_materials AS (
    SELECT
      ca.item_id,
      ca.total_consumption
    FROM
      consumption_agg ca
      CROSSJOIN percentile_threshold pt
    WHERE
      ca.total_consumption >= pt.consumption_90th_percentile
  ),
  cost_stats AS (
    SELECT
      pi.item_id,
      MAX(pi.purchase_unit_cost_normalized_to_base_unit) AS max_unit_cost,
      MIN(pi.purchase_unit_cost_normalized_to_base_unit) AS min_unit_cost,
      AVG(pi.purchase_unit_cost_normalized_to_base_unit) AS avg_unit_cost
    FROM
      purchase_invoice pi
      INNERJOIN top_consumed_raw_materials tcrm ON pi.item_id = tcrm.item_id
    WHERE
      pi.purchase_unit_cost_normalized_to_base_unit ISNOTNULLGROUPBY
      pi.item_id
  ),
  variation AS (
    SELECT
      cs.item_id,
      i.description,
      cs.max_unit_cost,
      cs.min_unit_cost,
      cs.avg_unit_cost,
      (cs.max_unit_cost - cs.min_unit_cost) AS max_min_variation,
      ABS(cs.max_unit_cost - cs.avg_unit_cost) AS max_avg_diff,
      ABS(cs.min_unit_cost - cs.avg_unit_cost) AS min_avg_diff
    FROM
      cost_stats cs
      INNERJOIN item i ON cs.item_id = i.item_id
  )
SELECT
  v.item_id,
  v.description,
  v.max_unit_cost,
  v.min_unit_cost,
  v.avg_unit_cost,
  v.max_min_variation,
  v.max_avg_diff,
  v.min_avg_diff
FROM
  variation v
ORDERBY
  v.max_min_variation DESC,
  v.item_id
LIMIT
  100;

Data retrieved

Please select a visualization type!

26 rows

Summarizing findings

- The data provided does not include total consumption volume for each raw material, so it is not possible to identify which items are in the top 10% by consumption volume. - However, among all listed raw materials, the largest variations between max, min, and average unit cost are observed for 'Cocoa Butter - Olam Astra-A Netherlands' (max-min: 24.71, max-avg: 11.96, min-avg: 12.76) and 'Veg Fat - Melano S1000M' (max-min: 29.69, max-avg: 23.93, min-avg: 5.76). - To accurately answer the question, total consumption volume data is required to filter the top 10% of raw materials by usage.

WITH
  item AS (
    SELECT
      description AS description,
      inventory_posting_group AS item_category_code,
      no_ AS item_id
    FROM
      base.chocolate_factory_item
  ),
  inventory_consumption AS (
    SELECTABS(consumption) AS consumption_quantity,
      itemno AS item_id
    FROM
      base.chocolate_factory_inventory_consumption
  ),
  purchase_invoice AS (
    SELECT
      no_ AS item_id,
      "Amount (LCY)" /NULLIF(qty__per_unit_of_measure * quantity, 0) AS purchase_unit_cost_normalized_to_base_unit
    FROM
      base.chocolate_factory_purchase_invoice_v2
  ),
  -- DNM_CTE: Do not modify the preceding local abstraction CTE's
  raw_materials AS (
    SELECT
      item.item_id,
      item.description,
      item.item_category_code
    FROM
      item
    WHERE
      item.item_category_code ='RM'
  ),
  consumption_agg AS (
    SELECT
      inventory_consumption.item_id,
      SUM(
        COALESCE(inventory_consumption.consumption_quantity, 0)
      ) AS total_consumption
    FROM
      inventory_consumption
      INNERJOIN raw_materials ON inventory_consumption.item_id = raw_materials.item_id
    GROUPBY
      inventory_consumption.item_id
  ),
  percentile_threshold AS (
    SELECT
      APPROX_PERCENTILE (total_consumption, 0.9) AS consumption_90th_percentile
    FROM
      consumption_agg
  ),
  top_consumed_raw_materials AS (
    SELECT
      ca.item_id,
      ca.total_consumption
    FROM
      consumption_agg ca
      CROSSJOIN percentile_threshold pt
    WHERE
      ca.total_consumption >= pt.consumption_90th_percentile
  ),
  cost_stats AS (
    SELECT
      pi.item_id,
      MAX(pi.purchase_unit_cost_normalized_to_base_unit) AS max_unit_cost,
      MIN(pi.purchase_unit_cost_normalized_to_base_unit) AS min_unit_cost,
      AVG(pi.purchase_unit_cost_normalized_to_base_unit) AS avg_unit_cost
    FROM
      purchase_invoice pi
      INNERJOIN top_consumed_raw_materials tcrm ON pi.item_id = tcrm.item_id
    WHERE
      pi.purchase_unit_cost_normalized_to_base_unit ISNOTNULLGROUPBY
      pi.item_id
  ),
  variation AS (
    SELECT
      cs.item_id,
      i.description,
      cs.max_unit_cost,
      cs.min_unit_cost,
      cs.avg_unit_cost,
      (cs.max_unit_cost - cs.min_unit_cost) AS max_min_variation,
      ABS(cs.max_unit_cost - cs.avg_unit_cost) AS max_avg_diff,
      ABS(cs.min_unit_cost - cs.avg_unit_cost) AS min_avg_diff
    FROM
      cost_stats cs
      INNERJOIN item i ON cs.item_id = i.item_id
  )
SELECT
  v.item_id,
  v.description,
  v.max_unit_cost,
  v.min_unit_cost,
  v.avg_unit_cost,
  v.max_min_variation,
  v.max_avg_diff,
  v.min_avg_diff
FROM
  variation v
ORDERBY
  v.max_min_variation DESC,
  v.item_id
LIMIT
  100;

WITH
  raw_materials AS (
    SELECT
      item.item_id,
      item.description,
      item.item_category_code
    FROM
      item
    WHERE
      item.item_category_code ='RM'
  ),
  consumption_agg AS (
    SELECT
      inventory_consumption.item_id,
      SUM(
        COALESCE(inventory_consumption.consumption_quantity, 0)
      ) AS total_consumption
    FROM
      inventory_consumption
      INNERJOIN raw_materials ON inventory_consumption.item_id = raw_materials.item_id
    GROUPBY
      inventory_consumption.item_id
  ),
  percentile_threshold AS (
    SELECT
      APPROX_PERCENTILE (total_consumption, 0.9) AS consumption_90th_percentile
    FROM
      consumption_agg
  ),
  top_consumed_raw_materials AS (
    SELECT
      ca.item_id,
      ca.total_consumption
    FROM
      consumption_agg ca
      CROSSJOIN percentile_threshold pt
    WHERE
      ca.total_consumption >= pt.consumption_90th_percentile
  ),
  cost_stats AS (
    SELECT
      pi.item_id,
      MAX(pi.purchase_unit_cost_normalized_to_base_unit) AS max_unit_cost,
      MIN(pi.purchase_unit_cost_normalized_to_base_unit) AS min_unit_cost,
      AVG(pi.purchase_unit_cost_normalized_to_base_unit) AS avg_unit_cost
    FROM
      purchase_invoice pi
      INNERJOIN top_consumed_raw_materials tcrm ON pi.item_id = tcrm.item_id
    WHERE
      pi.purchase_unit_cost_normalized_to_base_unit ISNOTNULLGROUPBY
      pi.item_id
  ),
  variation AS (
    SELECT
      cs.item_id,
      i.description,
      cs.max_unit_cost,
      cs.min_unit_cost,
      cs.avg_unit_cost,
      (cs.max_unit_cost - cs.min_unit_cost) AS max_min_variation,
      ABS(cs.max_unit_cost - cs.avg_unit_cost) AS max_avg_diff,
      ABS(cs.min_unit_cost - cs.avg_unit_cost) AS min_avg_diff
    FROM
      cost_stats cs
      INNERJOIN item i ON cs.item_id = i.item_id
  )
SELECT
  v.item_id,
  v.description,
  v.max_unit_cost,
  v.min_unit_cost,
  v.avg_unit_cost,
  v.max_min_variation,
  v.max_avg_diff,
  v.min_avg_diff
FROM
  variation v
ORDERBY
  v.max_min_variation DESC,
  v.item_id
LIMIT
  100;