with packs as(select * 
, "packages"."ClientGuid" as client_id
, last_value(packages."_id") OVER (PARTITION BY "packages"."ClientGuid" ORDER BY "DeliveryDate" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_pack
, (last_value(packages."DeliveryDate") OVER (PARTITION BY "packages"."ClientGuid" ORDER BY "DeliveryDate" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) + '3 hour' AS last_pack_date
, last_value("SubscriptionGuid") OVER (PARTITION BY packages."ClientGuid" ORDER BY "DeliveryDate" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sub
from subscriptions
left join packages on subscriptions."_id" = "packages"."SubscriptionGuid"
where "DeliveryDate" >= '2021-07-01' and "DeliveryDate" < '2021-08-01'
and packages."IsDeleted" is false
and "State" = 3
and subscriptions."IsDeleted" is False
and subscriptions."IsCanceled" is False
and subscriptions."Settings.IsPromo" is False
and subscriptions."ScheduleParams.DaysCount" >= 5
order by packages."ClientGuid", packages."DeliveryDate" 
--limit 1000
), pays as(

select *
, "subscriptions"."ClientGuid" as client_id
, payments."StatusDate" + '3 hour' as august_pay_date
, payments."_id" as pay_id
, first_value(payments."_id") OVER (PARTITION BY "payments"."ClientGuid" ORDER BY "StatusDate" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_august_pay
, (first_value(payments."StatusDate") OVER (PARTITION BY "payments"."ClientGuid" ORDER BY "StatusDate" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) + '3 hour' AS first_august_pay_date
from subscriptions
left join payments on subscriptions."_id" = "payments"."SubscriptionGuid"
where "Status" = 3 
and "Type" not in (2, 5, 7)
and subscriptions."IsDeleted" is False
and subscriptions."IsCanceled" is False
and subscriptions."Settings.IsPromo" is False
and subscriptions."ScheduleParams.DaysCount" >= 5
and "StatusDate" >= '2021-07-01' 
order by "subscriptions"."ClientGuid", "StatusDate"
)
select 
packs."client_id"
, last_pack
, last_pack_date
--, last_sub
, last_value("last_pack") OVER (PARTITION BY packs."client_id" ORDER BY "last_pack_date" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_july_pack
, last_value(last_pack_date) OVER (PARTITION BY packs."client_id" ORDER BY "last_pack_date" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_july_pack_date
, last_value("last_sub") OVER (PARTITION BY packs."client_id" ORDER BY "last_pack_date" rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_july_sub
--, july_packs."SubscriptionGuid" as july_sub
--, last_july_sub
--, last_july_pack
--, last_pack_date
, first_august_pay_date
, first_august_pay
--, pay_id as august_pay_id
--, pays."pay_id" as august_sub

from packs
left join pays on pays."client_id" = packs."client_id"
and first_august_pay_date > last_pack_date
--where first_august_pay_date is not null
order by "client_id", last_pack_date, first_august_pay_date