Q:

Finding the commencement date of a new project

Interested in a challenging SQL problem, read ahead:
For the data set below, I'm trying to find a logic which identifies the commencement date of a new project for each employee. 
Data Set

The logic to identify commencement date of new project is that:

An employee will not have any date record prior to the present one in a 14 day time frame.
Project windows only last 14 days after the commencement. The first record falling outside such a window will be counted as the start of the next project.

What is needed

Both Redshift/ Postgres solutions accepted.
Please note Redshift doesn't support recursive CTEs or RANGE keyword in window frame.
Thanks for reading.

A:

For Postgresql, including the CTE (DataSet) for the dataset, here you go:
WITH RECURSIVE TimeLine(Employee, ProjectID, ProjectStartDate, Date, DateRank) AS (
    SELECT Employee, 1, Date, Date, DateRank
    FROM DataSetWithRank
    WHERE DateRank = 1
    UNION ALL
    SELECT T.Employee,
           T.ProjectID + CASE When D.Date >= T.ProjectStartDate+14 THEN 1 Else 0 END,
           CASE When D.Date >= T.ProjectStartDate+14 THEN D.Date Else T.ProjectStartDate END,
           D.Date, D.DateRank
    FROM TimeLine T
    JOIN DataSetWithRank D ON D.Employee = T.Employee AND D.DateRank = T.DateRank + 1
), DataSet(Employee,Date) AS (
SELECT UNNEST(ARRAY['Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1','Employee1']),
    UNNEST(ARRAY['2018-01-01','2018-01-03','2018-01-05','2018-01-08','2018-01-11','2018-01-13','2018-01-14','2018-01-16','2018-01-18','2018-01-21','2018-01-22','2018-01-24','2018-01-25','2018-01-27','2018-01-29']::date[])
UNION
SELECT UNNEST(ARRAY['Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2','Employee2']),
    UNNEST(ARRAY['2018-01-03','2018-01-05','2018-01-07','2018-01-10','2018-01-13','2018-01-15','2018-01-16','2018-01-18','2018-01-20','2018-01-23','2018-01-24','2018-01-26','2018-01-27','2018-01-29','2018-01-31']::date[])
), DataSetWithRank AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY Employee ORDER BY Date) AS DateRank
FROM DataSet
)
SELECT Employee,
       'Project ' || ProjectID AS "Project #",
       Date,
       DENSE_RANK() OVER (PARTITION BY Employee, ProjectID ORDER BY Date) AS Rank,
       CASE WHEN Date = ProjectStartDate THEN 'Y' ELSE NULL END AS Is_New
FROM TimeLine

