-1

I've been playing around with Dune Analytics but can't seem to get this to work!

I've managed to filter a wallet to display the transactions showing the number of tokens sold and the corresponding BNB received (it was working before I started mucking around with it).

The issue that I am having is joining the corresponding BNB price on that day (I think I need to do an INNER JOIN function).

Essentially I'd like to have four columns DATE | No. Drip Tokens Sold | BNB Received | BNB Price (on that day)

I've come completely stuck on this last part!

WITH Drip_Data AS
(SELECT
  "bnb_amount" / 1e18 AS BNB_Received_Raw,
  "token_amount" / 1e18 AS Drip_Sold,
  DATE_TRUNC('day', evt_block_time) AS day
FROM
  drip."fountain_evt_onBnbPurchase"
WHERE
  buyer = '\xFdD4D2e4e3b344429107590c20BCf4B88281Da33'
),
BNB_Data AS (
SELECT BNB_USD_PRICE FROM dune_user_generated."bnb_price" AS BNB),

Thank you!

k06a
  • 17,755
  • 10
  • 70
  • 110
Sasha Gorgov
  • 1
  • 1
    Please [edit] your question to include representative sample source data and the desired result. – PM 77-1 Aug 19 '22 at 17:33
  • Sorry, I'm not really sure how to do this. It's on Dune Analytics, basically just spits out a table. The source data is crypto transactions I guess? – Sasha Gorgov Aug 19 '22 at 17:39
  • Run `SELECT * FROM dune_user_generated."bnb_price"`and you see the data and how the date column is named and which data it holds, the same goes for the other, as we can't see the data we can't help you but you nee dto join both tables, but without seeing the table struture and the data we can't help – nbk Aug 19 '22 at 17:43
  • You seem to have two tables of data and need to combine them in a result set. We would like to see what this data looks like (does not have to be your *real data*) and what you want in the end. – PM 77-1 Aug 19 '22 at 17:48

1 Answers1

1

You are completely correct in that you need the do a JOIN for the BNB price data! Here I've chosen to do LEFT JOIN

WITH drip_data AS
(
SELECT
    "bnb_amount" / 1e18 AS BNB_Received_Raw
    , "token_amount" / 1e18 AS Drip_Sold
    , DATE_TRUNC('day', evt_block_time) AS time
FROM
    drip."fountain_evt_onBnbPurchase"
WHERE
    buyer = '\xFdD4D2e4e3b344429107590c20BCf4B88281Da33'
)

, bnb_data AS 
(
SELECT 
    time
    , "bnb_usd_price"
FROM dune_user_generated."bnb_price"
)

SELECT 
    d.time as "DATE"
    , d.Drip_Sold as "No. Drip Tokens Sold"
    , d.BNB_Received_Raw as "BNB Received"
    , b.bnb_usd_price as "BNB Price"
FROM drip_data d
LEFT JOIN bnb_data b
ON d.time = b.time

I saved this query on Dune as well

Hopefully this is what you are looking for!

agaperste
  • 11
  • 1