Top-N per group query in SQL - Mini Project: Build & Apply

Top-N per Group Query in SQL

📖 Scenario: You are managing a sales database for a retail company. The company wants to identify the top 2 best-selling products in each category based on the total sales amount.

🎯 Goal: Write an SQL query to find the top 2 products with the highest sales amount in each product category.

📋 What You'll Learn

Create a table called sales with columns category, product, and sales_amount.

Insert the given sales data into the sales table.

Write a query using window functions to rank products by sales amount within each category.

Select only the top 2 products per category based on the ranking.

💡 Why This Matters

🌍 Real World

Retail companies often need to find top-selling products in each category to focus marketing and inventory efforts.

💼 Career

Knowing how to write top-N per group queries is essential for data analysts and database developers working with sales or transactional data.

1

Create the sales table and insert data

Create a table called sales with columns category (text), product (text), and sales_amount (integer). Then insert these exact rows into the sales table: ('Electronics', 'Laptop', 1500), ('Electronics', 'Smartphone', 2000), ('Electronics', 'Tablet', 800), ('Furniture', 'Chair', 300), ('Furniture', 'Table', 700), ('Furniture', 'Couch', 1200).

SQL

-- Create the sales table and insert the given data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Add a ranking column using window function

Write a query that selects all columns from sales and adds a new column called rank which ranks products within each category by sales_amount in descending order. Use the ROW_NUMBER() window function partitioned by category and ordered by sales_amount DESC.

SQL

CREATE TABLE sales (
    category TEXT,
    product TEXT,
    sales_amount INTEGER
);

INSERT INTO sales (category, product, sales_amount) VALUES
('Electronics', 'Laptop', 1500),
('Electronics', 'Smartphone', 2000),
('Electronics', 'Tablet', 800),
('Furniture', 'Chair', 300),
('Furniture', 'Table', 700),
('Furniture', 'Couch', 1200);

-- Write a SELECT query that adds a rank column using ROW_NUMBER() partitioned by category
-- Your code here

Need a hint?

Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_amount DESC) to assign ranks.

3

Filter to get top 2 products per category

Wrap the previous query as a subquery and select only rows where rank is less than or equal to 2 to get the top 2 products per category.

SQL

CREATE TABLE sales (
    category TEXT,
    product TEXT,
    sales_amount INTEGER
);

INSERT INTO sales (category, product, sales_amount) VALUES
('Electronics', 'Laptop', 1500),
('Electronics', 'Smartphone', 2000),
('Electronics', 'Tablet', 800),
('Furniture', 'Chair', 300),
('Furniture', 'Table', 700),
('Furniture', 'Couch', 1200);

-- Select only rows where rank <= 2 from the previous query
-- Your code here

Need a hint?

Use a subquery and filter with WHERE rank <= 2.

4

Order the final results by category and rank

Add an ORDER BY clause to the final query to sort the results by category ascending and rank ascending.

SQL

CREATE TABLE sales (
    category TEXT,
    product TEXT,
    sales_amount INTEGER
);

INSERT INTO sales (category, product, sales_amount) VALUES
('Electronics', 'Laptop', 1500),
('Electronics', 'Smartphone', 2000),
('Electronics', 'Tablet', 800),
('Furniture', 'Chair', 300),
('Furniture', 'Table', 700),
('Furniture', 'Couch', 1200);

-- Add ORDER BY category ASC, rank ASC to the final query
-- Your code here

Need a hint?

Use ORDER BY category ASC, rank ASC at the end of the query.