Beginner SQL / Dune Analytics

Question

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!

Answer 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!