but why join so many tables, why not just use Employee table and company table. rest are useless, ami right?

WITH tbl AS (SELECT occupation, NAME, Row_number() OVER ( partition BY occupation ORDER BY NAME) idx FROM occupations), all_idx AS (SELECT DISTINCT idx FROM tbl) SELECT doctors.NAME, professor.NAME, singer.NAME, actor.NAME FROM all_idx LEFT JOIN tbl AS doctors ON all_idx.idx = doctors.idx AND doctors.occupation = 'Doctor' LEFT JOIN tbl AS professor ON all_idx.idx = professor.idx AND professor.occupation = 'Professor' LEFT JOIN tbl AS singer ON all_idx.idx = singer.idx AND singer.occupation = 'Singer' LEFT JOIN tbl AS actor ON all_idx.idx = actor.idx AND actor.occupation = 'Actor' ORDER BY all_idx.idx

SELECT MAX(CASE WHEN occupation = 'Doctor' THEN name END) AS Doctor, MAX(CASE WHEN occupation = 'Professor' THEN name END) AS Professor, MAX(CASE WHEN occupation = 'Singer' THEN name END) AS Singer, MAX(CASE WHEN occupation = 'Actor' THEN name END) AS Actor FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_num FROM occupations ) AS ranked_occupations GROUP BY row_num ORDER BY row_num;

I have been struggling to understand the logic of the question and got exhusted by trying to understand from diff resources , this video helped a lot . thanks a lottttt , you explain it very clearly .

I've created my own tables and insert data to solve this problem, here's my solution (it can not fit the column name or the table name, you can easily change them) I use scalar subqueries to solve, SQL SERVER SELECT ALGORITHMS, Q1 = ( SELECT SUM(VOLUMN) FROM TRANSACTIONS B WHERE (CAST(DT AS DATETIME) BETWEEN '2023-01-01' AND '2023-03-31') AND B.COIN_CODE = A.CODE GROUP BY COIN_CODE ), Q2 = ( SELECT SUM(VOLUMN) FROM TRANSACTIONS B WHERE (CAST(DT AS DATETIME) BETWEEN '2023-04-01' AND '2023-06-30') AND B.COIN_CODE = A.CODE GROUP BY COIN_CODE ), Q3 = ( SELECT SUM(VOLUMN) FROM TRANSACTIONS B WHERE (CAST(DT AS DATETIME) BETWEEN '2023-07-01' AND '2023-09-30') AND B.COIN_CODE = A.CODE GROUP BY COIN_CODE ), Q4 = ( SELECT SUM(VOLUMN) FROM TRANSACTIONS B WHERE (CAST(DT AS DATETIME) BETWEEN '2023-10-01' AND '2023-12-31') AND B.COIN_CODE = A.CODE GROUP BY COIN_CODE ) FROM COINS A ORDER BY ALGORITHMS;

I think for more robust query we should concat this (IF(COUNT(Occupation)>1, 's', '')) instaed of just 's' in occupation

Why the hell mistakes in every sum like beginner 😑😑😑😑 Half time is wasted like that

