jfk9w-go / homebot Goto Github PK

with s as (select distinct on (id, time) id, time, account_amount
           from debit d
           left join shopping_receipt_items i on d.id = i.shopping_receipt_id
           where category in ('Мобильная связь')
             and merchant_name in ('')
             and (i.name in ('__all__') or '__all__' in ('__all__'))),
     t as (select date_trunc('month', time) as time,
                  sum(account_amount)       as amount
           from s
           group by 1
           order by 1),
     u as (select time, amount, avg(amount) over (order by time rows between 12 preceding and 1 preceding) as avg_amount from t)
select time, (amount - avg_amount) / avg_amount as pir
from u
where time >= '2021-06-28T08:20:49.649Z'::timestamp - '1 month'::interval and time < '2022-07-28T08:20:49.649Z'