SELECT

date_trunc('day', block_timestamp) AS date,

SUM(CASE WHEN f.value:parsed:type = 'transferChecked'

THEN f.value:parsed:info:tokenAmount:uiAmount

WHEN f.value:parsed:type = 'transfer'

THEN f.value:parsed:info:amount / pow(10, 9)

ELSE 0 END) AS fee

FROM solana.core.fact_events,

LATERAL FLATTEN(input => inner_instruction:instructions) AS f

WHERE 1 = 1

AND block_timestamp >= '2024-06-01'

AND succeeded

AND program_id IN ('JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4', 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR')

AND f.value:parsed:info:destination = '6u2N4aM4XDSQHrKuUYyFpRiJEidfqYsmQ8WRAbVHMNoB'

GROUP BY 1

),

latest_price AS (

SELECT

hour::date AS date,

price

FROM solana.price.ez_prices_hourly

WHERE token_address = 'jupSoLaHXQiZZTSfEWMTRRgpnyFm8f6sZdosWBjx93v'

ORDER BY hour DESC

LIMIT 1

r.date AS date,

r.fee AS fee,

r.fee * p.price AS fee_in_usd

revenue r,

latest_price p