3. Review wallet transaction history
For consistency, we will query the same preprod address as before:
addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t
🔎 You can verify results using any of the community explorers listed here (opens in a new tab).
CardanoScan search (opens in a new tab) (remember to choose 'Preprod' in the top right)
We will break the query up into three parts to fetch the information we need for the overall query.
3.1 Transaction details
First, let's determine which transaction details we want to collect:
- amount of ADA transferred
- if the transaction is incoming/outgoing
- transaction fee
- assets (name and quantity)
- transaction hash
- Timestamp
- Slot/Block
-- Transactions where the address is the output
SELECT
COALESCE(sum(tx_out.value), 0) - COALESCE(inp.inp_value, 0) AS ADA_amount,
'outgoing' AS direction,
tx.fee AS fee,
tx.hash AS tx_hash,
block."time" AS time_stamp,
block.block_no AS block,
block.slot_no AS slot
FROM tx_out
JOIN tx ON tx.id = tx_out.tx_id
LEFT JOIN ( SELECT
tx.id AS inp_txid,
tx.hash AS inp_hash,
tx_out.address AS source_add,
sum(tx_out.value) AS inp_value
FROM tx_in
JOIN tx_out ON tx_out.tx_id = tx_in.tx_out_id
AND tx_out.index = tx_in.tx_out_index
JOIN tx ON tx.id = tx_in.tx_in_id
WHERE
tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'
GROUP BY
inp_txid,
inp_hash,
source_add
)
AS inp ON inp.inp_txid = tx.id
JOIN block ON block.id = tx.block_id
WHERE
tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'
GROUP BY
tx_hash,
direction,
fee,
time_stamp,
block,
slot,
inp.inp_value
UNION
-- Transactions where the address is the input and not an output
SELECT
sum(tx_out.value) AS ADA_amount,
'incoming' AS direction,
tx.fee AS fee,
tx.hash AS tx_hash,
block."time" AS time_stamp,
block.block_no AS block,
block.slot_no AS slot
FROM tx_in
JOIN tx_out ON tx_out.tx_id = tx_in.tx_out_id
AND tx_out.index = tx_in.tx_out_index
JOIN tx ON tx.id = tx_in.tx_in_id
JOIN block ON block.id = tx.block_id
WHERE
tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'
AND NOT EXISTS (SELECT 1 AS txid
FROM tx_out
WHERE
tx_out.address = 'addr_test1qr70pedl22tnzswcfm39nqvsfan9jgtym2k0lgrftga8j67jel8q0vyag8770e8pu3rf6he70dtrexrsg6waj09cae0svmt57t'
AND tx_out.tx_id = tx.id )
GROUP BY
tx_hash,
direction,
fee,
time_stamp,
block,
slotExpected results format
ada_amount | direction | fee | tx_hash | time_stamp | block | slot
------------------+-----------+---------+------------------------------------------------------------------+-----------------------+-----------+------------
2,000,000,000,000 | outgoing | 174,961 | 17abe1200ebe4b801d830f8c216a421dcff778ed82b1f83198acd403b2938384 | 2024-02-05 20:13:48.00| 1,899,032 | 51,480,828
-199,601 | outgoing | 199,601 | 8eb504479c678b0a1d0073b3420f0b62df9ca98b7e503005f157580c010e1d0f| 2023-07-10 02:37:30.00| 1,143,600 | 33,273,450
39,735,569,942 | outgoing | 177,469 | 8ffaafe1dde6f78d6b742d94c9f1a21c892389d9fa73861211356687b751cf66| 2023-12-12 04:39:20.00| 1,692,918 | 46,672,760
(Note: results trimmed for readability)The overall aim is to track the flow of ADA for our specific address. It categorizes transactions as 'outgoing' or 'incoming' and provides details like the amount, fee, timestamp, and block information.
- When we say "outgoing", this is the address that is in the output of that transaction, and possibly the input as well (if they are receiving any change back).
- When we say "incoming", this is the address in the input part of that transaction, and not in the output part.
🔎 Let's take a closer look at each part of the query.
First Part (Before UNION)
SELECT COALESCE(sum(tx_out.value), 0) - COALESCE(inp.inp_value, 0) AS ADA_amount, 'outgoing' AS direction, ...
This SELECT statement calculates the net ADA amount involved in transactions where the given address is an output, ie. recipient.
COALESCE ensures that if sum(tx_out.value) or inp.inp_value is NULL, they are treated as 0, preventing errors.
outgoing labels these transactions as outgoing.
FROM tx_out JOIN tx ON tx.id = tx_out.tx_id ...
This FROM statement starts by looking at tx_out (opens in a new tab) (transaction outputs) and joins it with the tx (opens in a new tab)
(transactions) table, linking them based on transaction IDs.
LEFT JOIN ( SELECT ... ) AS inp ON inp.inp_txid = tx.id is a subquery aliased as inp. It looks for transactions where the given address is an input (or sender) and calculates the total input value inp_value for those transactions.
LEFT JOIN includes all rows from the left table tx (opens in a new tab) even if there's no match in the subquery inp.
JOIN block ON block.id = tx.block_id joins the block (opens in a new tab) table to get block-related information (timestamp, block number, slot number).
WHERE tx_out.address = '...' filters results to only include transactions where the specified address is an output.
GROUP BY tx_hash, ... groups the results by transaction hash and other fields to avoid duplicate rows.
UNION combines the results of the first part with the second part.
Second Part (After UNION)
SELECT sum(tx_out.value) AS ADA_amount, 'incoming' AS direction, ... calculates the total ADA amount for incoming transactions.
incoming labels these transactions as incoming. FROM tx_in JOIN tx_out ON ... JOIN tx ON ... JOIN block ON ... similar joins as in the first part, but starts from tx_in (opens in a new tab) (transaction inputs).
WHERE tx_out.address = '...' AND NOT EXISTS (SELECT 1 AS txid FROM tx_out WHERE ...) filters to include transactions where:
- The address is an input (via tx_out (opens in a new tab) linked to tx_in (opens in a new tab)).
- The address is not an output in the same transaction (using NOT EXISTS).
GROUP BY tx_hash, ... groups the results similarly to the first part.
3.2 Retrieve Transaction signatories
Next we will run a query to get the transaction signatories. :writing_hand:
For that, we will be using the transaction hash 3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7 as it has more than one signer keys.
SELECT tw."key" AS Transaction_Signatories
FROM tx
LEFT JOIN tx_witnesses tw ON tx.id = tw.tx_id
WHERE tx.hash = '3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7'Expected results format
transaction_signatories
----------------------------------------------------------------
1fdbf347cae52d54a9cf7f58e4ecf461144bbfb38a330dce3e8d66f1391bc442
55ff3b5b6603609463566057e0713cb2db512838eb864a0c15d73b377c278ae3
(2 rows)🔎 Let's take a closer look at each part of the query.
SELECT tw."key" AS Transaction_Signatories selects the key column from the tx_witnesses (opens in a new tab) table and renames it as Transaction_Signatories in the output.
FROM tx means the query is looking in the tx (opens in a new tab) table.
LEFT JOIN tx_witnesses tw ON tx.id = tw.tx_id performs a LEFT JOIN with the tx_witnesses (opens in a new tab) table.
LEFT JOIN tx_witnesses tw ON tx.id = tw.tx_id performs a LEFT JOIN with the tx_witnesses (opens in a new tab) table.
The JOIN condition tx.id = tw.tx_id connects rows from both tables where the id in the tx (opens in a new tab) table matches the tx_id in the tx_witnesses (opens in a new tab) table.
LEFT JOIN ensures that all rows from the tx (opens in a new tab) table are included in the result, even if there's no corresponding match in the tx_witnesses (opens in a new tab) table. In such cases, the columns from tx_witnesses (opens in a new tab) will have NULL values.
WHERE tx.hash = '3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7' is a filtering condition restricting the output to only those rows where the hash column in the tx (opens in a new tab) table matches the provided specific hash value.
3.3 Retrieve Native Asset details
While reviewing the above transactions, you might notice that some include more than just ADA, they also include Cardano Native Assets (opens in a new tab).
Next we will run a query get the details of a specific transaction which includes a native asset. We will use the transaction hash 3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7 because this transaction has native assets, in addtion to ada.
SELECT tx_detail.* FROM (
SELECT
'output' AS direction,
tx_out.address AS address,
sa."view" AS stake_address,
tx_out.value AS ADA_amount,
ma.name AS asset_name,
sum(mto.quantity) AS quantity,
b.time AS time_stamp,
tx.fee AS transaction_fee,
b.block_no AS block,
b.slot_no AS slot,
NULL AS utxo
FROM tx
LEFT JOIN tx_out ON tx_out.tx_id = tx.id
LEFT JOIN stake_address sa ON sa.id = tx_out.stake_address_id
LEFT JOIN ma_tx_out mto ON mto.tx_out_id = tx_out.id
LEFT JOIN multi_asset ma ON ma.id = mto.ident
LEFT JOIN block b ON b.id = tx.block_id
WHERE
tx.hash = '3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7'
GROUP BY direction, address, stake_address, ADA_amount, asset_name, time_stamp, transaction_fee, block, slot, utxo
UNION ALL
SELECT
'input' AS direction,
tx_out.address AS address,
sa."view" AS stake_address,
tx_out.value ADA_amount,
ma.name AS asset_name,
sum(mto.quantity) AS quantity,
b.time AS time_stamp,
tx.fee AS transaction_fee,
b.block_no AS block,
b.slot_no AS slot,
txin.hash AS utxo
FROM tx
LEFT JOIN tx_in ti ON ti.tx_in_id = tx.id
LEFT JOIN tx_out ON tx_out.tx_id = ti.tx_out_id AND tx_out."index" = ti.tx_out_index
LEFT JOIN tx txin ON txin.id = tx_out.tx_id
LEFT JOIN stake_address sa ON sa.id = tx_out.stake_address_id
LEFT JOIN ma_tx_out mto ON mto.tx_out_id = tx_out.id
LEFT JOIN multi_asset ma ON ma.id = mto.ident
LEFT JOIN block b ON b.id = tx.block_id
WHERE
tx.hash = '3565bb0621d56ff3349f014a41c7c4c9f5614288a4b00abbe76d3b41becc9da7'
GROUP BY direction, address, stake_address, ADA_amount, asset_name, time_stamp, transaction_fee, block, slot, utxo
) AS tx_detail
ORDER BY tx_detail.direction ASCExpected results format
direction | address | stake_addres | ada_amount | asset_name | quantity | time_stamp | transaction_fee| block | slot | utxo
-----------+------------------------------+----------------------------------+---------------+------------+----------+--------------+----------------+--------+---------+---------------
input | addr_test1qr...09cae0svmt57t | stake_test1urfvl...8juwuhclq5pxz | 4531923278317 | tDRIP | 1000020 | 2024-04-03 | 196917 | 2106415| 56425275| a55dca...ff747
(Note: results trimmed for readability)🔎 Let's zoom in for a closer look at each part of the query.
Two Subqueries Combined with UNION ALL
The main query selects everything prefixed tx_detail by combining the results of two subqueries using UNION ALL which appends the results of the second subquery to the first, without removing duplicates.
First Subquery focuses on Outputs
SELECT 'output' AS direction, ... labels each row as an 'output'.
FROM tx LEFT JOIN tx_out ... starts from the tx (opens in a new tab) table and joins with tx_out (opens in a new tab) (transaction outputs) table to get output details.
LEFT JOIN stake_address ... joins with stake_address to get associated stake address information.
LEFT JOIN ma_tx_out ... LEFT JOIN multi_asset ... joins to gather details about any native assets involved in the outputs.
LEFT JOIN block b ... joins with block (opens in a new tab) to get block-related info.
WHERE tx.hash = '...' filters to focus only on the specific transaction.
GROUP BY ... groups results to avoid redundancy, especially when multiple native assets are involved in an output.
NULL AS utxo sets the utxo column to NULL for outputs, as UTXOs are relevant to inputs.
Second Subquery focuses on Inputs
SELECT 'input' AS direction, ... labels each row as an 'input'.
FROM tx LEFT JOIN tx_in ... starts from tx (opens in a new tab) table and joins with tx_in (opens in a new tab) (transaction inputs) table.
LEFT JOIN tx_out ... LEFT JOIN tx txin ... traces back from the input to the original output it's spending, to get details about that output.
Other joins and WHERE clause are similar to the first subquery.
txin.hash AS utxo includes the hash of the transaction that created the spent output, representing the UTXO.
Main Query
SELECT tx_detail.* FROM (...) AS tx_detail selects all columns from the combined results of the subqueries.
ORDER BY tx_detail.direction ASC orders the results, showing outputs first, then inputs.
Summary
This query gives you a detailed breakdown of the inputs and outputs of the Cardano transaction we are interested in.
Next, we will run a query to fetch a breakdown list of all the native tokens (both fungible tokens and NFTs) held in the wallet. This will provide a detailed view of your various assets, allowing you to see each token or NFT along with its name and quantity.