---

WITH sample_operations AS MATERIALIZED (
    SELECT picked.*
    FROM (
        VALUES (17), (206)
    ) AS types(type_id)
    CROSS JOIN LATERAL (
        SELECT
            o.id,
            o.operation_type_id,
            o.ops_assignee_decision_id,
            o.effective_date
        FROM back_office.operation o
        WHERE o.operation_type_id = types.type_id
          AND o.approved IS NOT NULL
          AND o.deleted IS NULL
        ORDER BY o.id DESC
        LIMIT 3
    ) picked
)
SELECT
    o.id AS operation_id,
    ot.code AS operation_type_code,
    o.ops_assignee_decision_id AS decision_id,
    o.effective_date,
    t.id AS transaction_id,
    dat.mnemonics AS debit_account_type,
    cat.mnemonics AS credit_account_type,
    da.owner_sharer_id AS debit_sharer_id,
    ca.owner_sharer_id AS credit_sharer_id,
    t.value
FROM sample_operations o
JOIN back_office.operation_type ot
    ON ot.id = o.operation_type_id
LEFT JOIN back_office.transaction t
    ON t.operation_id = o.id
LEFT JOIN back_office.account da
    ON da.id = t.debit_account_id
LEFT JOIN back_office.account_type dat
    ON dat.id = da.account_type_id
LEFT JOIN back_office.account ca
    ON ca.id = t.credit_account_id
LEFT JOIN back_office.account_type cat
    ON cat.id = ca.account_type_id
ORDER BY ot.code, o.id DESC, t.id;