-
Notifications
You must be signed in to change notification settings - Fork 0
/
13_SQL_project_planning.sql
73 lines (65 loc) · 2.79 KB
/
13_SQL_project_planning.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/*
You are given a table, *Projects*, containing three columns: *Task_ID*, *Start_Date* and *End_Date*.
It is guaranteed that the difference between the *End_Date* and the *Start_Date* is equal to *1*day for each row in the table.
If the *End_Date* 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. Write a query to output the start and 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.
- Sample Input
- Sample Output
2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04
- Explanation
The example describes following *four* projects:
- Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project.
Thus start date of project is 2015-10-01 and end date is 2015-10-04, so it took 3 days to complete the project.
- Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project.
Thus, the start date of project is 2015-10-13 and end date is 2015-10-15, so it took 2 days to complete the project.
- Project 3: Only task 6 is part of the project.
Thus, the start date of project is 2015-10-28 and end date is 2015-10-29, so it took 1 day to complete the project.
- Project 4: Only task 7 is part of the project.
Thus, the start date of project is 2015-10-30 and end date is 2015-10-31, so it took 1 day to complete the project.
*/
--
SELECT
start_date,
MIN(end_date)
FROM
(SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date
FROM projects)) AS starts,
(SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date
FROM projects)) AS ends
WHERE
start_date < end_date
GROUP BY
start_date
ORDER BY
DATEDIFF(day, start_date, MIN(end_date)),
start_date;
-- second
SET sql_mode=''; -- not to group by end_date as well
SELECT
start_date,
end_date
FROM
(SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date
FROM projects)) AS starts,
(SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date
FROM projects)) AS ends
WHERE
start_date < end_date
GROUP BY
start_date
ORDER BY
DATEDIFF(end_date, start_date),
start_date