<p>Here is a query that will detect whether a spacebudz nft has changed ownership. It uses a stake address if one exists and otherwise uses the address.</p>
<p>It runs in ~2 min for me when the time is set to 10 days ago.</p>
<pre class="lang-sql prettyprint-override"><code>with
tx_id_limit as (
  select tx.id as tx_id
  from block
    join tx on tx.block_id = block.id
  where time &gt; '2024-03-10 00:00:00+00'
  order by tx_id asc
  limit 1
)

, stxo as (
  select  stxo.tx_out_id
     ,    stxo.tx_in_id
     ,    case
            when stake_address.view is not null then stake_address.view
            else stxo.address
          end as address
     ,    stxo.fingerprint
  from (
    select  tx_out.tx_id as tx_out_id
        ,   tx_in.tx_in_id as tx_in_id
        ,   tx_out.address
        ,   tx_out.stake_address_id
        ,   multi_asset.fingerprint
    from tx_out
    join tx_in on tx_in.tx_out_id = tx_out.tx_id and tx_in.tx_out_index = tx_out.index
    join ma_tx_out on ma_tx_out.tx_out_id = tx_out.id
    join multi_asset on multi_asset.id = ma_tx_out.ident
    where tx_out.tx_id &gt; (select tx_id from tx_id_limit)
      and multi_asset.policy = '\x4523c5e21d409b81c95b45b0aea275b8ea1406e6cafea5583b9f8a5f'
      and tx_in.id is not null
  ) as stxo
  left join stake_address on stake_address.id = stake_address_id
)


select  stxo_1.fingerprint
    ,   stxo_1.address as from_address
    ,   stxo_2.address as to_address
    ,   encode(tx.hash, 'hex') as tx_hash
from stxo as stxo_1
join stxo as stxo_2 on stxo_2.tx_out_id = stxo_1.tx_in_id
join tx on tx.id = stxo_1.tx_in_id
where stxo_1.fingerprint = stxo_2.fingerprint
  and stxo_1.address &lt;&gt; stxo_2.address
</code></pre>
<p>This will probably not work as intended for fungible assets. For that you have to treat the input and output addresses as sets (since there can be multiple addresses for a single asset) and then check that the input set and output set are different.</p>

CC BY-SA 4.0
2024-03-20T01:08:36.660Z
Very cool - I am going to try it out now! Thank you.
First question: I keep the last tx.id processed, and use that as my constraint, but you have used time. Is there a specific reason you chose to use time from the block table?
RelevantData
CC BY-SA 4.0
2024-03-20T22:16:03.917Z
Nah I just use time so I have a better sense of how far back in the chain I'm going. tx_id would be more efficient here but is less ergonomic imo
adjuric
CC BY-SA 4.0
2024-03-19T22:11:41.883Z
2024-03-19T22:18:40.253Z
2024-03-19T22:18:40.253Z
adjuric
<p>This is a non-trivial task. You can't quickly fetch the latest tx that meets this criteria. Postgres will first need to compute whether the criteria has been met across all txs for that asset. Then it will order them and return the latest one.</p>
<p>You have a few options:</p>
<ol>
<li>You can improve performance by selecting a timeframe (subset of latest txs). You then have to balance the risk returning of no results with the speed of the query ie the longer the timeframe the slower the query.</li>
<li>You can write a postgres function that will iterate a day at time in reverse order until it finds a matching tx. This may still be quite slow especially if you are trying to load results for a web page.</li>
<li>You can create a materialized view that maps assets to txs if they meet this criteria ie a table with columns <code>asset_id, tx_id, time</code>. This is probably your only option if you are doing this for a user-facing app. You then backfill the table with your relevant data and need a way to update the table with the new data coming in.</li>
</ol>

CC BY-SA 4.0
2024-03-19T02:25:14.223Z
Agreed, and in fact I do have a tx id I use for limiting the scope in time:

and tx_id > {X}

this does reduce the amount of work required, agreed!

The thing I need help with most though is what is it in the schema/data that would definitively identify a given transaction as one where any asset of the given policy has changed ownership.
RelevantData
CC BY-SA 4.0
2024-03-19T04:54:48.280Z
Thanks btw @adjuric.

For your #1: 
I do constrain by time, typically only querying for records of the last hour, constrained by using: and tx.id > X

It is not for a web UI per-se, it is for extracting the transactions I am interested in, and then storing/updating data in a separate DB.

The part I need help on is determining for certain how to identify that a tx is one where an asset of the given policy changed ownership.

There is the asset being on an input UTXO on one address, and then on another as an output ...
RelevantData
CC BY-SA 4.0
2024-03-18T20:20:55.123Z
2024-03-18T20:20:55.123Z
adjuric
<p>How can I return the latest transaction where any asset of a given policy changed address/ownership?</p>
<p>Here is a query which returns me the most recent transaction which included an asset of a given policy in it's utxo's:</p>
<pre><code>select encode(tx.hash::bytea, 'hex') as hash 
from ma_tx_out as matxo 
join multi_asset as ma on matxo.ident = ma.id 
join tx_out as txo on matxo.tx_out_id = txo.id 
join tx as tx on txo.tx_id = tx.id 
left join tx_in on tx_in.tx_out_id = txo.tx_id and tx_in.tx_out_index = txo.index 
where tx_in.id is null and ma.policy = '\x{policy_id}' 
order by txo.id desc limit 1;
</code></pre>
<p>What I would like is similar but also very much not the same, and every way I try to do it turns into a VERY long running query, so I am reaching out here for help.</p>
<p>I want only transactions where any asset of the given policy changed address - i.e. changed ownership, moved to another wallet, was sent to a script address, etc.
So I am trying by differentiating on address of asset in an input utxo being on a different address in an output utxo but I think I may be misunderstanding how things are represented in the schema.</p>

