Amazon: Referral and FBA Fees per Seller for October

Medium
589.

Amazon: Referral and FBA Fees per Seller for October

Seller Central sends every marketplace seller a monthly fee statement. This exercise uses a simplified fee card: a referral fee of the category's referral_pct percent on every unit's sale price, and a fulfilment (FBA) fee per unit shipped by the fulfilment network — 40 rupees for a product of at most 500 g, 70 for at most 2000 g, and 120 above that. Units the seller ships themselves (MFN) pay no FBA fee.

For sales made in October 2025, return seller_id, referral_fees (rounded to 2 decimals) and fba_fees for every seller with at least one October sale. Return the rows in any order.

A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.

Tables

Table: Category

ColumnType
categoryvarchar
referral_pctint

Primary key: category.

The referral fee percentage of a category.

Table: Listing

ColumnType
asinvarchar
seller_idint
categoryvarchar
weight_gramsint

Primary key: asin.

One row per product, sold by one seller; category always names a category.

Table: Sale

ColumnTypeValues
sale_idint
asinvarchar
sold_ondate
quantityint
unit_priceint
channelenumFBA, MFN

Primary key: sale_id.

One row per order line; unit_price is in rupees and asin always names a listing.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Category

categoryreferral_pct
Books5
Kitchen12
Electronics8

Listing

asinseller_idcategoryweight_grams
B0BOOK000131Books450
B0PAN0000231Kitchen2000
B0SPEAKER332Electronics2600
B0CABLE00433Electronics80

Sale

sale_idasinsold_onquantityunit_pricechannel
1B0BOOK00012025-10-023299FBA
2B0PAN000022025-10-1511249FBA
3B0PAN000022025-10-3121249MFN
4B0SPEAKER32025-10-2013999FBA
5B0SPEAKER32025-11-0113999FBA
6B0CABLE0042025-09-304199FBA

Output

seller_idreferral_feesfba_fees
31494.49190
32319.92120
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Amazon: Referral and FBA Fees per Seller for October

Seller Central sends every marketplace seller a monthly fee statement.

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #589

Problem statement

Seller Central sends every marketplace seller a monthly fee statement. This exercise uses a simplified fee card: a referral fee of the category's referral_pct percent on every unit's sale price, and a fulfilment (FBA) fee per unit shipped by the fulfilment network — 40 rupees for a product of at most 500 g, 70 for at most 2000 g, and 120 above that. Units the seller ships themselves (MFN) pay no FBA fee.

For sales made in October 2025, return seller_id, referral_fees (rounded to 2 decimals) and fba_fees for every seller with at least one October sale. Return the rows in any order.

A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.

Tables

Table: Category

ColumnType
categoryvarchar
referral_pctint

Primary key: category.

The referral fee percentage of a category.

Table: Listing

ColumnType
asinvarchar
seller_idint
categoryvarchar
weight_gramsint

Primary key: asin.

One row per product, sold by one seller; category always names a category.

Table: Sale

ColumnType
sale_idint
asinvarchar
sold_ondate
quantityint
unit_priceint
channelenum(FBA, MFN)

Primary key: sale_id.

One row per order line; unit_price is in rupees and asin always names a listing.

Examples

Example 1

Category

categoryreferral_pct
Books5
Kitchen12
Electronics8

Listing

asinseller_idcategoryweight_grams
B0BOOK000131Books450
B0PAN0000231Kitchen2000
B0SPEAKER332Electronics2600
B0CABLE00433Electronics80

Sale

sale_idasinsold_onquantityunit_pricechannel
1B0BOOK00012025-10-023299FBA
2B0PAN000022025-10-1511249FBA
3B0PAN000022025-10-3121249MFN
4B0SPEAKER32025-10-2013999FBA
5B0SPEAKER32025-11-0113999FBA
6B0CABLE0042025-09-304199FBA

Output

seller_idreferral_feesfba_fees
31494.49190
32319.92120

How to solve Amazon: Referral and FBA Fees per Seller for October

Each order line contributes two fees, computed from three tables: the sale (quantity, price, channel), the listing (seller and weight) and the category (percentage). Two inner joins bring them together, the October filter keeps the month's sales, and grouping by seller_id sums each fee.

The referral fee of a line is quantity × unit_price × referral_pct / 100; summing first and rounding once at the end avoids rounding errors piling up. The FBA fee is a CASE over the channel and the weight band, multiplied by the quantity because the fee is per unit. The order of the WHENs matters: MFN lines are caught first, then <= 500, then <= 2000, so a 500 g product pays 40 and a 2000 g one pays 70 — the boundaries belong to the lower band.

Sellers without October sales produce no joined rows and do not appear. The alternative looks up the percentage with a scalar subquery, writes the bands with nested IFs and filters the month with YEAR/MONTH; the date-range form is preferable when sold_on is indexed.

Reference solution (MySQL)

SELECT l.seller_id,
       ROUND(SUM(s.quantity * s.unit_price * c.referral_pct / 100), 2) AS referral_fees,
       SUM(CASE WHEN s.channel <> 'FBA' THEN 0
                WHEN l.weight_grams <= 500 THEN 40 * s.quantity
                WHEN l.weight_grams <= 2000 THEN 70 * s.quantity
                ELSE 120 * s.quantity END) AS fba_fees
FROM Sale s
JOIN Listing l ON l.asin = s.asin
JOIN Category c ON c.category = l.category
WHERE s.sold_on BETWEEN '2025-10-01' AND '2025-10-31'
GROUP BY l.seller_id

Another way

SELECT l.seller_id,
       ROUND(SUM(s.quantity * s.unit_price * (SELECT c.referral_pct FROM Category c WHERE c.category = l.category)) / 100, 2) AS referral_fees,
       SUM(IF(s.channel = 'FBA', s.quantity * IF(l.weight_grams <= 500, 40, IF(l.weight_grams <= 2000, 70, 120)), 0)) AS fba_fees
FROM Sale s JOIN Listing l ON l.asin = s.asin
WHERE YEAR(s.sold_on) = 2025 AND MONTH(s.sold_on) = 10
GROUP BY l.seller_id

← Amazon: Prime Day Deals Not Below the 30-Day Low Price · Amazon: Straight-Line Distance Driven on Each Delivery Route →