---

select
    a.id as account_id,
    at.mnemonics as account_type,
    count(t.id) as transaction_count,
    min(t.effective_date) as first_transaction_date,
    max(t.effective_date) as last_transaction_date,

    sum(
        case
            when cast(t.effective_date as date) in (
                     date '2023-05-31',
                     date '2020-03-30'
                 )
              or cast(o.effective_date as date) in (
                     date '2023-05-31',
                     date '2020-03-30'
                 )
            then 1
            else 0
        end
    ) as transactions_on_target_dates

from back_office.account a
left join back_office.account_type at
    on at.id = a.account_type_id
left join back_office.transaction t
    on (
        t.debit_account_id = a.id
        or t.credit_account_id = a.id
    )
left join back_office.operation o
    on o.id = t.operation_id

where a.owner_sharer_id = 14272405

group by a.id, at.mnemonics
order by a.id;