Given is a collection of swaps with a maker (the seller) and taker (the buyer). The query below groups maker and taker into a wallet and calculates the realized profit & loss. I’d like to add a kind of wallet tracer. Meaning I like to trace token transfers from one wallet to another. Let me give you an example:
- wallet A buys 500 MAI tokens (taker)
- wallet A tranfers 100 MAI tokens to wallet B
- wallet B transfers 50 MAI tokens to wallet C
- wallet C sells 50 MAI tokens (maker)
In this case I’d like to add a field to the result of wallet A with the address of wallet B and C as wallet A was the initial buyer of the tokens. To accomplish this I have a second collection holding all token transfers. In this collection the field “from” is the sender and the field “to” is the receiver. I attached a few examples of the swaps and transfers collections below. Can someone please help my with this? Is $graphLookup the right choice?
[
{
$match: {
'tokenOut.address': '0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7',
},
},
{
$project: {
_id: 0,
wallet: '$taker',
amountSpent: '$amountWETH',
amountBought: '$amountOut',
token: '$tokenOut'
},
},
{
$unionWith: {
coll: 'swaps',
pipeline: [
{
$match: {
'tokenIn.address': '0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7',
},
},
{
$project: {
_id: 0,
wallet: '$maker',
amountEarned: '$amountWETH',
amountSold: '$amountIn',
token: '$tokenIn'
},
},
],
},
},
{
// skip document if more tokens was sold then actually bought
// note: wallet may received Airdrop or bought on CEX and couldn't therefore be tracked properly
$match: {
$expr: {
$gte: [ '$amountBought', '$amountSold' ]
}
}
},
{
$addFields: {
wallet: '$_id',
// ROI: (100 / amountSpent) * (amountEarned - amountSpent)
realizedPnL: {
$multiply: [
{ $divide: [ 100, '$amountSpent' ] },
{ $subtract: [ '$amountEarned', '$amountSpent' ] }
]
}
}
},
{
// sort documents by PnL
$sort: {
realizedPnL: -1,
},
}
]
swaps collection
[
{
"_id": "657f173c3071c0e91902a00a",
"amountIn": 2105.8161971032937,
"amountOut": 3.3154016349661806,
"amountWETH": 3.3154016349661806,
"maker": "0x9621b895f62470c8dc953f293c3fe87a64152219",
"taker": "0xdb5889e35e379ef0498aae126fc2cce1fbd23216",
"tokenIn": {
"address": "0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7",
"symbol": "MAI",
"decimals": 18
},
"tokenOut": {
"address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
"symbol": "WETH",
"decimals": 18
}
},
{
"_id": "657f173c3071c0e91902a016",
"amountIn": 3161.1238328630093,
"amountOut": 4.948696292258826,
"amountWETH": 4.948696292258826,
"maker": "0xe7fb0c4cd91fbe5fb962b54de63e360095032a9c",
"taker": "0xdb5889e35e379ef0498aae126fc2cce1fbd23216",
"tokenIn": {
"address": "0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7",
"symbol": "MAI",
"decimals": 18
},
"tokenOut": {
"address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
"symbol": "WETH",
"decimals": 18
}
},
{
"_id": "657f173c3071c0e91902a020",
"amountIn": 1203.883502154729,
"amountOut": 1.8614844473732222,
"amountWETH": 1.8614844473732222,
"maker": "0xcc7935227c5c6aee6903cc59f1c8834780f27420",
"taker": "0xcc7935227c5c6aee6903cc59f1c8834780f27420",
"tokenIn": {
"address": "0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7",
"symbol": "MAI",
"decimals": 18
},
"tokenOut": {
"address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
"symbol": "WETH",
"decimals": 18
}
},
{
"_id": "657f173e3071c0e91902a039",
"amountIn": 639.7375984833882,
"amountOut": 0.9867166261032863,
"amountWETH": 0.9867166261032863,
"maker": "0x71476002ab320540eff63662e010a5163911a8ea",
"taker": "0xdb5889e35e379ef0498aae126fc2cce1fbd23216",
"tokenIn": {
"address": "0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7",
"symbol": "MAI",
"decimals": 18
},
"tokenOut": {
"address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
"symbol": "WETH",
"decimals": 18
}
},
{
"_id": "657f173e3071c0e91902a03a",
"amountIn": 95.9218563542534,
"amountOut": 0.14741272757997212,
"amountWETH": 0.14741272757997212,
"maker": "0x472f9decd796520ed2e306ace14af590e393b16f",
"taker": "0x472f9decd796520ed2e306ace14af590e393b16f",
"tokenIn": {
"address": "0x24d73bca2bd9c3a61e99dfc7cb86d3c379ebded7",
"symbol": "MAI",
"decimals": 18
},
"tokenOut": {
"address": "0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",
"symbol": "WETH",
"decimals": 18
}
}
]
transfer collection
[
{
"from": "0x472f9decd796520ed2e306ace14af590e393b16f",
"to": "0x9621b895f62470c8dc953f293c3fe87a64152219",
"amount": 2105.8161971032937
},
{
"from": "0xdb5889e35e379ef0498aae126fc2cce1fbd23216",
"to": "0xcc7935227c5c6aee6903cc59f1c8834780f27420",
"amount": 3161.1238328630093
}
]