Lesson 11.1

Top-N Reports in SQL

'Show me the top 5' is the most common report there is: aggregate, sort, then LIMIT.

Top-N questions are common in dashboards: the best-selling products, the busiest customers, the slowest orders. A common pattern is to aggregate to get a number per group, sort by it, then LIMIT to the few rows you care about.

Here "revenue" is sum(quantity * unit_price) across a product's line items. The starter already builds the full ranked list. Your job is to keep only the top five.

Pattern

select category, sum(amount) as total
from sales
group by category
order by total desc
limit 10

Preparing for an interview? The ranking and top-N interview questions add ties and top-N per group, with model answers to compare against.

Schema · Garden Shop Tables · order_items, products 2 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Your task

Find the top 5 products by total revenue. Return product_name and revenue, biggest first.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: product_name, revenue.
Rows: the top 5 products by revenue.
Revenue multiplies quantity by unit_price across every line item for that product.

← Back to all lessons

Next · 11.2 Percentages and rates →

Reference

SQL Subqueries and EXISTS

Nested SELECTs, IN, EXISTS, and correlation.

Cheatsheet

SQL Window Functions Cheatsheet

Ranking, LAG/LEAD, and running totals at a glance.

Interview

SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake

SQL: Window Running Totals Can Jump on Ties

The default RANGE frame groups tied rows; use ROWS.

Error

Fix “Window Functions Are Not Allowed in WHERE” in SQL

Filter window results in a CTE or with QUALIFY.

Reference

SQL Common Table Expressions

The WITH clause, chained steps, and CTE vs subquery.

Related