Recursive CTE PostgreSQL Connecting Multiple IDs with Additional Logic for Other Fields

demo:db<>fiddle

Main idea - sketch:

1. Looping through the ordered set. Get all ids, that haven't been seen before in any connected_lead_id (cli). These are your starting points for recursion. The problem is your number 142 which hasn't been seen before but is in same group as 11 because of its cli. So it is would be better to get the clis of the unseen ids. With these values it's much simpler to calculate the ids of the groups later in the recursion part. Because of the loop a function/stored procedure is necessary.

2. The recursion part: First step is to get the ids of the starting clis. Calculating the first referring id by using the created_at timestamp. After that a simple tree recursion over the clis can be done.

1. The function:

CREATE OR REPLACE FUNCTION filter_groups() RETURNS int[] AS $$
DECLARE
    _seen_values int[];
    _new_values int[];
    _temprow record;
BEGIN
    FOR _temprow IN
        -- 1:
        SELECT array_agg(id ORDER BY created_at) as ids, connected_lead_id FROM groups GROUP BY connected_lead_id ORDER BY MIN(created_at)
    LOOP
        -- 2:
        IF array_length(_seen_values, 1) IS NULL 
            OR (_temprow.ids || _temprow.connected_lead_id) && _seen_values = FALSE THEN

            _new_values := _new_values || _temprow.connected_lead_id;
        END IF;

        _seen_values := _seen_values || _temprow.ids;
        _seen_values := _seen_values || _temprow.connected_lead_id;
    END LOOP;

    RETURN _new_values;
END;
$$ LANGUAGE plpgsql;

1. Grouping all ids that refer to the same cli
2. Loop through the id arrays. If no element of the array was seen before, add the referred cli the output variable (_new_values). In both cases add the ids and the cli to the variable which stores all yet seen ids (_seen_values)
3. Give out the clis.

The result so far is {8, 7, 336} (which is equivalent to the ids {2,4,11,142}!)

2. The recursion:

-- 1:
WITH RECURSIVE start_points AS (
    SELECT unnest(filter_groups()) as ids
),
filtered_groups AS (
    -- 3:
    SELECT DISTINCT
       1 as depth, -- 3
       first_value(id) OVER w as id, -- 4
       ARRAY[(MIN(id) OVER w)] as visited, -- 5
       MIN(created_at) OVER w as created_at,
       connected_lead_id,
       MIN(on_boarded_date) OVER w as on_boarded_date -- 6,
       first_value(source) OVER w as source
    FROM groups 
    WHERE connected_lead_id IN (SELECT ids FROM start_points)
    -- 2:
    WINDOW w AS (PARTITION BY connected_lead_id ORDER BY created_at)

    UNION

    SELECT
        fg.depth + 1,
        fg.id,
        array_append(fg.visited, g.id), -- 8
        LEAST(fg.created_at, g.created_at), 
        g.connected_lead_id, 
        LEAST(fg.on_boarded_date, g.on_boarded_date), -- 9
        COALESCE(fg.source, g.source) -- 10
    FROM groups g
    JOIN filtered_groups fg
    -- 7
    ON fg.connected_lead_id = g.id AND NOT (g.id = ANY(visited))

)
SELECT DISTINCT ON (id) -- 11
    id, created_at,on_boarded_date, source 
FROM filtered_groups 
ORDER BY id, depth DESC;

1. The WITH part gives out the results from the function. unnest() expands the id array into each row for each id.
2. Creating a window: The window function groups all values by their clis and orders the window by the created_at timestamp. In your example all values are in their own window excepting 11 and 142 which are grouped.
3. This is a help variable to get the latest rows later on.
4. first_value() gives the first value of the ordered window frame. Assuming 142 had a smaller created_at timestamp the result would have been 142. But it's 11 nevertheless.
5. A variable is needed to save which id has been visited yet. Without this information an infinite loop would be created: 2-8-2-8-2-8-2-8-...
6. The minimum date of the window is taken (same thing here: if 142 would have a smaller date than 11 this would be the result).

Now the starting query of the recursion is calculated. Following describes the recursion part:

7. Joining the table (the original function results) against the previous recursion result. The second condition is the stop of the infinite loop I mentioned above.
8. Appending the currently visited id into the visited variable.
9. If the current on_boarded_date is earlier it is taken.
10. COALESCE gives the first NOT NULL value. So the first NOT NULL source is safed throughout the whole recursion

After the recursion which gives a result of all recursion steps we want to filter out only the deepest visits of every starting id.

11. DISTINCT ON (id) gives out the row with the first occurence of an id. To get the last one, the whole set is descendingly ordered by the depth variable.