วีดีโอ

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;

Hi, In Weather Observation Station 8 I did something like that: SELECT DISTINCT City FROM Station WHERE LOWER(LEFT(City, 1)) IN ('a', 'e', 'i', 'o', 'u') AND LOWER(RIGHT(City, 1)) IN ('a', 'e', 'i', 'o', 'u') ; Maybe it would be helpfull for somebody

while joining with challenges table, why didn't we joined them based on both columns challenge_id and hacker_id ?

Hey! This video has been such a life saver. Thank you!! Also, for the last challenge i.e. 06:39:06 15 days of learning SQL, you can skip the @count_hacker variable by just doing the count in the @consistent_hacker variable while joining it with the max_hacker CTE. Like this: declare @consistent_hackers table ( submission_date date, hacker_id int ); declare @sub_date date; declare @remaining_date date; insert into @consistent_hackers select submission_date, hacker_id from submissions where submission_date = '2016-03-01'; set @sub_date = '2016-03-01'; set @remaining_date = '2016-03-01'; while @sub_date <= '2016-03-15' begin set @sub_date = dateadd(day, 1, @sub_date); insert into @consistent_hackers select s.submission_date, s.hacker_id from submissions s join @consistent_hackers ch on ch.hacker_id = s.hacker_id and ch.submission_date = @remaining_date where s.submission_date = @sub_date; set @remaining_date = dateadd(day, 1, @remaining_date); end; with max_hacker as ( select row_number() over (partition by submission_date order by count(s.hacker_id) desc, s.hacker_id asc) as rn, submission_date, s.hacker_id, name from submissions s join hackers h on s.hacker_id = h.hacker_id group by submission_date, s.hacker_id, name ) select mh.submission_date, count(distinct ch.hacker_id), mh.hacker_id, name from @consistent_hackers ch join max_hacker mh on ch.submission_date = mh.submission_date and rn = 1 group by mh.submission_date, mh.hacker_id, name;

The question says "If more than one student created the same number of challenges AND the count is less than the maximum number of challenges created". Can you explain why did you put keyword ""then" instead of AND between these two statement?

This is great man, thank you a lot for taking your time for this and sharing it with us

You Deserve A Sub bro ❤

This was great. Step by step explanation was smooth.

Really helpful! Thanks!

can you update the test as there is some change in the requirement of the test as now we cant group by sender and have to do it in time frame

Good work mate! loved the way you approached the problem, and how you broke it down into bits and worked your way towards the answer required.

My query is correct.. output is no response on stdout...i also face same issue in intermediate test..it is bug?

can we use WITH in MySQL ????

I was able to crack it this way: WITH SS AS (SELECT CO.contest_id, SUM(SS.total_submissions) AS TOT_SUBS, SUM(SS.total_accepted_submissions) AS TOT_A_S FROM Colleges AS CO JOIN Challenges AS CH ON CO.College_id = CH.College_id JOIN Submission_Stats AS SS ON CH.Challenge_id = SS.Challenge_id GROUP BY CO.contest_id ), VS AS (SELECT CO.contest_id, SUM(VS.total_views) AS TOT_VS, SUM(VS.total_unique_views) AS TOT_UV FROM Colleges AS CO JOIN Challenges AS CH ON CO.College_id = CH.College_id JOIN View_Stats AS VS ON CH.Challenge_id = VS.Challenge_id GROUP BY CO.contest_id ) SELECT C.contest_id, C.hacker_id, C.name, TOT_SUBS, TOT_A_S, TOT_VS,TOT_UV FROM Contests AS C JOIN SS ON C.Contest_id = SS.Contest_id JOIN VS ON C.Contest_id = VS.Contest_id

Voice is not clear. Else content is good.

thank you for the great explanation, I learnt a lot!

Thanks so much

Hi I want one solution for this question from hacerank . Advertising System Net Seller Report 4 As part of Hacker Ad's advertising system analytics, a team needs a list of campaigns that have a sequence of events with more sell transactions than buy transactions. Transactions are considered in the order they appear in the table. A sequence is a number of consecutive transactions with the same campaign_id. 6 6789 10 The result should be in the following format: campaign, netsells count, netsells total. campaign is the campaign name. A campaign name may have more than one campaign id. Test netsells count is the total number of reported event sequences for a campaign name. netsells, total is a total of all the sell transactions in event sequences. Run Results should be sorted descending by netsells total

clean as a whistle

instead of excluding the dogecoin can we just use select DISTINCT algorthm?

Sorry a bit confused about the raw_input below (if). The variable below is first_name and last_name and assign the values to print_full_name. However, it needs to define two new variables (first and last) to output the final result. Is my understanding correct?

Do you have solution for Advertising Sytem Net Seller Report? And Transaction Monitoring System Report