Three Tricky Analytics Interview Questions with Andrew

Поделиться
HTML-код
  • Опубликовано: 22 дек 2024

Комментарии • 96

  • @Vintagetube310
    @Vintagetube310 3 года назад +55

    Man this was so dope. In a data analytics student and I’m so happy that a lot of this resonated with me as I’m still learning. Everyone who has or is studying coding and programming, understands that it’s a steep learning curve and a lot of times you might find yourself in a rut thinking you’re never going to get the material. But when you observe mock interviews and the info they are covering definitely resonates with you, it reinforces your progress knowing you’re heading in the right direction. Everyone who is learning how to program, KEE GOING! thanks fellas’

  • @Auslims
    @Auslims 3 года назад +25

    I really like andrew’s thought process. It gives me so much insights to prepare for the coming interviews!

  • @KisaanTuber
    @KisaanTuber 2 года назад +15

    Thanks Jay for this awesome session. I have few sugestions regarding Part 1. We should emphasize on using RANK()/DENSE_RANK() function instead of ORDER BY and LIMIT. RANK()/DENSE_RANK() will take care of scenarios when multiple employees have same salary. Let me know your thoughts.

  • @omerbraude5694
    @omerbraude5694 3 года назад +2

    In the first problem, how can he order by e.salary without having it in the group by clause? Wouldn't it throw an error? I would imagine it must be 'order by avg(salary)' or 'max(salary)' which would yield the same result. Alternatively, you could add the salary column to the group by clause. Am I right?

  • @shao4686
    @shao4686 3 года назад +6

    I really liked the thought process sharing. Thanks.

  • @shrutisinghal8556
    @shrutisinghal8556 3 года назад +4

    In second question, Andrew assumed that Employees who have not finished any project should be considered while Jay assumed that any employee who has even one unfinished project must be considered. None of them made clear their assumption and therefore both had such different queries. I like how Andrew thought! Good interview session.

  • @D3Jia
    @D3Jia 2 года назад +2

    andrew is such articulate and intelligent dude.

  • @bricelearnstuff
    @bricelearnstuff 3 года назад +2

    For question 2, does the following query work as well?
    select sum(salary)
    from(
    select distinct id, salary
    from employees e
    inner join projects p on e.id=p.employee_id
    where end_dt is null or end_dt>current_date()) as cte

    • @Han-ve8uh
      @Han-ve8uh 3 года назад +2

      Strange to name this cte at the end when no WITH is used. You mean subquery? This query will include employees who have also finished some projects. The question wanted people who have started 1 or many projects but never finished a single one

  • @nickayala7263
    @nickayala7263 2 года назад +3

    First Question:
    I think it would be better to COUNT(DISTINCT Project_id) and have a WHERE End_dt IS NOT NULL.
    We don't know if this is a unique column or not. Could a project have been closed and then re-opened? Would that have the same ID or a new ID?
    Counting just end dates could lead us to incorrect counts.

  • @ShowTiimee123
    @ShowTiimee123 2 года назад +1

    Happy to see that what I'm learning in my classes right now is actually applicable to the real world!

  • @iqjayfeng
    @iqjayfeng  3 года назад +8

    Thanks for watching! If you're interested in learning more about SQL, check out our SQL course. The course is designed to help you learn everything you need to know about working with data, from basic concepts to more advanced techniques.
    www.interviewquery.com/learning-paths/sql

    • @mypure
      @mypure 3 года назад

      Thanks a ton. I will do honest attempt to solve the problems and learn.

  • @Mario-ox5dm
    @Mario-ox5dm 4 года назад +6

    This mock interview definitely was helpful! Thank you Jay & Andrew!

    • @venkatnetha8382
      @venkatnetha8382 4 года назад

      For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit:
      payhip.com/b/ndY6
      You can download the sample pages so as to see the quality of the content.

  • @arjunekbote8979
    @arjunekbote8979 3 года назад +6

    Steps:
    1. Gets project from boss to find employees who haven't finished project
    2. Remembers that he hasn't completed any project till now
    3. Does not complete project

    • @khiemgluong
      @khiemgluong 3 года назад

      Were not creating a table of employees we need to "furlough" or anything

  • @GalaxyZhou1006
    @GalaxyZhou1006 4 года назад +4

    for part 1 the solution does not seem to include case for example that the sixth lowest paid employee has the same salary with the fifth one, do you include the sixth person or not. in that case you could apply rank or dense_rank?

    • @sunny73555
      @sunny73555 3 года назад

      You should use dense rank

  • @黃冠豪-y2w
    @黃冠豪-y2w 3 года назад +12

    Regarding the 2nd question:
    If an employee has 3 projects on hand and none of them are finished, wouldn't her salary be summed up 3 times according to the query in this video?

    • @rakeshpillai2173
      @rakeshpillai2173 3 года назад

      Yes. Also not sure how this works in MySQL, but in both postgres and sql server, grouping by id will give you separate rows of salaries and won't add them up.

  • @shashankrai10
    @shashankrai10 3 года назад +16

    For the second question can we do this:
    SELECT sum(Sum_Of_Salary) as Total_sum
    FROM (
    select sum(e.salary) as Sum_Of_Salary, Count(end_dt) as num_projects
    FROM employees as e
    RIGHT JOIN projects as p
    ON e.id = p.employee_id
    GROUP BY(A.id)
    )
    WHERE num_projects = 0
    The right join takes only the values from projects that have an id in employees. So that takes care of the third definition - employees who have no project started are not counted. Among those employees who have no end_dt, their count of end_dt should appear as 0. And then outside of brackets, we simply sum the salaries where number of projects are 0.

    • @Hotobu
      @Hotobu 2 года назад

      ...Please don't use right joins, there's never a good reason to use a right join.

    • @5lanediver
      @5lanediver Год назад

      good call -- they never addressed that third case

    • @5lanediver
      @5lanediver Год назад

      @@Hotobu then just rewrite it as a left join -

  • @dbc3745
    @dbc3745 Год назад

    Thank you for this. Please give us more glimpses of real world data analytics/scientist work and how they solve problems through data

  • @leos5246
    @leos5246 3 года назад +2

    During the second task with this complex construction, I literally thought "Why it's so complex? Why you cannot use just where clause" and it's surprising that Jay is added this solution after :).
    The only one thing about performance. If the tables are huge you don't want to use "NOT IN" construction, it's preferable to use NOT EXISTS instead, much more effective but mostly the same code.

  • @antonisdaniil2857
    @antonisdaniil2857 3 года назад +1

    I believe this is better so we if we have employee with same salary we get all of them
    with cte as
    (select e.id, Dense_RANK() over(order by salary) as r
    from projects t
    join employees e
    on e.id=t.employee_id
    group by t.employee_id
    having count(end_dt)>10)
    select * from cte where r=5

    • @averw3047
      @averw3047 2 года назад

      My solution is similar to yours, except "Where r

  • @cinyenne
    @cinyenne 4 года назад +1

    This sort of discussion helps us to understand your thought process better

    • @AndrewMoMoney
      @AndrewMoMoney 4 года назад

      Cheers, Cy :)

    • @venkatnetha8382
      @venkatnetha8382 4 года назад

      For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit:
      payhip.com/b/ndY6
      You can download the sample pages so as to see the quality of the content.

  • @dontstopn0w
    @dontstopn0w 3 года назад +4

    Really appreciate this video. Extremely helpful!

  • @quishzhu
    @quishzhu Год назад

    i am sooo lucky to pump into this video before the data science tech interview tomorrow!

  • @Rohit_Ankita
    @Rohit_Ankita 2 года назад

    For part 1: can we do count(distinct project_id) >= 10 in having clause and where clause should have end date is not null. Thoughts.

  • @mimansamaheshwari4664
    @mimansamaheshwari4664 2 года назад

    In the first question if employee table why did we drop cases with null end_date?

  • @pratik903
    @pratik903 6 месяцев назад

    Just out if curiosity, a weird question related to 3rd scenerio. As it is mentioned in the video that person who completed 10 projects in a week shouldn't be given more raise than a person who completes 5 project in a month.
    Ideally person completing 10 projects in leas time is more efficient and should be given more raise?
    Please suggest where I am wrong

  • @brittosabu07
    @brittosabu07 2 года назад +1

    Can we use this for second question ?
    SELECT SUM(salary)
    FROM Employees
    WHERE employee_id IN
    (SELECT employee_id
    FROM Projects
    GROUP BY 1
    HAVING MAX(END_DATE) IS NULL)

    • @mattcarter4749
      @mattcarter4749 2 года назад

      I believe the only error is using "employee_id" in your where statement. Since you have "From Employees", you would need "Where id IN".

    • @mattcarter4749
      @mattcarter4749 2 года назад +2

      This is the query I wrote to solve, very similar to yours except I used "having count(end_dt) = 0" instead of max()
      select sum(salary)
      from Employees
      where id in (select employee_id
      from Projects
      group by employee_id
      having count(end_dt) = 0
      )

  • @OkechukwuOfili
    @OkechukwuOfili 3 года назад +1

    What if different projects finished on the same date:
    [ count (p.end_dt) >= 10 ]
    won't your count then be off?
    Would it not be better to do a
    [ count (distinct p.projects_id) >= 10 ] ?

    • @hariniprabakaran5434
      @hariniprabakaran5434 3 года назад

      Yes, also We need to handle the end date case

    • @pavelbakunovitch
      @pavelbakunovitch 3 года назад +1

      No, it won't. count(p.end_dt) is counting every not null p.end_dt even they are the same.

  • @danielxing1034
    @danielxing1034 2 года назад +1

    Fantastic mock! Very thorough thinking paths! But wonder if we really need to go through this entire path during the interview? Because the number of problems solved is also an important metric.

  • @GH-hg6vs
    @GH-hg6vs 2 года назад

    I was having this question - For the second part, why not just selecting the employees that don't have a valid "end_date". In this case, no matter how many project he/she has been assigned to, he/she didn't finish any of them. Then I saw Jay's solution. Perfect.

  • @arindampatra6283
    @arindampatra6283 4 года назад +5

    Not as tricky as I expected , but I enjoyed how the guy thinks

  • @shifaali1923
    @shifaali1923 3 года назад

    how about?
    select r.id from (select e.id as id,,e.salary,p.project_id,rank() over(Partition by e.id,p.projectid order by e.salary asc) as rank from employ e inner join project p on e.id=p.project_id where p.end_dt is not null)r
    where r.rank

  • @brothermalcolm
    @brothermalcolm 3 года назад +1

    You don’t need a cte if you place the case statement inside the having statement sum(case when...)

    • @YT-yt-yt-3
      @YT-yt-yt-3 3 года назад

      infact do we need case statement? why not this - having count(e.id) = count(p.end_dt is null)

  • @santiagocanon5281
    @santiagocanon5281 4 года назад +1

    I have an interview with a large tech company on Monday for a DS position. While I am confident in my SQL skills, it is good to see and understand the soft skills that should be leveraged during the interview. I did want to ask is my position also requires R which I am proficient in as well, but do you have any insight into R interview questions?

    • @venkatnetha8382
      @venkatnetha8382 4 года назад

      For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit:
      payhip.com/b/ndY6
      You can download the sample pages so as to see the quality of the content.

  • @AbhishekSharma.0311
    @AbhishekSharma.0311 2 года назад +2

    Hey Jay.
    I think the query by Andrew has got an issue. He has used Group By e.id and then taken sum(salary). This would output salary for each employee and NOT a SUM of All employees salary.
    Correct me, if otherwise
    Thanx for the good work
    Cheers

  • @abhijitbhaduriya117
    @abhijitbhaduriya117 3 года назад

    These are super helpful. Thanks for this, Jay.

  • @ananyadutta7852
    @ananyadutta7852 3 года назад

    For the first ques what if two projects have the same end date we would end up counting it as 1 project which could be actually multiple projects. I propose to filer as end date not null and count project id. Am i missing something here?

    • @Amber-ws5hy
      @Amber-ws5hy 2 года назад

      Andrew used count() instead of count(distinct), so it shouldn't matter I think

  • @nppreaches
    @nppreaches 3 года назад

    which query of the two will perform better assuming you have a big number in terms of employees and projects?

  • @rick2591
    @rick2591 2 года назад +1

    The join would break the query causing salaries double triple, etc.

  • @joehatch3289
    @joehatch3289 3 года назад

    Can I ask, for part 1 what ensures that there is no duplicates? If the lowest salary emplyee was on multiple projects?

    • @Han-ve8uh
      @Han-ve8uh 3 года назад

      group by e.id removes possibility of any duplicates in employee/salary that can cause some of lowest 5 salaries to come from same employee

  • @max007sh
    @max007sh 3 года назад +1

    Was wondering why you used aggregating (and having clause) for the first question. Wouldn't be simpler to use a where clause?

    • @nanfengbb
      @nanfengbb 3 года назад

      Nope. "WHERE COUNT(p.*)>=10 " would show an syntax error. It has to be GROUP BY followed by HAVING.

    • @max007sh
      @max007sh 3 года назад

      @@nanfengbb @B Karcher select e.id from employee e inner join (select employee_id eid, count(end_dt) c from project group by employee_id) temp on e.id=temp.eid
      where temp.eid>10
      order by ...

    • @max007sh
      @max007sh 3 года назад

      but now that I think about it, having seems simpler 😅

  • @hardikojha220
    @hardikojha220 4 года назад +1

    Very interesting thought process

  • @itsadamjosh
    @itsadamjosh 2 года назад

    Whats the code for amc and gme to go to the moon

  • @yyyj926
    @yyyj926 4 года назад

    Thank you, Andrew and Jay.

    • @venkatnetha8382
      @venkatnetha8382 4 года назад

      For a 1200 long pages of question bank on real world scenarios to make you think like a data scientist. please visit:
      payhip.com/b/ndY6
      You can download the sample pages so as to see the quality of the content.

  • @TalhaFurqan-l4n
    @TalhaFurqan-l4n 4 месяца назад +1

    andrew pretty much messed up the entire second query. There was no need to over-complicate it.
    select sum(salary)
    from employees
    where id IN (
    select distinct employeed_id
    from projects
    where end_dt is null
    )

  • @5lanediver
    @5lanediver Год назад

    for problem 2 you never tacked case 3, when there is no projectid associated with an employeeid...

  • @tilibanner5920
    @tilibanner5920 3 года назад

    Question 2: sum(e.salary) and then group by employee_id? That's not producing what the question is asking for.

  • @MattIn3rtia
    @MattIn3rtia 7 месяцев назад

    Not sure about that first one. The end date might be in the future so the projects unfinished. The Qs a bit ambigous. I'd have gone with the following:
    SELECT
    id
    FROM employees
    WHERE id IN
    (SELECT DISTINCT employee_id
    FROM projects
    WHERE COUNT(DISTINCT project_id_) > =10)
    ORDER BY salary ASC
    LIMIT 5

  • @iremozen7172
    @iremozen7172 2 года назад

    great video!! thank you.

  • @gabiscarabel
    @gabiscarabel Год назад

    Excellent video!

  • @akshitsinghal8590
    @akshitsinghal8590 2 года назад

    Really productive session

  • @tz1938
    @tz1938 3 года назад

    This is awesomeeee!

  • @khiemgluong
    @khiemgluong 3 года назад

    learning SQL and this is very helpful

  • @AnujJaiswal91
    @AnujJaiswal91 3 года назад

    Great process

  • @SamairaJain11
    @SamairaJain11 2 года назад

    In the first question he is literally passing the time🤣

  • @LeoLeung.93
    @LeoLeung.93 3 месяца назад

    They both did wrong on the second question, they agreed on the definition that employees without projects on hand are not considered but Jay's answer included those employees.

  • @brothermalcolm
    @brothermalcolm 3 года назад

    Employees who’ve done at least 10 project, then select bottom 5 by salary

  • @Peekabostreet
    @Peekabostreet 4 года назад

    They miscommunicated a couple times but very helpful

  • @flyingcucumber
    @flyingcucumber 2 года назад

    Got a interview tomorrow. I'm f*cked

  • @ravitanwar9537
    @ravitanwar9537 4 года назад +1

    oo princes of data science :)

    • @AndrewMoMoney
      @AndrewMoMoney 4 года назад +1

      Wahahaha our royalty status is highly in question

    • @ravitanwar9537
      @ravitanwar9537 4 года назад

      @@AndrewMoMoney 😂
      I can vouch for that

  • @annxiao7721
    @annxiao7721 Год назад +1

    I can’t talk and think at the same time. I hate it that straightforward SQL case can appear so much more difficult just because u have to explain to the interviewers while u r working on it.. it’s just anti humanity…. 😢

  • @masheTyMasha
    @masheTyMasha 2 года назад

    Cool

  • @comatosetorpor3602
    @comatosetorpor3602 3 года назад +1

    i dont think they r tricky at all. for any one who knows and uses sql in industry, they r basic.

  • @pinkyellow1able
    @pinkyellow1able 4 года назад

    can you please do benford's law with the 2020 election numbers? would really get your channel some views and open up the truth to alot of people

  • @jonathancauchi6457
    @jonathancauchi6457 3 года назад

    Cool video, but not tricky at all.

  • @drkenny7928
    @drkenny7928 2 года назад

    Dude made this overly complicated

  • @1danielsz24
    @1danielsz24 2 года назад

    I am completely started in sql but I imagined a different way and I would like to know if I would return the output correctly
    SELECT
    employee_id,
    MIN(salary),
    COUNT(project_id) AS project_number
    FROM
    employee e
    INNER JOIN
    projects p
    ON
    e.id = p.employee_id
    WHERE project_number >= 10
    GROUP BY employee_id
    ORDER BY salary
    LIMIT = 5

  • @tsume184tg
    @tsume184tg 2 года назад

    SELECT e.id, e.salary, p.employee_id, p.project_id, COUNT(project_id) AS numberofprojects
    FROM projects AS p
    INNER JOIN employees AS e
    ON e.id = p.employee_id
    WHERE numberofprojects >= 10
    GROUP BY e.id, e.salary, p.employee_id, p.project_id
    ORDER BY e.salary DESC LIMIT 5;
    I think that should work for the first scenario. I am still learning, on day 2. Hopefully i got that correct. If i can get some feedback that would be great.