test.sql

test.sql

with boletos as
(
    select
    to_char (ii .created_at  ,  'YYYY-MM' ) as mes ,
    --sum(ii .amount ) as valor_total_invoices ,
    --count(ii .id ) as quantidade_invoices ,
    sum (ii .amount ) filter (where bb.created_at is not null) as valor_total_boletos ,
    count(distinct i .account_id ) filter (where bb.created_at  is  not null) as total_clientes_com_boletos ,
    count(bb .id ) filter (where bb .created_at  is  not null) as quantidade_boletos_emitidos ,
    count(bb .id ) filter  ( where bb.paid_at  is not null) as quantidade_boletos_liquidados ,
    count(distinct i.account_id ) filter (where bb.paid_at  is not null)  as total_clientes_com_boletos_liquidados ,
    sum(ii .amount ) filter (where bb.paid_at is not null) as valor_total_boletos_liquidados ,
    count(bb .id ) filter (where bb.canceled_at is not null) as quantidade_boletos_cancelados
    from
    invoices_items  ii  
    inner join invoices  i  on i .id  = ii .invoice_id
    left join bank_billets  bb  on bb.invoice_item_id  = ii .id
    where
    ii .created_at between '2023-01-01'   and '2023-05-31'
    group by to_char (ii .created_at  ,  'YYYY-MM' )
    order by to_char (ii .created_at  ,  'YYYY-MM' )
),
tarifas  as (
    select
    to_char (m .operation_date  ,  'YYYY-MM' ) as mes ,
    sum(amount ) filter (where m .operation  =  'partner.bonus' ) as partner_bonus ,
    sum(amount ) filter (where m .operation  =  'pay.rate' ) as pay_rate ,
    sum(amount ) filter (where m .operation  =  'transfer.rate' ) as transfer_rate
    from movements  m  
    where m .operation_date  between '2023-01-01'   and '2023-05-31'
    and operation  in (
        'partner.bonus' ,
        'pay.rate' ,
        'transfer.rate'
    )
    group  by to_char (m .operation_date  ,  'YYYY-MM' )
    order by to_char (m .operation_date  ,  'YYYY-MM' )
)
select
b.*,
t.partner_bonus ,
t.pay_rate  * -1   as pay_rate ,
t .transfer_rate  * -1  as transfer_rate
from boletos  b
left join tarifas  t   on t .mes  = b .mes