Is there a way to optimize this query in Tsql?

Question

WITH CTE AS
(
   WITH flattened_data AS 
   (
    SELECT
        DISTINCT
        MAX(CASE WHEN (entry.value:Id::STRING)='123' THEN entry.value:Answer:text::STRING END) AS VD,
        MAX(CASE WHEN (entry.value:Id::STRING)='3456' THEN entry.value:Answer:text::STRING END) AS DPL,
        MAX(CASE WHEN (entry.value:Id::STRING)='8971' THEN entry.value:Answer:text::STRING END) AS DL_Date
    FROM TABLE_FCA,
         LATERAL FLATTEN(input => COLUMN_TX:Question) AS entry
    WHERE entry.value:Answer:text IS NOT NULL
        AND entry.value:Id IN ('123','3456','8971')
  GROUP BY COLUMN_TX
  HAVING VD IS NOT NULL
  )

SELECT VD,MAX(DPL) AS DPL ,MAX(DL_Date) AS DL_Date 
FROM flattened_data GROUP BY VD

)

Select * from
TABLE_A A
Join TABLE_B B ON...
.
.
.
.
.
LEFT JOIN CTE ON CTE.VD=A.VD

The reason why it is done like this is beause the TABLE_FCA doesn't have a unique key and COLUMN_TX has a json value. There could be multiple json records having the same VD and could have differrent DL_Date. using a CTE inside a CTE and then using it in another JOIN feels like there could be an easier way which iam missing.

I tried using CTE inside a CTE. Iam not sure if there is a better way this could be done like using a temp table.