SQL Interview Question (Medium): Most Profitable Companies

Summary

The article provides two SQL solutions to identify the top three most profitable companies worldwide, with a focus on using ORDER BY, LIMIT, and RANK window functions.

Abstract

The web content discusses a medium-level SQL practice question that challenges users to find and list the three most profitable companies globally, sorted by profits in descending order. The author presents two distinct solutions to this problem. The first solution involves ordering the companies by profit and limiting the results to the top three using the ORDER BY and LIMIT clauses in SQL. The second solution employs a more advanced approach using the RANK() window function to assign ranks based on profits and then filtering for companies with a rank of three or less. The article also provides visual code snippets for both solutions and concludes by summarizing the SQL clauses used. Additionally, the author encourages readers to enhance their SQL skills through resources like Analyst Builder and a free sample copy of "Grokking the SQL Interview."

Opinions

The author suggests that the first solution is straightforward, implying it is suitable for those with basic SQL knowledge.
The use of the RANK() window function in the second solution is presented as a more sophisticated method, indicating the author's preference for advanced SQL techniques.
By providing code examples and links to further learning resources, the author conveys a commitment to helping readers improve their SQL skills.
The mention of a discount code for Analyst Builder implies an endorsement of this platform as a valuable tool for SQL learning and development.
The recommendation of "Grokking the SQL Interview" suggests that the author believes in the importance of practical interview preparation for aspiring data analysts and database professionals.

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

SQL Interview Question (Medium): Most Profitable Companies

Solving Medium SQL Practice Question

Question

Solution

Code

Conclusion

Before You Leave