new_menu_packs

select
packages."_id"
, packages."Date" AS pack_date
, "packages"."DeliveryDate" AS delivery_date
, "SubscriptionSettings.ProductCode" as product_code
, COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id") as "dish_id"
-- , count(packages."_id") over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes_in_packs"
-- , count(COALESCE(packages_menu."Menu.Items.DishCode", menus."Meals.Containers._id")) over(PARTITION BY packages."_id" ORDER BY "packages"."DeliveryDate" ROWS BETWEEN unbounded preceding AND unbounded following) as "dishes"
, "SubscriptionSettings.Count" as "packs_count"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then true
       else false
       end as "replaced"
, case when packages_menu."Menu.Items.DishCode" != packages_menu."Menu.Items.OriginalDishCode" then "packages"."DeliveryReportGuid"
       else null
       end as "delivery_report_id"
from packages
left join packages_menu on (packages_menu."_id" = packages."_id"
                        and packages."IsReplaced" is true
                        )
left join menus ON (packages."Date"= menus."Date" + '3 hour'
                aND packages."SubscriptionSettings.ProductCode" = menus."Program"
                and packages."IsReplaced" is false
                and menus."IsDeleted" is false
                )
where packages."State" = 3
and packages."IsDeleted" is false
and packages."Date" >= '2022-01-01'
-- and packages."_id" = '00039e24-67cb-4dbd-b35c-634997077458'
order by packages."_id"