Average Selling Price of Each Canteen Item — SQL Easy Problem

Problem statement

The canteen changes its prices from time to time. PriceList holds every price an item has had, each valid for a period of days, and Sale records how many plates of an item were sold on a day.

For every item in PriceList, return item_id and average_price: the total money taken for the item — each sale's quantity times the price that was valid on the sale's date — divided by the total quantity sold, rounded to 2 decimal places. An item that has never been sold has an average_price of 0. Return one row per item, in any order.

Tables

Table: PriceList

Primary key: item_id, valid_from.

A period includes both of its end dates. An item's periods never overlap, and every sale of the item falls inside exactly one of them.

Table: Sale

Primary key: sale_id.

One row per sale: quantity plates of an item on sold_on.

Examples

Example 1

PriceList

Sale

Output

How to solve Average Selling Price of Each Canteen Item

Each sale has to be priced at the rate that applied on its date. The join condition therefore has two parts: the same item_id, and sold_on BETWEEN valid_from AND valid_to. BETWEEN includes both ends, which matters here — a sale on the last day of one period or the first day of the next must find its price, and a strict < / > would lose it. Because an item's periods never overlap, every sale matches exactly one price row.

The average is weighted: 10 plates at ₹40 and 20 at ₹45 average ₹43.33, not ₹42.50, so it is SUM(quantity * price) / SUM(quantity), not AVG(price).

Every item must appear, so the join is a LEFT JOIN from PriceList. An unsold item keeps its price rows with NULL sales; both sums are NULL, the division is NULL, and IFNULL(…, 0) gives the 0 the statement asks for. Grouping by p.item_id folds an item's several price rows into one output row — the unmatched periods of a sold item contribute only NULLs, which SUM ignores.

The alternatives compute the takings of sold items first and attach them to the list of items, or use correlated sums per item. All of them read each sale once against its item's few price rows.

Reference solution (MySQL)

SELECT p.item_id,
       IFNULL(ROUND(SUM(s.quantity * p.price) / SUM(s.quantity), 2), 0) AS average_price
FROM PriceList p
LEFT JOIN Sale s
  ON s.item_id = p.item_id
 AND s.sold_on BETWEEN p.valid_from AND p.valid_to
GROUP BY p.item_id

Another way

WITH takings AS (
  SELECT s.item_id, SUM(s.quantity * p.price) AS money, SUM(s.quantity) AS plates
  FROM Sale s JOIN PriceList p ON p.item_id = s.item_id AND s.sold_on BETWEEN p.valid_from AND p.valid_to
  GROUP BY s.item_id
)
SELECT i.item_id, IFNULL(ROUND(t.money / t.plates, 2), 0) AS average_price
FROM (SELECT DISTINCT item_id FROM PriceList) i
LEFT JOIN takings t ON t.item_id = i.item_id

Another way

SELECT i.item_id, IFNULL(ROUND(
  (SELECT SUM(s.quantity * p.price) FROM Sale s JOIN PriceList p ON p.item_id = s.item_id AND s.sold_on >= p.valid_from AND s.sold_on <= p.valid_to WHERE s.item_id = i.item_id)
  / (SELECT SUM(s.quantity) FROM Sale s WHERE s.item_id = i.item_id), 2), 0) AS average_price
FROM (SELECT DISTINCT item_id FROM PriceList) i