{"id":1602,"date":"2021-05-23T07:30:00","date_gmt":"2021-05-23T02:00:00","guid":{"rendered":"https:\/\/www.codingbroz.com\/?p=1602"},"modified":"2021-06-20T21:39:52","modified_gmt":"2021-06-20T16:09:52","slug":"challenges-sql-solution","status":"publish","type":"post","link":"https:\/\/www.codingbroz.com\/challenges-sql-solution\/","title":{"rendered":"Challenges in SQL | HackerRank Solution"},"content":{"rendered":"\n<p>Hello coders, today we are going to solve <strong>Challenges HackerRank Solution in SQL<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img fetchpriority=\"high\" decoding=\"async\" width=\"1200\" height=\"676\" src=\"https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/challenges-sql-solution-codingbroz.png\" alt=\"Challenges\" class=\"wp-image-1604\" srcset=\"https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/challenges-sql-solution-codingbroz.png 1200w, https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/challenges-sql-solution-codingbroz-768x433.png 768w\" sizes=\"(max-width: 1200px) 100vw, 1200px\" \/><\/figure>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_65 ez-toc-wrap-left counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-662c25b03ba09\" class=\"ez-toc-cssicon-toggle-label\"><label class=\"ez-toc-title\">Table of Contents<\/label>\n<span class=\"ez-toc-cssicon\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #0090ff;color:#0090ff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #0090ff;color:#0090ff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-662c25b03ba09\" checked \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.codingbroz.com\/challenges-sql-solution\/#Problem\" title=\"Problem\">Problem<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.codingbroz.com\/challenges-sql-solution\/#Input_Format\" title=\"Input Format\">Input Format<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.codingbroz.com\/challenges-sql-solution\/#Explanation\" title=\"Explanation\">Explanation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.codingbroz.com\/challenges-sql-solution\/#Solution_%E2%80%93_Challenges_in_SQL\" title=\"Solution &#8211; Challenges in SQL \">Solution &#8211; Challenges in SQL <\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.codingbroz.com\/challenges-sql-solution\/#MySQL\" title=\"MySQL\">MySQL<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Problem\"><\/span><strong>Problem<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Julia asked her students to create some coding challenges. Write a query to print the&nbsp;<em>hacker_id<\/em>,&nbsp;<em>name<\/em>, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by&nbsp;<em>hacker_id<\/em>. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Input_Format\"><\/span><strong>Input Format<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The following tables contain challenge data:<\/p>\n\n\n\n<ul><li><em>Hackers:<\/em>&nbsp;The&nbsp;<em>hacker_id<\/em>&nbsp;is the id of the hacker, and&nbsp;<em>name<\/em>&nbsp;is the name of the hacker.&nbsp;<\/li><li><em>Challenges:<\/em>&nbsp;The&nbsp;<em>challenge_id<\/em>&nbsp;is the id of the challenge, and&nbsp;<em>hacker_id<\/em>&nbsp;is the id of the student who created the challenge.&nbsp;<\/li><\/ul>\n\n\n\n<p><strong>Sample Input 0<\/strong><\/p>\n\n\n\n<p>Hackers Table <\/p>\n\n\n\n<p>Challenges Table <\/p>\n\n\n\n<p><strong>Sample Output 0<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>21283 Angela 6\n88255 Patrick 5\n96196 Lisa 1<\/code><\/pre>\n\n\n\n<p><strong>Sample Input 1<\/strong><\/p>\n\n\n\n<p>Hackers Table <\/p>\n\n\n\n<p>Challenges Table <\/p>\n\n\n\n<p><strong>Sample Output 1<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>12299 Rose 6\n34856 Angela 6\n79345 Frank 4\n80491 Patrick 3\n81041 Lisa 1<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Explanation\"><\/span><strong>Explanation<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>For&nbsp;<em>Sample Case 0<\/em>, we can get the following details:<br><br>Students&nbsp;<strong>5077<\/strong>&nbsp;and&nbsp;<strong>62743<\/strong>&nbsp;both created&nbsp;<strong>4<\/strong>&nbsp;challenges, but the maximum number of challenges created is&nbsp;<strong>6<\/strong>&nbsp;so these students are excluded from the result.<\/p>\n\n\n\n<p>For&nbsp;<em>Sample Case 1<\/em>, we can get the following details:<br><br>Students&nbsp;<strong>12299<\/strong>&nbsp;and&nbsp;<strong>34856<\/strong>&nbsp;both created&nbsp;<strong>6<\/strong>&nbsp;challenges. Because&nbsp;<strong>6<\/strong>&nbsp;is the maximum number of challenges created, these students are included in the result.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Solution_%E2%80%93_Challenges_in_SQL\"><\/span><strong>Solution &#8211; Challenges in SQL <\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"MySQL\"><\/span><strong>MySQL<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT h.hacker_id, \n       h.name, \n       COUNT(c.challenge_id) AS c_count\nFROM Hackers h\nJOIN Challenges c ON c.hacker_id = h.hacker_id\nGROUP BY h.hacker_id, h.name\nHAVING c_count = \n    (SELECT COUNT(c2.challenge_id) AS c_max\n     FROM challenges as c2 \n     GROUP BY c2.hacker_id \n     ORDER BY c_max DESC limit 1)\nOR c_count IN \n    (SELECT DISTINCT c_compare AS c_unique\n     FROM (SELECT h2.hacker_id, \n                  h2.name, \n                  COUNT(challenge_id) AS c_compare\n           FROM Hackers h2\n           JOIN Challenges c ON c.hacker_id = h2.hacker_id\n           GROUP BY h2.hacker_id, h2.name) counts\n     GROUP BY c_compare\n     HAVING COUNT(c_compare) = 1)\n\nORDER BY c_count DESC, h.hacker_id;\n<\/pre>\n\n\n\n<script async src=\"https:\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-5246141430412355\"\n     data-ad-slot=\"8798433565\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Disclaimer:<\/strong> The above Problem <strong>(<a href=\"https:\/\/www.hackerrank.com\/challenges\/challenges\/problem\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"rank-math-link\">Challenges<\/a>)<\/strong> is generated by <strong>Hacker Rank<\/strong> but the Solution is Provided by <strong>CodingBroz<\/strong>. This tutorial is only for <strong>Education<\/strong> and <strong>Learning<\/strong> Purpose.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hello coders, today we are going to solve Challenges HackerRank Solution in SQL.<\/p>\n","protected":false},"author":1,"featured_media":1604,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9,138],"tags":[80,18,139,156],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/challenges-sql-solution-codingbroz.png","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1602"}],"collection":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/comments?post=1602"}],"version-history":[{"count":3,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1602\/revisions"}],"predecessor-version":[{"id":3503,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1602\/revisions\/3503"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/media\/1604"}],"wp:attachment":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/media?parent=1602"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/categories?post=1602"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/tags?post=1602"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}