Cardano db sync sender and receiver address confusion

Question

I am needing help with this. I queried the rows whose tx_id = 133903 from tx_out table from postgres.

select * from tx_out where tx_id = 133903;

and I got two rows having two different indices 0 and 1. The result is below in JSON format.

[
  {
    "id": 240155,
    "tx_id": 133903,
    "index": 0,
    "address": "DdzFFzCqrhskf5Bho1HFpjzFGcVG3avzBMBkP4DUHMJMuqaNmPrt5Cnf1GUxQ3gFxFrbZUHsrxgqUf3fkn5NxjHSDh67SfuKHGLcYYuV",
    "address_raw": "E'\\\\x82D818584283581C3490095835CC47B0D182A0DC054D49816D9782FA42D3D26EFF4F613EA101581E581C740E27451628BDD32329FC359C8FC1C1A48FE25B94908AB8ABB577A1001A7E3A51E8'",
    "payment_cred": null,
    "stake_address_id": null,
    "value": 2936699288244,
    "address_has_script": false,
    "data_hash": null
  },
  {
    "id": 240156,
    "tx_id": 133903,
    "index": 1,
    "address": "DdzFFzCqrht3JYLq1Cc9Y3EeyZvTwzwuu5QZCJqbmjFVMj23miQeSMESfJoSb4f1NNJL9ti1nJryGSB1VK7yFbzkRtcEjamaSvj6LP8k",
    "address_raw": "E'\\\\x82D818584283581CB3CC4806AB502B2EC4FFD82CDA50BF4E5465769B6BCB6C0692C6F797A101581E581CCA3E553C9C63C562EE05D443E8D3D451372D66B450379B9002540A9B001A88AF5021'",
    "payment_cred": null,
    "stake_address_id": null,
    "value": 100000000000,
    "address_has_script": false,
    "data_hash": null
  }
]

Correct me if I am wrong but the address field in the tx_out table basically is the receiver right? I want to get the sender's address. How do I find out the sender of this transaction? I am having some hard time figuring this out. Basically I want to create a table that contains the address of sender and receiver and amount for all the transaction ever recorded in the postgres table.

Answer 1

I am not 100 % sure of this answer; The input is stored in tx_in table and the corresponding inputs addresses can be found by

select * from tx_out where tx_id in ( select tx_in_id from tx_in where tx_out_id =133903);

you are getting the address which are inputs to the transaction.

Answer 2

Query:

select prev_tx_out.address sender
     , this_tx_out.address receiver
     , this_tx_out.value

  from tx this_tx

 inner join tx_out this_tx_out
    on this_tx_out.tx_id = this_tx.id

 inner join tx_in this_tx_in
    on this_tx_in.tx_in_id = this_tx.id

 inner join tx_out prev_tx_out
    on prev_tx_out.tx_id = this_tx_in.tx_out_id
   and prev_tx_out.index = this_tx_in.tx_out_index

 where this_tx.id = 133903
;

Result:

                                                  sender                                                  |                                                 receiver                                                 |     value     
----------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------+---------------
 DdzFFzCqrhsycjyjN1UGhehYVs7ZcCfoUR8PNF7QaXgYtNcEci68gLuFrbioBTyvDPW6ztiEDYPZC3jXH9DqjEgXhzLLGUL5hdENMVF4 | DdzFFzCqrhskf5Bho1HFpjzFGcVG3avzBMBkP4DUHMJMuqaNmPrt5Cnf1GUxQ3gFxFrbZUHsrxgqUf3fkn5NxjHSDh67SfuKHGLcYYuV | 2936699288244
 DdzFFzCqrhsycjyjN1UGhehYVs7ZcCfoUR8PNF7QaXgYtNcEci68gLuFrbioBTyvDPW6ztiEDYPZC3jXH9DqjEgXhzLLGUL5hdENMVF4 | DdzFFzCqrht3JYLq1Cc9Y3EeyZvTwzwuu5QZCJqbmjFVMj23miQeSMESfJoSb4f1NNJL9ti1nJryGSB1VK7yFbzkRtcEjamaSvj6LP8k |  100000000000

Answer 3

Remember that a transaction always spends all the transaction input.

Unless the total (tx_out.value) + fee == tx_in.value there will be two tx_out entries, one for the recipient and the other is the change output that goes back to the sending wallet.

Answer 4

Using the code of @grim_i_am

1. for transaction 34369121, I have the following. Am I right to understand that in this transaction, 1 sender send to multiple receiver with different amount? If so, why at the receiver, the receiver's address are duplicated?

2. For transaction id 34368652, the sender and receiver are multiple. this is normal? How can 2 sender can send at the same time? I am not quite understand.