Hackerrank Symmetric Pairs Problem | SQL | Ashutosh Kumar

Подписаться 20 тыс.

Просмотров 7 тыс.

50% 1

Sql one of the most important language asked in most of the analytics interviews, in this series i will be solving sql questions from hackerrank, hackerearth, leetcode and many other websites which are more relevant to questions asked in interviews these questions will be in all levels -
Easy
Medium
Hard
👉 Link to questions -
www.hackerrank...
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
---------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 How to Learn SQL
• How to learn sql for b...
👉 How to become a business analyst complete roadmap-
• Business Analyst Compl...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 Rank ,Dense Rank, Row Number in sql -
• RANK - DENSE RANK - RO...
👉 Cross join in sql
• CROSS JOIN SQL
👉 union join in sql
• UNION IN SQL
👉 left join in sql
• LEFT JOIN IN SQL
👉 Right join in sql
• RIGHT JOIN IN SQL
👉 Inner join in sql
• INNER JOIN IN SQL
👉 Introduction to tables and databases in sql -
• INTRO TO TABLES AND DA...
👉 Aggregate Function in sql
• AGGREGATE FUNCTION IN SQL
👉 Functions in sql-
• IMPORTANT FUNCTIONS IN...
👉 String Function in sql
• STRING FUNCTIONS IN SQL
👉 CRUD operations in sql
• CREATE- READ- UPDATE-D...
👉 Autoincrement in sql
• Auto Increment in SQL ...
👉 Primary Key in sql-
• PRIMARY KEYS IN SQL - SQL
👉 Null and Default values in sql-
• NULL AND DEFAULT VALUE...
👉 Data types in sql-
• Data types in Sql - SQL
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.co...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutosh.analytics
💻Linkedin- / ashutoszh
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for data science,
sql for data analytics,
sql practise questions,
sql practise questions and solutions,
sql tutorials for beginners,
sql problems for data engineers,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics,
sql problems easy,
sql problem medium,
sql problems hard,
sql window functions,
sql advanced questions,
rank functions in sql,
lag lead in sql,
sql interview questions and answers,
sql interview questions,
sql questions asked in interviews,
hackerrank sql solutions,
hackerearth sql solutions,
leetcode sql solution
🏷️HashTags
#sql #interviews #questions #solutions

Опубликовано:

30 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 29

@cyber_dbs 11 месяцев назад

Here's a clear example lets see with an example table, Table Funtion ------------- x1 x2 20 20 19 19 20 20 15 10 10 15 Now, see after joining table SELECT A.X, A.Y, B.X, B.Y FROM FUNCTION AS A JOIN FUNTION AS B ON A.X = B.Y AND A.Y = B.X; output: a1 a2 b1 b2 20 20 20 20 19 19 19 19 20 20 20 20 15 10 10 15 10 15 15 10 SELECT A.X, A.Y FROM FUNCTION AS A JOIN FUNTION AS B ON A.X = B.Y AND A.Y = B.X; output: a1 a2 20 20 19 19 20 20 15 10 10 15 from the above table you can see that we have to get rid of this record (19 19) since it does not have any pair unlike (20 20). thats why 'having count(A.X) > 1' is necessary Now (15 10) and (10 15) is also a pair but its not like we can choose either of them but only the record where x 1 or f.X < f.Y order by f.X;

@ragnarlodhbrok1684 11 месяцев назад

I have one doubt, in the output, how the pair (20,20) is only displayed once... isn't the output supposed to be (20,20) (20,20) (10,15) but in the output, (20,20) is present only once even without using distinct, how, please explain

@cyber_dbs 11 месяцев назад

@@ragnarlodhbrok1684 it's because of the 'group by '

@AshwinGhosalkar 3 месяца назад

Hi @cyber_dbs, we have display the record which has x1 1 or F.X

@monarch04 Год назад

hey Ashutosh, you should have explained the question a liitle bit more before jumping onto the solution, some more examples of output would have been great, to undertand what exactly is asked in the question

@AshutoshKumaryt Год назад

My bad, did you understood or not ?

@monarch04 Год назад

@@AshutoshKumaryt actually I took help of some other video to understand the question then visited this solution which was making sense after that. Problem I was facing was that I was not able to understand question

@arpanbanerjee6224 Год назад

