Test
Testunknown
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