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;
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?
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;
@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
I think, window functions are not supported in mysql version 5.7., but in version 8. Hackerrank is using that. Check with this query:- SELECT VERSION(); I got the same problem. Don't know how to proceed. How come he got it running ><
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❤
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
because if you read explanation carefully you will notice that hackers who have obtained full marks are written Difficulty table contains full score of Difficulty and submissons score column contains score obtained by hacker so we need hackers that obtained full score only
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;
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;