Awesome explanation! Thanks

@cmpe793 Год назад

ı wonder this. If we have only one pair that x's is equal to y's equal, we will be obtained this as output since we are checking the same table again. But isn't that wrong? Iam just confused about that part. How can we eliminate that?

@aniketpandey4084 8 месяцев назад

count(A.X) satisfies that condition. If count is only one then it wont give that point as o/p

@vitorcastro42 3 месяца назад

Hey, can anyone explain why my MYSQL solution is wrong? with Functions_symmetric as ( select Functions.y as x2, Functions.x as y2 from Functions ), Joined_tables as ( select Functions.x, Functions.y, Functions_symmetric.x2, Functions_symmetric.y2 from Functions inner join Functions_symmetric on Functions.x = Functions_symmetric.y2 and Functions.y = Functions_symmetric.x2 ) select distinct x, y from Joined_tables where x

@prachitamta4098 2 года назад

my solution , please give me feedbCK with cte as (select x, y from xy where x

@nitinmadan4009 Год назад

Hi @prachitamta4098,slightly late on this, but nevertheless. In this problem, you are trying to filter out x,y pairs/tuples, which have xi

@Abby-ts4ul 8 месяцев назад

I'm confused , why we not use distinct to remove the duplicated rows of which X=Y, why this syntax does not work? ----select distinct A.X, A.Y from functions A join functions b on A.X = B.Y and a.y = b.x where a.x

@paritoshkumar9062 Год назад

Is the or keyword used for group by ? Like either group by common ones or group by the different ones??

@AshutoshKumaryt Год назад

Can you please elaborate

@paritoshkumar9062 Год назад

I didnt understand the "OR" part I mean "OR" is used with respect to Having clause right?

@nitinmadan4009 Год назад

@@paritoshkumar9062 here "OR" applies to having clause, i.e. either count(*) > 1 or having A.X < A.Y. Via the two conditions in the having clause, we are able to cover both the cases of keeping just out of the duplicates (X and Y are same) using (count(*) > 1 and by the other clause A.X < A.Y, we are able to filter for symmetric pairs which have their Xi strictly smaller than Yi. The having clause thus ensures we are able to set the condition mentioned in the last line of the problem statement - " List the rows such that X1 ≤ Y1". hope this makes sense now.

@erikassena Год назад

I did not undestand the count > 1 part. Why cant be used the distinct funciont?

@cyber_dbs 11 месяцев назад

look into my comment...

@shubhamgupta9323 Год назад

why are you using two tables. Could you please tell me

@nitinmadan4009 Год назад

slightly late reply on this. Nevertheless, here is my 2 cent. The problem at hand is a classical example of a Self Join, where a table is joined to itself based on some criteria. In this problem, as we want to check for symmetric pairs, the easiest way to do would be use self join where we join on the A.X = B.Y and A.Y = B.X, which has defined as the condition for the symmetric pairs. Hope this was helpful.

@shubhamgupta9323 Год назад

@@nitinmadan4009 yes thank-you

@vivekjoshi3769 10 месяцев назад

Hi Ashustosh, I had a hard time solving this question. I went through your solution but I still have some doubts. I did not undestand this part HAVING COUNT(A.X) > 1 what exactly this is doing, that I did not get. You did it to remove duplicates if I understand what you said in the video but but how does this works in removing duplicates?

@AshutoshKumaryt 9 месяцев назад

you can actually see what does "having" clause does

@vivekjoshi3769 9 месяцев назад

@@AshutoshKumaryt How bro? That was my question. Well, forget it. I discontinued hackerrank. I started doing it to get better at SQL but I realized it worked in making me feel shit about my existing SQL working knowledge. For then on I decided to leave that for technical interviews.

@AshutoshKumaryt 9 месяцев назад

@@vivekjoshi3769 oh ok but I feel some questions on hackerank good like this was one , you may try practicing on leetcod

@vivekjoshi3769 9 месяцев назад

@@AshutoshKumaryt Indeed this was one of the good questions but it actually affected my mental health also the day when I tried it multiple times bro & spent more than 2 hours on it 😅 Will check leetcode this week, appreciate your suggestions, thanks man. Happy holidays! 🎄

@AshutoshKumaryt 9 месяцев назад

@@vivekjoshi3769 take care man