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
| Column | Type |
|---|---|
| category | varchar |
| referral_pct | int |
Primary key: category.
The referral fee percentage of a category.
Table: Listing
| Column | Type |
|---|---|
| asin | varchar |
| seller_id | int |
| category | varchar |
| weight_grams | int |
Primary key: asin.
One row per product, sold by one seller; category always names a category.
Table: Sale
| Column | Type |
|---|---|
| sale_id | int |
| asin | varchar |
| sold_on | date |
| quantity | int |
| unit_price | int |
| channel | enum(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
| category | referral_pct |
|---|---|
| Books | 5 |
| Kitchen | 12 |
| Electronics | 8 |
Listing
| asin | seller_id | category | weight_grams |
|---|---|---|---|
| B0BOOK0001 | 31 | Books | 450 |
| B0PAN00002 | 31 | Kitchen | 2000 |
| B0SPEAKER3 | 32 | Electronics | 2600 |
| B0CABLE004 | 33 | Electronics | 80 |
Sale
| sale_id | asin | sold_on | quantity | unit_price | channel |
|---|---|---|---|---|---|
| 1 | B0BOOK0001 | 2025-10-02 | 3 | 299 | FBA |
| 2 | B0PAN00002 | 2025-10-15 | 1 | 1249 | FBA |
| 3 | B0PAN00002 | 2025-10-31 | 2 | 1249 | MFN |
| 4 | B0SPEAKER3 | 2025-10-20 | 1 | 3999 | FBA |
| 5 | B0SPEAKER3 | 2025-11-01 | 1 | 3999 | FBA |
| 6 | B0CABLE004 | 2025-09-30 | 4 | 199 | FBA |
Output
| seller_id | referral_fees | fba_fees |
|---|---|---|
| 31 | 494.49 | 190 |
| 32 | 319.92 | 120 |
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_idAnother 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 →