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.

Difficulty Level – Hard

Company – Meta

Problem Link – Top 3 Products

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