Wholesale Orders with Bean Origin

A coffee roaster sells green beans to cafes in bulk. The order table only stores a bean_id, but the sales team wants a readable price list built from real orders.

For every wholesale order, return origin_name, order_year and price_per_bag, in that order. Keep one row per order, even if two orders produce identical rows. Beans that were never ordered must not appear. Rows may be in any order; return no rows if there are no orders.

Tables

beans — One row per coffee the roaster stocks.

wholesale_orders — One row per bulk order placed by a cafe. Every bean_id exists in beans.

Example

beans

wholesale_orders

Expected output

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

SQL Path · Joins · problem 24 of 70 · SQLite

The idea

This is a lookup: each order carries a foreign key bean_id, and the readable name sits in beans. An inner JOIN … ON b.bean_id = o.bean_id attaches the bean row to every order.

An inner join is the right choice for two reasons. Every order has a valid bean, so no order is lost. And beans that were never ordered have no partner row, so they drop out automatically, which is what the statement asks for. Since bean_id is the primary key of beans, each order matches exactly one bean, and the result has exactly one row per order.

Do not add DISTINCT: two different orders can legitimately produce the same three output values, and the task wants one row per order. The alternative fetches the name with a correlated scalar subquery, which works because the lookup returns at most one value.

Solution

SELECT
  b.origin_name,
  o.order_year,
  o.price_per_bag
FROM wholesale_orders AS o
JOIN beans AS b
  ON b.bean_id = o.bean_id;

Another way

SELECT
  (SELECT b.origin_name FROM beans AS b WHERE b.bean_id = o.bean_id) AS origin_name,
  o.order_year,
  o.price_per_bag
FROM wholesale_orders AS o;

Why common answers fail

A left join from beans adds rows with NULL year and price for beans nobody ordered.

SELECT b.origin_name, o.order_year, o.price_per_bag
FROM beans AS b
LEFT JOIN wholesale_orders AS o ON o.bean_id = b.bean_id;

DISTINCT merges separate orders that happen to share bean, year and price.

SELECT DISTINCT b.origin_name, o.order_year, o.price_per_bag
FROM wholesale_orders AS o
JOIN beans AS b ON b.bean_id = o.bean_id;

Joins on the wrong key (order_id instead of bean_id); it only works by coincidence when the numbers overlap.

SELECT b.origin_name, o.order_year, o.price_per_bag
FROM wholesale_orders AS o
JOIN beans AS b ON b.bean_id = o.order_id;

What the interviewer is checking

A warm-up that checks join keys, join type, and whether you know the cardinality of the result before you run it.

Mistakes to avoid

• Starting from beans with a LEFT JOIN and listing unordered beans.
• Adding DISTINCT and merging real orders.
• Joining on the wrong id column.

Follow-ups to expect

• Add revenue per order (bags * price_per_bag).
• Average price per origin and year (GROUP BY origin_name, order_year).
• Also list beans never ordered (LEFT JOIN from beans with WHERE o.order_id IS NULL).

In real work

Turning fact tables full of ids into readable reports is the most common join in analytics: order lines with product names, events with user names.

Practice next