Average Price per Coffee Bag

A coffee roaster changes the price of each blend a few times a year. Finance wants the average price actually paid per bag for every blend, weighted by how many bags each order contained: total money received for the blend divided by total bags sold.

An order is charged the price of the period that contains its order_date; both valid_from and valid_to are inclusive. Every order falls in exactly one period of its blend.

Return blend_id, blend_name and avg_price, rounded to 2 decimal places, for every blend in blends. A blend with no orders gets an avg_price of 0. Rows may be in any order.

Tables

blends — One row per coffee blend the roaster sells.

price_list — Price periods per blend. Periods of the same blend never overlap.

bag_orders — One row per order.

Example

blends

price_list

bag_orders

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Aggregation & Grouping · problem 14 of 70 · SQLite

The idea

The work splits into three steps.

1. Price each order. Join bag_orders to price_list on the blend and on order_date BETWEEN valid_from AND valid_to. BETWEEN is inclusive on both ends, matching the rule, and ISO date strings compare correctly as text. Since periods do not overlap, each order matches exactly one price.

2. Weight by quantity. The average price paid per bag is revenue divided by bags: SUM(bags * bag_price) / SUM(bags). AVG(bag_price) would give every order the same weight regardless of size. Both sums are integers, and SQLite truncates integer division, so multiply by 1.0 first and then ROUND(..., 2).

3. Keep every blend. Drive the query from blends with LEFT JOINs. An unsold blend then has one row of NULLs, both sums are NULL, the division is NULL, and COALESCE(..., 0) turns that into 0.

The alternative computes revenue and bags per blend in a CTE with inner joins, then left-joins that small summary to blends. Aggregating before joining keeps the logic for sold and unsold blends cleanly separated.

Solution

SELECT b.blend_id,
       b.blend_name,
       ROUND(COALESCE(SUM(o.bags * p.bag_price) * 1.0 / SUM(o.bags), 0), 2) AS avg_price
FROM blends AS b
LEFT JOIN bag_orders AS o
  ON o.blend_id = b.blend_id
LEFT JOIN price_list AS p
  ON p.blend_id = o.blend_id
 AND o.order_date BETWEEN p.valid_from AND p.valid_to
GROUP BY b.blend_id, b.blend_name;

Another way

WITH blend_totals AS (
  SELECT o.blend_id,
         SUM(o.bags * p.bag_price) AS revenue,
         SUM(o.bags) AS bags_sold
  FROM bag_orders AS o
  JOIN price_list AS p
    ON p.blend_id = o.blend_id
   AND o.order_date >= p.valid_from
   AND o.order_date <= p.valid_to
  GROUP BY o.blend_id
)
SELECT b.blend_id,
       b.blend_name,
       ROUND(COALESCE(CAST(t.revenue AS REAL) / t.bags_sold, 0), 2) AS avg_price
FROM blends AS b
LEFT JOIN blend_totals AS t
  ON t.blend_id = b.blend_id;

Why common answers fail

Inner joins drop blends that never sold, but every blend must appear with 0.

SELECT b.blend_id, b.blend_name, ROUND(SUM(o.bags * p.bag_price) * 1.0 / SUM(o.bags), 2) AS avg_price FROM blends AS b JOIN bag_orders AS o ON o.blend_id = b.blend_id JOIN price_list AS p ON p.blend_id = o.blend_id AND o.order_date BETWEEN p.valid_from AND p.valid_to GROUP BY b.blend_id, b.blend_name;

Divides two integers, so SQLite truncates the result to a whole number of dollars.

SELECT b.blend_id, b.blend_name, ROUND(COALESCE(SUM(o.bags * p.bag_price) / SUM(o.bags), 0), 2) AS avg_price FROM blends AS b LEFT JOIN bag_orders AS o ON o.blend_id = b.blend_id LEFT JOIN price_list AS p ON p.blend_id = o.blend_id AND o.order_date BETWEEN p.valid_from AND p.valid_to GROUP BY b.blend_id, b.blend_name;

Averages the price per order, ignoring how many bags each order had, so a one-bag order weighs as much as a ten-bag order.

SELECT b.blend_id, b.blend_name, ROUND(COALESCE(AVG(p.bag_price), 0), 2) AS avg_price FROM blends AS b LEFT JOIN bag_orders AS o ON o.blend_id = b.blend_id LEFT JOIN price_list AS p ON p.blend_id = o.blend_id AND o.order_date BETWEEN p.valid_from AND p.valid_to GROUP BY b.blend_id, b.blend_name;

Treats the period ends as exclusive, so orders placed on the first or last day of a period find no price.

SELECT b.blend_id, b.blend_name, ROUND(COALESCE(SUM(o.bags * p.bag_price) * 1.0 / SUM(o.bags), 0), 2) AS avg_price FROM blends AS b LEFT JOIN bag_orders AS o ON o.blend_id = b.blend_id LEFT JOIN price_list AS p ON p.blend_id = o.blend_id AND o.order_date > p.valid_from AND o.order_date < p.valid_to GROUP BY b.blend_id, b.blend_name;

Joins prices on the blend only, so each order is matched with every price period of its blend.

SELECT b.blend_id, b.blend_name, ROUND(COALESCE(SUM(o.bags * p.bag_price) * 1.0 / SUM(o.bags), 0), 2) AS avg_price FROM blends AS b LEFT JOIN bag_orders AS o ON o.blend_id = b.blend_id LEFT JOIN price_list AS p ON p.blend_id = o.blend_id GROUP BY b.blend_id, b.blend_name;

What the interviewer is checking

Combines a range join, a weighted average, integer division and preserving rows with no activity; interviewers watch which of those you handle without prompting.

Mistakes to avoid

• Using AVG(price) instead of a quantity-weighted average.
• Integer division truncating the result.
• Inner joins that drop blends with no orders.
• Exclusive date bounds that miss orders on the first or last day of a period.

Follow-ups to expect

• Report the average per blend per quarter. (add strftime-based quarter to GROUP BY)
• Find orders that match no price period. (LEFT JOIN price_list and keep rows where p.blend_id IS NULL)
• Detect overlapping price periods. (self-join price_list on blend with a.valid_from <= b.valid_to AND b.valid_from <= a.valid_to)

In real work

Revenue analytics regularly join transactions to time-versioned price lists, tax rates or exchange rates, then compute weighted averages per product.

Practice next