CREATE OR REPLACE VIEW public.returning_clients
AS SELECT sf.year_month,
    sf.clientaltkey,
    sf.group_format,
    sf.region,
    sf.format,
    sf.revenue,
    sf1.year_month_before,
    sf1.group_format_before,
    news.mark
   FROM segmentation_formats sf
     LEFT JOIN ( SELECT DISTINCT segmentation_formats.year_month AS year_month_before,
            segmentation_formats.clientaltkey,
            segmentation_formats.group_format AS group_format_before
           FROM segmentation_formats) sf1 ON sf.clientaltkey::text = sf1.clientaltkey::text AND sf.year_month = (sf1.year_month_before + '1 mon'::interval)
     LEFT JOIN ( SELECT make_date(n.year::integer, n.month::integer, 1) AS date_new,
            n.clientaltkey,
            1 AS mark
           FROM newclients n) news ON news.date_new = sf.year_month AND news.clientaltkey::text = sf.clientaltkey::text
  WHERE sf.year_month >= '2025-01-01'::date;