Top N analysis

Top N analysis finds the highest or lowest ranked rows from a dataset.

About 5–10 minutes · no sign-up needed

Example

Finding Top Retail Products with Ranking Functions

SELECT * FROM (
  SELECT product_id, brand, units_sold,
         DENSE_RANK() OVER (ORDER BY units_sold DESC) AS rnk
  FROM products
) ranked
WHERE rnk <= 3

Result:

product_id	brand	units_sold	rnk
P1005	ActivePro	5500	1
P1001	NovaTech	4200	2
P1002	UrbanWear	3800	3
P1003	GlowBeauty	3800	3

Step by step

The asterisk symbol means all columns.
```
SELECT *
```

FROM tells SQL which table to pull the data from.

SELECT *
FROM (
  SELECT product_id, brand, units_sold,
         DENSE_RANK() OVER (ORDER BY units_sold DESC) AS rnk
  FROM products
) ranked

WHERE keeps only the rows that match your condition.

SELECT *
FROM (
  SELECT product_id, brand, units_sold,
         DENSE_RANK() OVER (ORDER BY units_sold DESC) AS rnk
  FROM products
) ranked
WHERE rnk <= 3;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query to return the top 10 rows by revenue.

SELECT * FROM orders ORDER BY revenue DESC LIMIT 10

2. Using products, write a query to return the top 3 rows by price.

SELECT * FROM products ORDER BY price DESC LIMIT 3

3. Using store_sales, write a query to return the top 10 rows by revenue.

SELECT * FROM store_sales ORDER BY revenue DESC LIMIT 10

4. Using reviews, write a query to return the top 10 rows by rating.

SELECT * FROM reviews ORDER BY rating DESC LIMIT 10

5. Using employees, write a query to return the top 10 rows by salary.

SELECT * FROM employees ORDER BY salary DESC LIMIT 10

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed