Having a simple task (called projects) table with task_id, start and end dates, identify begin and end date for groups of tasks that are back 2 back. Back 2 back (b2b), means that a task starts on the same date where another ended.
2 solutions:
I approached the solution as one where you needed to group the b2b tasks by its root or beggining task. As this is a tree, I used recursion through common table expressions (CTEs).
I have a thing for recursion, as you can see here, where I built a date dimension using CTEs and the perks of cartesian product.
This query will identify the root tasks and then group by them. Once grouped, min and max dates are selected
WITH recursive CTE_Projects AS
(
SELECT
0 previous_task_id, task_id root_task_id, task_id, Start_Date, End_Date
FROM Projects
where Start_Date not IN (SELECT End_Date FROM Projects)
union all
select
previous.task_id parent_task_id, previous.root_task_id root_task_id, p.task_id, p.start_date, p.end_date
from Projects p, CTE_Projects previous
where p.start_date = previous.end_date
)
SELECT
MIN(Start_Date), MAX(End_Date),
DATEDIFF(MAX(End_Date), MIN(Start_Date))
from CTE_Projects
group by root_task_id
order by DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date)This was probably the easier more obvious solution. Identify both start and end tasks and just identify the minimum end task that corresponds to each start task. This way you get start and end date of b2b tasks.
This solution takes the assumption that there are NO OVERLAPPING GROUP OF B2B TASKS
SELECT
start_task.Start_Date,
MIN(end_task.End_Date),
DATEDIFF(MIN(end_task.End_Date), start_task.Start_Date)
FROM
(SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) start_task,
(SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) end_task
WHERE start_task.Start_Date <= end_task.End_Date
GROUP BY start_task.Start_Date
ORDER BY DATEDIFF(MIN(end_task.End_Date), start_task.Start_Date), start_task.Start_DateThis problem/question was taken from here You can use the MySQL script below to populate test data and test your solution against the actual hackerrank question.