You've already known that PROFESSOR table has the most values, so you select from PROFESSOR. On the other hand, in case of not knowing which table has the most values. How do you solve this ? I suppose using full outer join, it returns correct answer but it doesn't return correct format (hackerrank doesn't approve my answer) here's my output: Aamina Ashley Christeen Eve Julia Belvet Jane Jennifer Priya Britney Jenny Ketty NULL Maria NULL NULL NULL Meera NULL NULL NULL Naomi NULL NULL NULL Priyanka NULL NULL NULL NULL Kristeen NULL NULL NULL NULL Samantha

Here is the simplest logic i applied and that worked - with cte as ( select month(record_date) as monthd,record_date, data_type as type, data_value as val, ntile(3) over (order by data_type desc) as rankv from temperature_records ), mintemp as ( select monthd, min(val) as minval from cte where rankv=1 group by monthd ), maxtemp as ( select monthd, max(val) as maxval from cte where rankv=2 group by monthd ), avgtemp as ( select monthd, (avg(val)) as avgval from cte where rankv=3 group by monthd ) select (mintemp.monthd), round(maxtemp.maxval),round(mintemp.minval),round(avgtemp.avgval) from mintemp left join maxtemp on mintemp.monthd = maxtemp.monthd left join avgtemp on mintemp.monthd = avgtemp.monthd

the final solution with t1 as ( select sender, dt, amount, unix_timestamp(dt) - unix_timestamp( coalesce( lag(dt) over ( partition by sender order by dt ), dt ) ) as diff_second from sender_dt_amount ), with t2 as ( select sender, dt, amount, count(if(diff_second / 60 / 60 < 1, 1, null)) over ( partition by sender order by dt rows between unbounded preceding and current row ) as session from t1 ) select sender, min(dt) as sequence_start, max(dt) as sequence_end, count(1) as transactions_count, sum(amount) as transactions_sum from t2 group by sender, session having count(1) > 1 and sum(amount) >= 150 order by sender, sequence_start, sequence_end

Good solution. The solution I used was a bit simpler i think. SELECT c.company_code, c.founder, (SELECT COUNT (DISTINCT lead_manager_code) FROM Lead_Manager l WHERE l.company_code = c.company_code), (SELECT COUNT (DISTINCT senior_manager_code) FROM Senior_Manager s WHERE s.company_code = c.company_code), (SELECT COUNT (DISTINCT manager_code) FROM Manager m WHERE m.company_code = c.company_code), (SELECT COUNT (DISTINCT employee_code) FROM Employee e WHERE e.company_code = c.company_code) FROM Company c ORDER BY c.company_code ASC;

This is a late comment but you said this is not ideal since LEFT JOIN will need the table with the most rows at the left most position. So can't you use a FULL JOIN instead?

Nope

WITH Considered_transactions AS ( SELECT t1.sender, t1.dt, (SELECT MAX(dt) FROM transactions WHERE dt<t1.dt AND sender=t1.sender) AS dt_end FROM transactions t1 WHERE DATEDIFF(minute, (SELECT MAX(dt) FROM transactions WHERE dt<t1.dt AND sender=t1.sender), dt)<=60 ), Transactions_start_end AS ( SELECT sender, dt FROM Considered_transactions UNION SELECT sender, dt_end FROM Considered_transactions) SELECT t2.sender, MIN(t2.dt) AS Sequence_start, MAX(t2.dt) AS Sequence_end, COUNT(*) AS transactions_count, SUM(t2.amount) AS transactions_sum FROM transactions t2 JOIN Transactions_start_end t3 ON t2.sender=t3.sender AND t2.dt=t3.dt GROUP BY t2.sender HAVING SUM(t2.amount)>=150;

thanks for the explanation and really liked your solving approach.

thank you so much for your time and effort wish you all the success in your life as this is very kind of you <3

thanks you

If you had more clear audio It would have been a amazing experience learning sql in such a rich and conceptual way

U re not the bro that i deserve but the bro that I need ! !

@thecodingmentor7701 Hi, the link to second udemy course in description for PSD is currently invalid again

Thank you for the explanation, I was confused and this helped a lot.

thanks a lot sir for clearly explaining a complex problem in simple way

@3:47:07 HEY CAN YOU PLZZ get me out of this riddle WITH CTE AS(SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER, ROW_NUMBER() OVER(PARTITION BY P.AGE,p.power ORDER BY P.AGE,P.POWER W.COINS_NEESED ASC) AS RK FROM WANDS AS W INNER JOIN WANDS_PROPERTY AS P ON W.CODE=P.CODE WHERE P.IS_EVIL=0) SELECT * FROM CTE WHERE RK=1 ORDER BY POWER DESC , AGE what is the problem in this code and why it is showing error

why can't we join difficult and submission table on the score column

This is brilliant..

To be honest i actually felt why this video is 1 hour and i initially not interested and thinking like skip this one problem in hacker rank but the way cross checked with excell and making the queries in chunks and chunks very brilliant respect brother❤

Thanks!

The Correct Answer for this Problem is WITH hours_worked AS ( SELECT emp_id, CASE WHEN MINUTE(TIMESTAMP) >= MINUTE(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) THEN HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) ELSE HOUR(TIMESTAMP) - HOUR(LAG(TIMESTAMP) OVER(PARTITION BY DATE(TIMESTAMP), emp_id ORDER BY TIMESTAMP)) - 1 END AS hours_worked FROM attendance WHERE DAYOFWEEK(TIMESTAMP) IN (1, 7) -- 1 for Sunday, 7 for Saturday ) SELECT emp_id, SUM(hours_worked) AS hours_worked FROM hours_worked GROUP BY emp_id ORDER BY hours_worked DESC;

