Market Share

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

An investor slide claims every category earns nearly all its revenue from products we can ship today, and merchandising doesn't believe it: rebuild it from transactions and products as each category's percentage of its own revenue coming from products currently in stock, most in-stock first. The slide shows whole percentage points with the fraction dropped rather than rounded, alphabetical where two categories tie.

Every category wants a bigger slice.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this really is

This is a conditional part-to-whole ratio wearing an investor-deck costume, with a formatting rule that quietly decides the grade. Anyone can join transactions to products and sum revenue per category. What separates candidates is building the part and the whole in the same grouped pass, and then trimming it correctly. Filter out-of-stock rows with a WHERE and the denominator shrinks with the numerator, so every category reads 100, which is exactly the slide merchandising refused to believe. Then the share has to land in whole points with the fraction dropped: reach for ROUND and a 97.8 becomes 98.

Filter the rows

WHERE p.in_stock = 1 before grouping. The out-of-stock sales vanish from both sums, so numerator equals denominator and every category reads 100.

Filter inside the numerator

Keep every row and weight the numerator by p.in_stock. The denominator still sees the full category, so the share describes a real split.

Building it

Step 1: Attach category and stock status

Join transactions to products on product_id so every sale carries its category and its in_stock flag. Revenue is the total_amount already on the transaction; recomputing price * quantity gives a different number.

Step 2: Build the part and the whole together

GROUP BY p.category gives one row per category. In that row, SUM(t.total_amount * p.in_stock) is the in-stock revenue and SUM(t.total_amount) is all of it.

Step 3: Turn it into a percentage

Multiply the part by 100.0 and divide by the whole. The 100.0 keeps the arithmetic in decimals so nothing is lost before you decide how to trim it.

Step 4: Truncate to whole points with CAST

CAST(... AS INTEGER) drops the fraction: 97.8 becomes 97. That is the slide's rule. ROUND would push it to 98, which is a different answer, not a style choice.

Step 5: Make the order total

Sort by in_stock_revenue_pct DESC, then p.category. Dropping the fraction collapses nearby shares onto the same whole point, so without the alphabetical tiebreak those rows come back in whatever order the engine likes.

The solution

SELECT p.category,
    CAST(SUM(t.total_amount * p.in_stock) * 100.0 / SUM(t.total_amount) AS INTEGER) AS in_stock_revenue_pct
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY p.category
ORDER BY in_stock_revenue_pct DESC, p.category

Common follow-up questions

• Merchandising now also wants the share from products rated 4 or higher. How do you add that column without a second query? (Tests that the flag trick generalizes to any 0/1 or derived condition.)
• `in_stock` reflects today, not the day of the sale. What would you need to answer the question historically? (Tests noticing that `in_stock` is current state, not state at the time of sale.)
• The deck wants the same split per month per category. What changes in the grouping? (Tests scaling the same shape to a different grain.)