<cardano-db-sync>
How to query latest transaction for a policy where an asset of that policy changed address/ownership
# How to query latest transaction for a policy where an asset of that policy changed address/ownership

(Asked by: username_0 on 2024-03-18)

How can I return the latest transaction where any asset of a given policy changed address/ownership?
Here is a query which returns me the most recent transaction which included an asset of a given policy in it's utxo's:
select encode(tx.hash::bytea, 'hex') as hash 
from ma_tx_out as matxo 
join multi_asset as ma on matxo.ident = ma.id 
join tx_out as txo on matxo.tx_out_id = txo.id 
join tx as tx on txo.tx_id = tx.id 
left join tx_in on tx_in.tx_out_id = txo.tx_id and tx_in.tx_out_index = txo.index 
where tx_in.id is null and ma.policy = '\x{policy_id}' 
order by txo.id desc limit 1;

What I would like is similar but also very much not the same, and every way I try to do it turns into a VERY long running query, so I am reaching out here for help.
I want only transactions where any asset of the given policy changed address - i.e. changed ownership, moved to another wallet, was sent to a script address, etc.
So I am trying by differentiating on address of asset in an input utxo being on a different address in an output utxo but I think I may be misunderstanding how things are represented in the schema.

**Tags:** cardano-db-sync

---

## 2 answers

### Answer by username_1 on 2024-03-19. Score: 2

Here is a query that will detect whether a spacebudz nft has changed ownership. It uses a stake address if one exists and otherwise uses the address.
It runs in ~2 min for me when the time is set to 10 days ago.
with
tx_id_limit as (
  select tx.id as tx_id
  from block
    join tx on tx.block_id = block.id
  where time > '2024-03-10 00:00:00+00'
  order by tx_id asc
  limit 1
)

, stxo as (
  select  stxo.tx_out_id
     ,    stxo.tx_in_id
     ,    case
            when stake_address.view is not null then stake_address.view
            else stxo.address
          end as address
     ,    stxo.fingerprint
  from (
    select  tx_out.tx_id as tx_out_id
        ,   tx_in.tx_in_id as tx_in_id
        ,   tx_out.address
        ,   tx_out.stake_address_id
        ,   multi_asset.fingerprint
    from tx_out
    join tx_in on tx_in.tx_out_id = tx_out.tx_id and tx_in.tx_out_index = tx_out.index
    join ma_tx_out on ma_tx_out.tx_out_id = tx_out.id
    join multi_asset on multi_asset.id = ma_tx_out.ident
    where tx_out.tx_id > (select tx_id from tx_id_limit)
      and multi_asset.policy = '\x4523c5e21d409b81c95b45b0aea275b8ea1406e6cafea5583b9f8a5f'
      and tx_in.id is not null
  ) as stxo
  left join stake_address on stake_address.id = stake_address_id
)


select  stxo_1.fingerprint
    ,   stxo_1.address as from_address
    ,   stxo_2.address as to_address
    ,   encode(tx.hash, 'hex') as tx_hash
from stxo as stxo_1
join stxo as stxo_2 on stxo_2.tx_out_id = stxo_1.tx_in_id
join tx on tx.id = stxo_1.tx_in_id
where stxo_1.fingerprint = stxo_2.fingerprint
  and stxo_1.address <> stxo_2.address

This will probably not work as intended for fungible assets. For that you have to treat the input and output addresses as sets (since there can be multiple addresses for a single asset) and then check that the input set and output set are different.

> **Comments:**
> **username_0**: Very cool - I am going to try it out now! Thank you. First question: I keep the last tx.id processed, and use that as my constraint, but you have used time. Is there a specific reason you chose to use time from the block table?  
> **username_1**: Nah I just use time so I have a better sense of how far back in the chain I'm going. tx_id would be more efficient here but is less ergonomic imo  

---

### Answer by username_1 on 2024-03-18. Score: 1

This is a non-trivial task. You can't quickly fetch the latest tx that meets this criteria. Postgres will first need to compute whether the criteria has been met across all txs for that asset. Then it will order them and return the latest one.

You have a few options:

1. You can improve performance by selecting a timeframe (subset of latest txs). You then have to balance the risk returning of no results with the speed of the query ie the longer the timeframe the slower the query.
2. You can write a postgres function that will iterate a day at time in reverse order until it finds a matching tx. This may still be quite slow especially if you are trying to load results for a web page.
3. You can create a materialized view that maps assets to txs if they meet this criteria ie a table with columns `asset_id, tx_id, time`. This is probably your only option if you are doing this for a user-facing app. You then backfill the table with your relevant data and need a way to update the table with the new data coming in.

> **Comments:**
> **username_0**: Agreed, and in fact I do have a tx id I use for limiting the scope in time:  and tx_id > {X}  this does reduce the amount of work required, agreed!  The thing I need help with most though is what is it in the schema/data that would definitively identify a given transaction as one where any asset of the given policy has changed ownership.  
> **username_0**: Thanks btw @adjuric.  For your #1:  I do constrain by time, typically only querying for records of the last hour, constrained by using: and tx.id > X  It is not for a web UI per-se, it is for extracting the transactions I am interested in, and then storing/updating data in a separate DB.  The part I need help on is determining for certain how to identify that a tx is one where an asset of the given policy changed ownership.  There is the asset being on an input UTXO on one address, and then on another as an output ...  

---