SELECT ALGORITHM, SUM(CASE WHEN quarterly =1 THEN VOL ELSE 0 END) AS TRANSACTION_Q1 , SUM(CASE WHEN quarterly =2 THEN VOL ELSE 0 END) AS TRANSACTION_Q2 , SUM(CASE WHEN quarterly =3 THEN VOL ELSE 0 END) AS TRANSACTION_Q3 , SUM(CASE WHEN quarterly =4 THEN VOL ELSE 0 END) AS TRANSACTION_Q4 FROM ( SELECT ALGORITHM AS ALGORITHM, SUM(VOLUME) VOL, datepart(quarter,dt) as quarterly FROM COINS C INNER JOIN TRANSACTIONS T ON C.code=T.coin_code WHERE DATEPART(YEAR,DT) = 2020 GROUP BY ALGORITHM,DATEPART(QUARTER,DT) ) Q GROUP BY Q.ALGORITHM ORDER BY Q.ALGORITHM

for interview question(second last one ) Why wont below query work , its giving me sum which is higher than the acrual results select c.contest_id, hacker_id, name,clg.college_id,chlng.challenge_id,total_submissions as sum1, total_accepted_submissions as sum2, total_views as sum3,total_unique_views as sum4 from Contests c join Colleges clg on c.contest_id = clg.contest_id join Challenges chlng on clg.college_id = chlng.college_id join Submission_Stats s on chlng.challenge_id = s.challenge_id join View_Stats v on s.challenge_id = v.challenge_id where c.contest_id = 845 order by c.contest_id

do you recommend i do the regular scrum certification first? I'm a senior engineer.

Could you please clarify why we having " WHERE s.score = d.score " in where condition

Could you please tell me why we having WHERE s.score = d.score

thank you!

For Binary Tree Nodes, try this instead: SELECT N, CASE WHEN P IS NULL THEN 'Root' WHEN N IN (SELECT P FROM BST) THEN 'Inner' ELSE 'Leaf' END FROM BST ORDER BY N;

nicely explained sir, Thank you🤩

Bless you

if this medium then Im screwed.

Man you had it but still struggled for small stuff 😂 but yeah great way to approach a problem

Thank you for the video !

by far the best explanation, thank you!

You can full outer join all of the tables. That way you can get all of the null values regardless of column count.

Thank you very much!

Very great work bro. Please keep uploading the videos

damn I wish I saw this earlier I took the test and got stuck on it so I failed.

very well explained

Definetelly not a very smart approach, far too complicated

Awesome video and thank you for putting the timestamp for each problem in the comments

For MySQL SELECT CASE WHEN grade >= 8 THEN name ELSE 'NULL' END AS Name, Grade, Marks FROM Students s, Grades G WHERE S.marks BETWEEN G.min_mark and G.max_mark ORDER BY grade DESC, name ASC, marks ACS;