I have a query with several sub-queries added on using Cross Joins. This tracks purchases, sales, proceeds and profit.
It works perfectly if there's purchases and sales, but when there's only purchases and no sales, it falls apart. The query returns nothing, instead of just showing for example 1,000,000 tokens bought and 0 tokens sold.
CROSS JOIN
(select
Sum(CASE
WHEN ttsfm.contract_address = '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3' then ttsfm.value/1000
ELSE ttsfm.value
END)/1e9 AS Safemoon_sold,
sum(distinct(ttbnb.value)/1e18) as BNB_proceeds, sum(distinct(pusd.price*ttbnb.value)/1e18) as USD_Proceeds, count(distinct(tx.hash)) as Sales
from bsc.transactions tx
left join bep20."BEP20_evt_Transfer" ttsfm on tx.hash = ttsfm.evt_tx_hash
left join bep20."BEP20_evt_Transfer" ttbnb on tx.hash = ttbnb.evt_tx_hash
left join prices.usd pusd on date_trunc('minute', tx.block_time) = pusd.minute
where ttsfm.contract_address in ('\x42981d0bfbaf196529376ee702f2a9eb9092fcb5', '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3') --V2 & V1 contract addresses
and ttbnb.contract_address = '\xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c'
and tx.from in ('{{Wallet}}') --Owner Wallet Address
and ttsfm.from in ('{{Wallet}}') --Owner Wallet Address
and pusd.symbol = 'WBNB'
and left(tx.data::varchar, 10) in ('\x18cbafe5', '\x4a25d94a', '\x791ac947', '\x8803dbee', '\x5c11d795', '\x38ed1739')
group by tx."from") as Sells
CROSS JOIN
Where do I add coalesce in here? There are four columns I'm returning - Safemoon_Sold, BNB_proceeds, USD_Proceeds and Sales.
I think the issue is that I'm finding the sum of all sales.
The below is my entire query. It finds all purchases of a scam cryptocurrency, how much BNB was spent to get it, how much that BNB is worth, how many sales there are. How much Safemoon was sold, how much BNB was returned, how much that returned BNB is worth, how many sales there have been, how many transfers in and transfers out there have been, what the current balance is, and then finally the profit/loss figure & the percentage gained/lost.
with Calcs as (
SELECT * FROM
(select tx."from", Sum(CASE
WHEN tt.contract_address = '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3' then tt.value/1000
ELSE tt.value
END)/1e9 AS Safemoon_Bought, sum(ttbnb.value/1e18) as BNB_Spent, sum(pusd.price*ttbnb.value/1e18) as TxCosts, count(tx.from) as purchases
from bsc.transactions tx
left join bep20."BEP20_evt_Transfer" tt on tx.hash = tt.evt_tx_hash
left join bep20."BEP20_evt_Transfer" ttbnb on tx.hash = ttbnb.evt_tx_hash
left join prices.usd pusd on date_trunc('minute', tx.block_time) = pusd.minute
where tt.contract_address in ('\x42981d0bfbaf196529376ee702f2a9eb9092fcb5', '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3') --V2 & V1 contract addresses
and ttbnb.contract_address = '\xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c'
and tx.from in('{{Wallet}}') --Owner Wallet Address
and tt.to in ('{{Wallet}}') --Owner Wallet Address
and pusd.symbol = 'WBNB'
and left(tx.data::varchar, 10) not in ('\x454b0608', '\xbaa2abde')
group by tx."from") AS Buys
CROSS JOIN
coalesce(select
Sum(CASE
WHEN ttsfm.contract_address = '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3' then ttsfm.value/1000
ELSE ttsfm.value
END)/1e9 AS Safemoon_sold,
sum(distinct(ttbnb.value)/1e18) as BNB_proceeds, sum(distinct(pusd.price*ttbnb.value)/1e18) as USD_Proceeds, count(distinct(tx.hash)) as Sales
from bsc.transactions tx
left join bep20."BEP20_evt_Transfer" ttsfm on tx.hash = ttsfm.evt_tx_hash
left join bep20."BEP20_evt_Transfer" ttbnb on tx.hash = ttbnb.evt_tx_hash
left join prices.usd pusd on date_trunc('minute', tx.block_time) = pusd.minute
where ttsfm.contract_address in ('\x42981d0bfbaf196529376ee702f2a9eb9092fcb5', '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3') --V2 & V1 contract addresses
and ttbnb.contract_address = '\xbb4CdB9CBd36B01bD1cBaEBF2De08d9173bc095c'
and tx.from in ('{{Wallet}}') --Owner Wallet Address
and ttsfm.from in ('{{Wallet}}') --Owner Wallet Address
and pusd.symbol = 'WBNB'
and left(tx.data::varchar, 10) in ('\x18cbafe5', '\x4a25d94a', '\x791ac947', '\x8803dbee', '\x5c11d795', '\x38ed1739')
group by tx."from"),0) as Sells
CROSS JOIN
(select Sum(CASE
WHEN ttin.contract_address = '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3' then ttin.value/1000
ELSE ttin.value
END)/1e9 AS TransfersIn,
Sum(CASE
WHEN ttout.contract_address = '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3' then ttout.value/1000
ELSE ttout.value
END)/1e9 AS TransfersOut
from bsc.transactions tx
left join bep20."BEP20_evt_Transfer" ttin on tx.hash = ttin.evt_tx_hash
left join bep20."BEP20_evt_Transfer" ttout on tx.hash = ttout.evt_tx_hash
where ttin.contract_address in ('\x42981d0bfbaf196529376ee702f2a9eb9092fcb5', '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3') --V2 & V1 contract addresses
and ttout.contract_address in ('\x42981d0bfbaf196529376ee702f2a9eb9092fcb5', '\x8076C74C5e3F5852037F31Ff0093Eeb8c8ADd8D3') --V2 & V1 contract addresses
and ttin.to in ('{{Wallet}}') --Owner Wallet Address
and ttout.to in ('{{Wallet}}') --Owner Wallet Address
and ttin.from <> '\x678ee23173dce625a90ed651e91ca5138149f590' --V2Deployer
and ttout.to <> '\x678ee23173dce625a90ed651e91ca5138149f590' --V2Deployer
and left(tx.data::varchar, 10) = '\xa9059cbb'
) as Transfers
CROSS JOIN
(with
transfers AS (
--Inflow
SELECT "value" as amount
FROM bep20."BEP20_evt_Transfer"
WHERE "to" in ('{{Wallet}}') -- Owner Wallet
AND "contract_address" = '\x42981d0bfbaf196529376ee702f2a9eb9092fcb5' -- V2
UNION ALL
--Outflow
SELECT -"value"/0.96 as amount
FROM bep20."BEP20_evt_Transfer"
WHERE "from" in ('{{Wallet}}') -- Owner Wallet
AND "contract_address" = '\x42981d0bfbaf196529376ee702f2a9eb9092fcb5' -- V2
)
SELECT
CASE when sum(t.amount/1e9) <0 then 0
else sum(t.amount/1e9)
end as balance
FROM transfers t) as Balances
)
select *, (usd_proceeds+(Balance*0.00038)) - txcosts as Profit, (usd_proceeds+(balance*0.00038)-txcosts)/txcosts as Percentage from Calcs
Thank you!
EDIT: I think the issue here is that there are 4 columns I'm taking from the "Sales" cross join. When I delete 3 of them and run Coalesce, it returns a 0 as expected. So how do I coalesce a subquery with multiple columns?