SQLize | PHPize | SQLtest

with balance_with_prev as ( select bal_date::date , code , coalesce(value,0) as current_balance_value , lag( coalesce(value,0), 1) over(partition by code order by bal_date asc) as prev_balance_value from benefit_balances ) , daily_points_transactions as ( select code , created_at::date as trans_date , coalesce(sum(case when direction=1 then cust_sum else 0 end),0) as points_add , coalesce(sum(case when direction=0 then cust_sum else 0 end),0) as points_spent from benefit_points group by 1,2 ) Select bwp.bal_date , bwp.code , bwp.current_balance_value , bwp.prev_balance_value , dpt.points_add , dpt.points_spent , case when bwp.prev_balance_value is null and bwp.current_balance_value = 0 then true --первая запись when bwp.prev_balance_value is null and bwp.current_balance_value != 0 then false -- ошибка первой записи, например не почистили тестовые данные when bwp.current_balance_value=bwp.prev_balance_value+dpt.points_add-dpt.points_spent then true else false end as is_match from balance_with_prev bwp left join daily_points_transactions dpt on dpt.code=bwp.code AND dpt.trans_date = DATEADD('day', -1, bwp.bal_date) -- where bwp.prev_balance_value is not null and bwp.current_balance_value != 0 -- в зависимости от требований к итоговой таблице, можем хотеть избавиться, от нулевых записей и не пересчитывать их order by 2,1