How to optimize query by preventing repetitive subquery
You can use a lateral cross join, although I don't understand why you want to avoid a CTE:
SELECT dim_date.date AS &quot;date&quot;, 
       stage_net_subscription_020_classes.client_id AS client_id,
       stage_net_subscription_020_classes.product_id AS product_id
FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes
  CROSS JOIN LATERAL (
    select min(date) as min_date, max(date) as max_date 
    from stage.stage_net_subscription_010_filter_and_net_pds
  ) as mm
  where date &gt;= mm.min_date
  and   date &lt;= mm.max_date