We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  • Hackerrank Home
  • Prepare
    NEW
  • Certify
  • Compete
  • Career Fair
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Advanced Join
  4. SQL Project Planning
  5. Discussions

SQL Project Planning

Problem
Submissions
Leaderboard
Discussions

Sort 935 Discussions, By:

recency

Please Login in order to post a comment

  • dorirgob
    3 hours ago+ 0 comments

    Here is my take on Mysql. Any advice is appreciated.

    /* This outer query is  only for ordering by  project duration */
    SELECT P3.sd,P3.ed FROM 
    
    (SELECT  MIN(P1.Start_Date) AS sd  , P2.End_Date  AS ed
    FROM Projects AS P1
    INNER JOIN Projects AS P2
    ON  p2.End_Date  NOT IN (SELECT Start_Date From Projects) /*we exclude the end dates that exists on Start_Date which means they are part of the same project*/
    AND
    /*Once we've found an end date  not same as any of the start dates we check backwards how many other cols  there are within the same time span  count them first if the number equals to the day difference that means they are actually linked.  Use "group by" End_date with MIN() to  find the earliest start date there is that is linked        */
    (SELECT count(*) From Projects  WHERE Start_Date BETWEEN P1.Start_Date AND P2.End_Date ) = DATEDIFF(P2.End_Date,P1.Start_Date) 
    
    GROUP BY P2.End_Date) AS P3
    
    
    ORDER by DATEDIFF(P3.ed, P3.sd), P3.sd ;
    
    0|
    Permalink
  • Wisnu_D_Uzu
    9 hours ago+ 0 comments
    WITH difference AS (
        SELECT 
            start_date, 
            end_date,
            @group := IF(@prev_end = start_date, @group, @group + 1) AS grp,
            @prev_end := end_date
        FROM projects, (SELECT @prev_end := NULL, @group := 0) vars
        ORDER BY 1
    )
    SELECT 
        MIN(start_date) AS start_date, 
        MAX(end_date) AS end_date
    FROM difference
    GROUP BY grp
    ORDER BY count(grp), 1;
    
    0|
    Permalink
  • kalendercem
    1 day ago+ 0 comments
     select min(Start_Date), max(End_date)
     from (
        select *, day(End_date) - row_number() over (order by end_date) dif
        from Projects) x
    group by dif
    order by datediff(day,min(Start_Date), max(End_date)), min(Start_Date);
    
    0|
    Permalink
  • mayank912912
    1 day ago+ 0 comments

    mysql solution

    select start_date, end_date 
    from 
    (select start_date, row_number() over(order by start_date) as x from projects where start_date not in (select end_date from projects)) as a  join 
    
    (select end_date, row_number() over(order by end_date) as y  from projects where end_date not in (select start_date from projects)) as b 
    on a.x = b.y
    
    order by datediff(end_date, start_date), 1
    
    0|
    Permalink
  • kabilanna12345
    1 week ago+ 0 comments

    select start_date,end_date from

    (select start_date,x from (select start_date,row_number() over(order by start_date) x from projects where start_date not in (select end_date from projects)) e ) a join

    (select end_date,y from (select end_date,row_number() over(order by end_date) y from projects where end_date not in (select start_date from projects)) t) b on a.x=b.y

    order by Datediff(end_date, start_date), start_date

    0|
    Permalink
Load more conversations

Need Help?


View top submissions
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy