Top Product Category by Sales

Medium ⏱ 12 min 57% acceptance ★★★★★ 4.5
Rank product categories by total sales value from order items, where a line's value is quantity * unit_price. Return category and total_sales, highest first.

Database Schema

Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.

customers 5 columns
customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE
products 4 columns
product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL
orders 5 columns
order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT
order_items 4 columns
order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL
payments 5 columns
payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE
employees 6 columns
employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE

Examples

Example 1
Input
order_items joined to products
Output
category | total_sales
Furniture | ...
Electronics | ...
Explanation

Multiply quantity by unit_price per line, join to products for the category, then sum per category.

Constraints

  • Line value = quantity * unit_price.
  • Order by total_sales descending.

Topics

JOINGROUP BYAggregationHAVING

Companies

FlipkartWalmartSwiggy

Hints

Hint 1

Join order_items to products on product_id.

Hint 2

SUM(quantity * unit_price) grouped by category.

Reveal Official Solution

Try it yourself first — you learn more from a struggle than a spoiler.

SELECT p.category, SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY total_sales DESC;

Related Problems

← Employees Earning More Than Their Manager Running Total of Revenue →
Press Run (or Ctrl+Enter) to execute your query against the sample database.
customers5 cols
  • customer_idINTEGER
  • nameTEXT
  • cityTEXT
  • countryTEXT
  • signup_dateDATE
customer_idnamecitycountrysignup_date
101RahulDelhiIndia2023-01-12
102AmanMumbaiIndia2023-02-03
103SaraBengaluruIndia2023-02-20
products4 cols
  • product_idINTEGER
  • product_nameTEXT
  • categoryTEXT
  • priceREAL
product_idproduct_namecategoryprice
1Wireless MouseElectronics799
2Office ChairFurniture5499
3NotebookStationery149
orders5 cols
  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATE
  • amountREAL
  • statusTEXT
order_idcustomer_idorder_dateamountstatus
10011012024-01-051598Delivered
10021022024-01-115499Delivered
10031012024-02-02149Cancelled
order_items4 cols
  • order_idINTEGER
  • product_idINTEGER
  • quantityINTEGER
  • unit_priceREAL
order_idproduct_idquantityunit_price
100112799
1002215499
100435149
payments5 cols
  • payment_idINTEGER
  • order_idINTEGER
  • methodTEXT
  • amountREAL
  • paid_dateDATE
payment_idorder_idmethodamountpaid_date
50011001UPI15982024-01-05
50021002Card54992024-01-11
50031005UPI9992024-03-02
employees6 cols
  • employee_idINTEGER
  • nameTEXT
  • departmentTEXT
  • manager_idINTEGER
  • salaryREAL
  • hire_dateDATE
employee_idnamedepartmentmanager_idsalaryhire_date
1MeeraAnalyticsNULL180000
2RohitAnalytics195000
3NehaEngineering1120000
Loading expected output…