Direct Servise - Сопровождение

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;