Blog

SQL Interview Question (Medium): Most Profitable Companies

  • Post last modified:November 8, 2023
  • Reading time:2 mins read

Solving Medium SQL Practice Question

Question

  • Find the 3 most profitable companies in the entire world. 
  • Output the result along with the corresponding company name. 
  • Sort the result based on profits in descending order.

forbes_global_2014

This question is taken from stratascratch.

Solution
Solution 1

  • We need to find 3 profitable companies across the globe in the dataset, we have been given profit and company name.
  • So the very first thing that comes to mind is to order companies by their profit. ORDER BY clause in SQL will do the job.
  • Since we need the most profitable company first, we can add DESC with the ORDER BY clause.
  • Also, we need the top 3 companies, that we can get by Adding LIMIT 3 .

Code

Solution 2

  • The first solution was pretty simple. But we can also implement this problem using the window function.
  • The idea is to rank each company based on their profits and return the company with a rank of 3 or less.
  • In order to calculate rank we can use the RANK() function that needs a window which we can define with OVER() that takes partition and order by, here partition will be the entire dataset which defaults in the window function, for order we can pass ORDER BY profits DESC.
  • The next task is to return the company name, profit, and rank from the above subquery. To return the 3 most profitable companies, we can return all the companies with a rank ≤3.

Code

Code

Find out the code at GitHub.

Conclusion 

  • In this article, we solved a Medium SQL Question where we used clauses such as ORDER BY, LIMIT, and RANK Window Functions.
  • We discussed 2 solutions and wrote code.

Before You Leave

Tags: , , , , ,