A Number for the Seller

An easy Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
easy
Seniority
L3
Asked in
Amazon

Problem

We run an online marketplace where sellers list products, and each product records the date its listing went live and whether that listing is still active, since a seller may delist or relist over time. Every sale is logged line by line with the units sold and the revenue earned. Build the analytics schema behind a daily seller dashboard that shows, for each seller, how many of their listings are currently active and, for each date, the revenue and units their sales earned.

They want a total. Give them the right schema first.

Worked solution and explanation

Two numbers, two grains

This is a two-grain problem wearing a seller-dashboard costume. The active-listing count is a snapshot count over products; revenue and units are additive sums over sale lines. Anyone can draw four tables. What separates candidates is refusing to let the sales rows answer the listings question. Collapse the two into one fact and every product that never sold drops out of the count, and the moment listings and sales meet before aggregation, revenue fans out by the number of listings. Both numbers come back wrong, and neither looks obviously broken.

Building it

Step 1: Name the two grains out loud

Say it before you draw: one number counts things that exist (listings), the other sums things that happened (sale lines). A thing that exists does not need to have happened to be counted, so they cannot share a table.

Step 2: Declare fact_sales at one row per sale line

One row per product sold within an order, keyed by its own sale_key, carrying quantity and gross_revenue. That is the most atomic grain the log offers, so daily totals per seller are a plain sum and nothing is lost if someone later wants per-product numbers.

Step 3: Put listing status on dim_products

listed_at and is_active describe the listing, not any sale, so they live on dim_products. Counting active listings becomes a filter on is_active over the product table, and an unsold product still counts because its row exists whether or not it ever appears in fact_sales.

Step 4: Hang both off one dim_sellers

Both dim_products.seller_key and fact_sales.seller_key point at the same dim_sellers row. The shared dimension is the only place the two metrics meet, and they meet there after each has been rolled up to one row per seller.

Step 5: Give the calendar its own dim_date

The dashboard reads by date, so the fact stores only date_key and dim_date holds full_date. Adding day_of_week and month there costs nothing and means a weekday or month comparison is written once instead of in every dashboard query.

The reference design

`fact_sales` is one row per sale line; the active-listing count comes from `dim_products` (`listed_at`, `is_active`), never from `fact_sales`.
  • dim_date: date_key INT PK, full_date DATE, day_of_week INT, month INT
  • dim_sellers: seller_key INT PK, seller_name TEXT, joined_at TIMESTAMP, region TEXT
  • dim_products: product_key INT PK, seller_key INT FK, product_name TEXT, listed_at TIMESTAMP, is_active BOOLEAN
  • fact_sales: sale_key BIGINT PK, product_key INT FK, seller_key INT FK, date_key INT FK, quantity INT, gross_revenue DECIMAL
  • dim_products.seller_key references dim_sellers.seller_key
  • fact_sales.seller_key references dim_sellers.seller_key
  • fact_sales.product_key references dim_products.product_key
  • fact_sales.date_key references dim_date.date_key
The dashboard query the design makes easy
WITH active_listings AS (
    SELECT seller_key, COUNT(*) AS active_listings
    FROM dim_products
    WHERE is_active = TRUE
    GROUP BY seller_key
),
daily_sales AS (
    SELECT sale.seller_key,
           cal.full_date,
           SUM(sale.gross_revenue) AS revenue,
           SUM(sale.quantity) AS units
    FROM fact_sales AS sale
    JOIN dim_date AS cal ON cal.date_key = sale.date_key
    GROUP BY sale.seller_key, cal.full_date
)
SELECT seller.seller_name,
       COALESCE(listing.active_listings, 0) AS active_listings,
       sales.full_date,
       sales.revenue,
       sales.units
FROM dim_sellers AS seller
LEFT JOIN active_listings AS listing ON listing.seller_key = seller.seller_key
LEFT JOIN daily_sales AS sales ON sales.seller_key = seller.seller_key

Each metric is aggregated in its own CTE; they meet only at dim_sellers.

Two grains, star schema
dim_products holds listing status, fact_sales holds sale lines. Unsold products still count, revenue sums once per line, and BI tools slice by dim_date attributes for free.
One flat sales table
Listing status copied onto each sale row. Products with no sales vanish from the count, a relisted product carries stale is_active values on old rows, and any join back to products multiplies revenue.

Common follow-up questions