I came across a Salesforce SQL Interview question from StrataScratch, at first I thought it was a pretty straightforward, but this question taught me a few things, and I want to share them with you.
Here is the preview of the dataset:
Basically, for each carmaker (ie Ford) we want to find out if they produced more (or less) product names (Mustang, Focus, Explorer, etc.) the next year.
My first approach is to select company_name (ie Ford), in year 2020 using the following SQL query:
I thought I would half way done, once I have the 2019 query and join the two tables. I realized I have to figure out how to subtract the 2019 count column from the 2020 count column.
Solution:
To solve this problem, I have to give an alias to the count columns (C1 and C2). We then can easily use them to subtract C1 column from C2 column on the first SELECT statement.
Conclusion:
When I first started learning about SQL, I thought the sole purpose of using an alias in a SQL query is similar to using alias when importing modules in Python (import pandas as pd): to avoid having to type “pandas” everytime we want to call it. Though this is true, aliases are used so we can use them in a higher level query.
My name is Johan Halim, I am a SharpestMinds Data Science mentee. I have experience in Accounting, and I am currently looking to transition into a Data Analyst role. If you are recruiting for a Data Analyst role, please reach out to me on my LinkedIn.
Thank you.
