Deviopath › PostgreSQL

Top 2 Best Sellers per Category

Hard · Window Functions

"Top N per group" is a classic: rank rows inside each group with ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), then keep the rows with a small rank. ROW_NUMBER never gives ties, so the ORDER BY inside OVER needs a tie-breaker to be deterministic.

A product's units_sold is the sum of its quantity over all order items. For each category, return the 2 products with the most units sold (only products that sold at least once). When two products sold the same number of units, the one with the alphabetically first name ranks higher.

Return category, product (name), units_sold and place (1 or 2), sorted by category, then place.

Next: Month-over-Month Growth