# SQL Interview Questions – Top 3 Products

## Problem Description –

The marketing team wants to run a new promotion for the most successful category of products (the category is identified as the product_class_id).

Write a query to find out what are the top 3 selling product classes by total sales. Return the result ordered by total of sales in descending order.

## Solution –

with sales_by_category as (
select
p.product_class_id as category,
sum(o.units_sold * p.price) as total_sales
from orders o left join products p
on o.product_id = p.product_id
group by p.product_class_id
)
select category
from sales_by_category
order by total_sales desc
limit 3;

This is a common SQL Interview Question where you have been asked to find Top 3, 5 or 10 of products, customers or something based on some criteria. To solve this question first we need to join both the tables and multiply units_sold and price to get the sales amount.

select
p.product_class_id as category,
(o.units_sold * p.price) as sales_amount
from orders o left join products p
on o.product_id = p.product_id

In the next step we need to group the data by each product category and calculate the total sales amount using the SUM function.

select
p.product_class_id as category,
sum(o.units_sold * p.price) as total_sales
from orders o left join products p
on o.product_id = p.product_id
group by p.product_class_id

Here we almost got the result that we want. All we need to do is order the result by total sales amount in decreasing order and limit the result to 3 as we are asked to find only the top 3 Product categories.

with sales_by_category as (
select
p.product_class_id as category,
sum(o.units_sold * p.price) as total_sales
from orders o left join products p
on o.product_id = p.product_id
group by p.product_class_id
)
select category
from sales_by_category
order by total_sales desc
limit 3;

To Practice more free problem like this, visit – BigTechInterviews

Rating: 1 out of 5.