{"id":1586,"date":"2021-05-23T21:30:00","date_gmt":"2021-05-23T16:00:00","guid":{"rendered":"https:\/\/www.codingbroz.com\/?p=1586"},"modified":"2021-06-20T21:41:29","modified_gmt":"2021-06-20T16:11:29","slug":"sql-project-planning-sql-solution","status":"publish","type":"post","link":"https:\/\/www.codingbroz.com\/sql-project-planning-sql-solution\/","title":{"rendered":"SQL Project Planning in SQL | HackerRank Solution"},"content":{"rendered":"\n<p>Hello coders, today we are going to solve <strong>SQL Project Planning 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\/sql-project-planning-sql-solution-codingbroz.png\" alt=\"SQL Project Planning\" class=\"wp-image-1588\" srcset=\"https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/sql-project-planning-sql-solution-codingbroz.png 1200w, https:\/\/www.codingbroz.com\/wp-content\/uploads\/2021\/05\/sql-project-planning-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-662cc14294820\" 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-662cc14294820\" 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\/sql-project-planning-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\/sql-project-planning-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-3\" href=\"https:\/\/www.codingbroz.com\/sql-project-planning-sql-solution\/#Solution_%E2%80%93_SQL_Project_Planning_in_SQL\" title=\"Solution &#8211; SQL Project Planning in SQL \">Solution &#8211; SQL Project Planning 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-4\" href=\"https:\/\/www.codingbroz.com\/sql-project-planning-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>You are given a table,&nbsp;<em>Projects<\/em>, containing three columns:&nbsp;<em>Task_ID<\/em>,&nbsp;<em>Start_Date<\/em>&nbsp;and&nbsp;<em>End_Date<\/em>. It is guaranteed that the difference between the&nbsp;<em>End_Date<\/em>&nbsp;and the&nbsp;<em>Start_Date<\/em>&nbsp;is equal to&nbsp;<em>1<\/em>&nbsp;day for each row in the table.<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><thead><tr><th>Column<\/th><th>Type<\/th><\/tr><\/thead><tbody><tr><td><em>Task_ID<\/em><\/td><td><em>Integer<\/em><\/td><\/tr><tr><td><em>Start_Date<\/em><\/td><td><em>Date<\/em><\/td><\/tr><tr><td><em>End_Date<\/em><\/td><td><em>Date<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>If the&nbsp;<em>End_Date<\/em>&nbsp;of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.<\/p>\n\n\n\n<p>Write a query to output the start and&nbsp;end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.<\/p>\n\n\n\n<p><strong>Sample Input<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><thead><tr><th>Task_ID<\/th><th>Start_Date<\/th><th>End_Date<\/th><\/tr><\/thead><tbody><tr><td><em>1<\/em><\/td><td><em>2015-10-01<\/em><\/td><td><em>2015-10-02<\/em><\/td><\/tr><tr><td><em>2<\/em><\/td><td><em>2015-10-02<\/em><\/td><td><em>2015-10-03<\/em><\/td><\/tr><tr><td><em>3<\/em><\/td><td><em>2015-10-03<\/em><\/td><td><em>2015-10-04<\/em><\/td><\/tr><tr><td><em>4<\/em><\/td><td><em>2015-10-13<\/em><\/td><td><em>2015-10-14<\/em><\/td><\/tr><tr><td><em>5<\/em><\/td><td><em>2015-10-14<\/em><\/td><td><em>2015-10-15<\/em><\/td><\/tr><tr><td><em>6<\/em><\/td><td><em>2015-10-28<\/em><\/td><td><em>2015-10-29<\/em><\/td><\/tr><tr><td><em>7<\/em><\/td><td><em>2015-10-30<\/em><\/td><td><em>2015-10-31<\/em><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><strong>Sample Output<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>2015-10-28 2015-10-29\n2015-10-30 2015-10-31\n2015-10-13 2015-10-15\n2015-10-01 2015-10-04<\/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>The example describes following&nbsp;<em>four<\/em>&nbsp;projects:<\/p>\n\n\n\n<ul><li><em>Project 1<\/em>: Tasks&nbsp;<em>1<\/em>,&nbsp;<em>2<\/em>&nbsp;and&nbsp;<em>3<\/em>&nbsp;are completed on consecutive days, so these are part of the project. Thus start date of project is&nbsp;<em>2015-10-01<\/em>&nbsp;and end date is&nbsp;<em>2015-10-04<\/em>, so it took&nbsp;<em>3 days<\/em>&nbsp;to complete the project.<\/li><li><em>Project 2<\/em>: Tasks&nbsp;<em>4<\/em>&nbsp;and&nbsp;<em>5<\/em>&nbsp;are completed on consecutive days, so these are part of the project. Thus, the start date of project is&nbsp;<em>2015-10-13<\/em>&nbsp;and end date is&nbsp;<em>2015-10-15<\/em>, so it took&nbsp;<em>2 days<\/em>&nbsp;to complete the project.<\/li><li><em>Project 3<\/em>: Only task&nbsp;<em>6<\/em>&nbsp;is part of the project. Thus, the start date of project is&nbsp;<em>2015-10-28<\/em>&nbsp;and end date is&nbsp;<em>2015-10-29<\/em>, so it took&nbsp;<em>1 day<\/em>&nbsp;to complete the project.<\/li><li><em>Project 4<\/em>: Only task&nbsp;<em>7<\/em>&nbsp;is part of the project. Thus, the start date of project is&nbsp;<em>2015-10-30<\/em>&nbsp;and end date is&nbsp;<em>2015-10-31<\/em>, so it took&nbsp;<em>1 day<\/em>&nbsp;to complete the project.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Solution_%E2%80%93_SQL_Project_Planning_in_SQL\"><\/span><strong>Solution &#8211; SQL Project Planning 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 START_DATE, MIN(END_DATE)\nFROM\n  (SELECT START_DATE\n   FROM PROJECTS\n   WHERE START_DATE NOT IN\n       (SELECT END_DATE\n        FROM PROJECTS)) A,\n  (SELECT END_DATE\n   FROM PROJECTS\n   WHERE END_DATE NOT IN\n       (SELECT START_DATE\n        FROM PROJECTS)) B\nWHERE START_DATE &lt; END_DATE\nGROUP BY START_DATE\nORDER BY (MIN(END_DATE) - START_DATE), START_DATE;<\/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\/sql-projects\/problem\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"rank-math-link\">SQL Project Planning<\/a>)<\/strong> is generated by <strong>Hacker Rank<\/strong> but the Solution is Provided by <strong>CodingBroz<\/strong>. This tutorial is only for <strong>Educational<\/strong> and <strong>Learning<\/strong> Purpose.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hello coders, today we are going to solve SQL Project Planning HackerRank Solution in SQL.<\/p>\n","protected":false},"author":1,"featured_media":1588,"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\/sql-project-planning-sql-solution-codingbroz.png","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1586"}],"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=1586"}],"version-history":[{"count":3,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1586\/revisions"}],"predecessor-version":[{"id":3505,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/posts\/1586\/revisions\/3505"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/media\/1588"}],"wp:attachment":[{"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/media?parent=1586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/categories?post=1586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codingbroz.com\/wp-json\/wp\/v2\/tags?post=1586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}