SQL Data Science Interview Question Walkthrough | SQL Sundays #6

hello time for another sql sunday all right so today's question is from microsoft it's called employee project budgets find the top five most expensive projects by the amount of budget allocated to each employee on the project exclude projects with zero employees assume each employee works on only one project the output should be the project title and the budget that's allocated to each employee that is a budget to employee ratio and display the top five projects with the highest budget to employee ratio first that was a mouthful um cool so let us look at the table it's ms projects as well as ms amp projects and ms employee projects so here we have id title and budget and msm projects we have mpid employee id and the project id cool now let us look at the expected output it is let's see okay project budget to employee ratio what assumptions do we have i think that the assumptions actually listed out here are pretty much everything that i thought about exclude projects with zero employees find the top five most expensive projects and assume that each employee works only on one project okay and one project can have multiple employees that makes sense okay oh there is one thing i noticed though the budget to employee ratio so i noticed that these are whole numbers so we might be doing integer division here um yeah so i guess like we're doing inter integer division here all right let's get started so we're gonna do here first off is let's see so the what we want is project and budget to amp ratio which is equivalent to budget divided by counts of the employees okay so first thing we're going to do here is group by oh oops no we're going to join first so we're doing an inner join um because we're excluding projects with zero employees so you want to do inner join between ms projects and ms amp projects and then after that we're going to be doing a group buy so we want to group by the believe it is title yeah so you want to group by the title um and then we want to count so we want to do the budget divided by count of employee and then finally we want to order by this budget divided by count employee and then we want to limit to five cool let us write the query so from ms projects let's call that p inner join msm e on p dot uh i d is equal to e dot i think it's project id yep okay and then we're gonna group by title so i should write the select statement first so select title as project and this one is budget divided by what are we dividing by count of the employee id so count of amp id as budget and ratio so remember how i was saying my assumptions that i wanted to i noticed that it was whole numbers so i think instead of doing like in this is integer division as opposed to like sometimes we would like say multiply by 1. 00 so that it would actually be a fraction afterwards so in this case i believe it's going to be an integer division um let's see oh and then we're going to write the group by so we're grouping by one as well as budget and

we are going to order by two more that by descending and then limit five let us see if that is correct looks right and join okay let's check and that looks correct all right let us look at their solution here let's see from okay join group by title budget order by budget and ratio descending limit five and select title yep this looks pretty much the same solution yep all right looks good so is there anything else that we can think of that could make more business sense to do let me know in the comments below i also of course always want to know your solution for how you approach this question um and i think that's about it yeah that is about it for this week's sequel sunday um oh yeah as always if you haven't checked out strata scratch yet uh that's the platform that i'm using now saves me a lot of time so do check that out if you're interested and i'll see you guys in the next video