Test

Test
mail@pastecode.io avatar
unknown
pgsql
a year ago
1.9 kB
6
Indexable
Never
with last_orders_per_client
as (
select
	so.client_id as "client_id",
	maxentrytime as "max_entry_time",
	MAX(so.id) as "latest_order_id"
from
	supply_order so
join (
	select
		client_id,
		MAX(created_at) MaxEntryTime
	from
		supply_order
	where
		supply_order_status in ('STOCKED', 'ASSIGNED', 'ON_ROUTE', 'DELIVERED')
	group by
		client_id
) B on
	so.client_id = B.client_id
	and so.created_at = B.MaxEntryTime
	and so.supply_order_status in ('STOCKED', 'ASSIGNED', 'ON_ROUTE', 'DELIVERED')
group by
	so.client_id,
	maxentrytime
having
	date_trunc('day', max(created_at)) >= cast(date_trunc('month', current_date) as date) - interval '1 months'
	and date_trunc('day', max(created_at)) < cast(date_trunc('month', current_date) as date)),

recovered_clients
as (select
	distinct so.client_id,
	max(so.created_at) as "order_date",
	max(so.id) as "id_order"
from
	supply_order so
where
	so.id not in (
	select
		latest_order_id
	from
		last_orders_per_client)
	and so.client_id in (
	select
		client_id
	from
		last_orders_per_client)
	and so.supply_order_status in ('STOCKED', 'ASSIGNED', 'ON_ROUTE', 'DELIVERED')
group by
	so.client_id
having
	max(so.created_at) >= cast(date_trunc('month', current_date) as date) - interval '4 months'
	and max(so.created_at) < cast(date_trunc('month', current_date) as date) - interval '2 months')
	
select rc.client_id, sum(so.total_with_promotion) as "sum_orders_current_month" 
from recovered_clients rc
inner join
supply_order so
on rc.client_id = so.client_id
where so.supply_order_status in ('STOCKED', 'ASSIGNED', 'ON_ROUTE', 'DELIVERED')
and date_trunc('day', so.created_at) >= cast(date_trunc('month', current_date) as date) - interval '1 months'
and date_trunc('day', so.created_at) < cast(date_trunc('month', current_date) as date)
GROUP BY rc.client_id