date(block_timestamp) as date,

event_attributes:"0_contract_address" as token,

address_name as token_name,

sum(event_attributes:"amount"/pow(10,6)) as psi_tokens_sold,

sum(event_attributes:"return_amount"/pow(10,6)) as sell_volume,

sell_volume/psi_tokens_sold as PSI_price,

count(distinct tx_id) as tx_count

FROM terra.msg_events

LEFT JOIN terra.labels ON address = event_attributes:"0_contract_address"

WHERE event_type = 'from_contract'

AND event_attributes:"1_contract_address" IN

('terra1v4kpj65uq63m4x0mqzntzm27ecpactt42nyp5c', 'terra106a00unep7pvwvcck4wylt4fffjhgkf9a0u6eu', 'terra163pkeeuwxzr0yhndf8xd2jprm9hrtk59xf7nqf', 'terra1gm5p3ner9x9xpwugn9sp6gvhd0lwrtkyrecdn3',

'terra1e59utusv5rspqsu8t37h5w887d9rdykljedxw0', 'terra1zkyrfyq7x9v5vqnnrznn3kvj35az4f6jxftrl2', 'terra1xj2w7w8mx6m2nueczgsxy2gnmujwejjeu2xf78',

'terra1l7xu2rl3c7qmtx3r5sd2tz25glf6jh8ul7aag7', 'terra1yjg0tuhc6kzwz9jl8yqgxnf2ctwlfumnvscupp', 'terra1jzqlw8mfau9ewr7lufqkrpgfzk4legz9zx306p',

'terra1pn20mcwnmeyxf68vpt3cyel3n57qm9mp289jta', 'terra1hqnk9expq3k4la2ruzdnyapgndntec4fztdyln', 'terra1etdkg9p0fkl8zal6ecp98kypd32q8k3ryced9d',

'terra19pg6d7rrndg4z4t0jhcd7z9nhl3p5ygqttxjll', 'terra1tn8ejzw8kpuc87nu42f6qeyen4c7qy35tl8t20', 'terra1mz0p4wzz5tmethu7rca2jjrw077hv2ypj7h06z',

'terra12mzh5cp6tgc65t2cqku5zvkjj8xjtuv5v9whyd' , 'terra178jydtjvj4gw8earkgnqc80c3hrmqj4kw2welz' )

AND event_attributes:"ask_asset" = 'uusd'

AND tx_status = 'SUCCEEDED'

and block_timestamp >= CURRENT_DATE - 90

GROUP BY 1,2,3),

luna_p as (select date(block_timestamp) as dt, avg(price_usd) as luna_price

from terra.oracle_prices

where symbol = 'LUNA' and block_timestamp >= CURRENT_DATE - 90

group by 1)

select a.*, b.luna_price,

- 100.0 * (1 - LEAD(a.psi_price) OVER (ORDER BY a.date) / a.psi_price) AS percentage_diff

from raw A

left join luna_p b on a.date = b.dt

where a.token_name = 'PSI'