LinkedIn SQL Interview Question for Data Scientists and Data Analysts (StrataScratch 10304)

[@samirpatil551]

Hey, bro thank you so much for making videos for us. Please don't be disappointed with video views you will grow in the future

[@frederikmuller]

Thanks, I'll just try to become more consistent again and hopefully see the channel grow. Thanks for the support!

[@GuruprakashAcademy]

Thanks Bro. A nice explanation. I have a question, Is it possible to get a entry level data analyst position with very good skill in SQL.

[@frederikmuller]

yes, definitely. it’s the main skill you need.

[@GuruprakashAcademy]

@@frederikmuller Thank you

[@hariprasath9997]

Hi bro ....can y plz tell me how to optimise this query ....in mysql

[@kartiikss8515]

Lovely explanation. I worked this problem out too. Just wanted to know if it is important to mention which join operation we're using. For ex. In the sub_query calculating SUM(salary) for every project_id, I used an INNER JOIN whereas you used just a JOIN function. For the outer query, I tried with just a JOIN function like you did and it worked. But, I'm kind of stuck with what JOIN operation is exactly happening when you say just a JOIN instead of which type of JOIN. Also, what is the query performance difference between 1. ON linkedin_projects.id = yearly_salary.project_id AND CEILING((end_date - start_date)*yearly_salary_sum/365::float) > budget AND 2. ON linkedin_projects.id = yearly_salary.project_id WHERE CEILING((end_date - start_date)*yearly_salary_sum/365::float) > budget Thank you so much for your help!

[@frederikmuller]

INNER JOIN refers to the same as JOIN. JOIN is just a short form of writing INNER JOIN (the default). It's similar to omitting the sort order when using ORDER BY. The default is ASC and that's what it's gonna assume. There are a lot of things that go into query performance including column indexing and table structure. In theory, including a filter in the ON clause instead of the WHERE should filter the data earlier (as part of the join) and increase performance. This is due to the order of execution in SQL that evaluates JOINs before the WHERE clause.

[@kartiikss8515]

@@frederikmuller Got it. Thank you so much Fred! Love your content!

[@hariprasath9997]

Instaed of :: float , what we can use in mysql server ?

[@frederikmuller]

you could just multiply by 1.0 to convert it to float

[@hariprasath9997]

@@frederikmuller will u plz send me the code ..?

[@hariprasath9997]

@@frederikmuller plz repl me sir ........will u plz send me the sql query for this ? Plz

[@ayeoh47]

ill try to get back to this if you still need it lmk

[@HenggaoCai]

Salary or celery?

[@frederikmuller]

hehe