CTE block will make your code clear and readable

If you want to make your SQL readable and solve many performance questions first of all create CTE blocks. This simple and understandable solution solve for you many problems. But for better performance I am recommending to analyze your SQL scripts every time.

Example for PostgreSQL.

1. Standard example of CTE block:

WITH _data AS(
  SELECT 1 AS val
)
SELECT val FROM _data;
-- return: 1

2. Example using mapping:

WITH YearCodes (year_code, year) AS (
  VALUES
  ( 'Y', 2000 ),
  ( '1', 2001 ),
  ( '2', 2002 )
)
SELECT * FROM YearCodes

3. Full real examples create a reports:

WITH yearCTE(yearDual) AS (SELECT :taxYear FROM dual)

   , yearsMapCTE(year) AS (
    -- by business case previous 5 years required (from -1 to -5 from current),
    -- this CTE just have it and used in other CTE as logical block for representation
    SELECT yearDual - 5 FROM yearCTE
    UNION ALL SELECT yearDual - 4 FROM yearCTE
    UNION ALL SELECT yearDual - 3 FROM yearCTE
    UNION ALL SELECT yearDual - 2 FROM yearCTE
    UNION ALL SELECT yearDual - 1 FROM yearCTE)

   , mainTmpCTE AS (
    -- this CTE return all 5 years data for specific source, goal is to have whole those data as history to check with xxx data
    -- Important note for this CTE block: return could have revisions, so we need to take only last revision (document) for each year. Logic done in mainCTE block
    SELECT
      return.LIABILITY_NUMBER                                                        AS liabilityNumber,
      return.TAX_YEAR                                                                AS taxYear,
      doc.DOCUMENT_NUMBER                                                            AS docNumber,
      REGEXP_SUBSTR(doc.DOCUMENT_NUMBER, '[^-]+', 1, 1)                              AS docNumberPart1, -- need this to know if document (revision) is last
      COALESCE(CAST(REGEXP_SUBSTR(doc.DOCUMENT_NUMBER, '[^-]+', 1, 2) as number), 0) AS docNumberPart2, -- need this to know if document (revision) is last
      doc.FINALIZED                                                                  AS docFinalized,
      source.id                                                                      AS sourceId,
      COALESCE(adj.LOSS_CARRIED_FORWARD, 0)                                          AS lossCarriedToNextYear
    FROM ....)

   , mainCTE AS (
    -- block will return only last document (revised as approved or draft)
    SELECT mainRanked.* FROM (
      SELECT mainTmpCTE.*, rank() over (partition by mainTmpCTE.docNumberPart1 order by mainTmpCTE.docNumberPart2 desc) rnk FROM mainTmpCTE) mainRanked
    WHERE mainRanked.rnk = 1)

   ...
   ...

   , combineHistoryCTE AS (
    SELECT * FROM (
      SELECT
        histTaxYear,
        prevLossCarriedAmount,
        ROW_NUMBER() OVER (PARTITION BY histTaxYear ORDER BY source) AS rn
      FROM (
        SELECT 1 AS source, histTaxYear, prevLossCarriedAmount FROM histLossCTE WHERE histLossCTE.rnk = 1
        UNION ALL
        SELECT 2 AS source, histTaxYear, prevLossCarriedAmount FROM prevLossCTE
      )
    ) WHERE rn = 1)

SELECT
  yearsMapCTE.year                                        AS year,
  COALESCE(combineHistoryCTE.prevLossCarriedAmount, 0.00) AS previousLossCarriedAmount
FROM yearsMapCTE
  LEFT JOIN currentMainCTE ON yearsMapCTE.year = currentMainCTE.taxYear - 1
  LEFT JOIN combineHistoryCTE ON yearsMapCTE.year = combineHistoryCTE.histTaxYear;