SELECT

RECORDED_hour,

currency,

price

FROM osmosis.price.ez_prices

qualify row_number()over (partition by currency order by recorded_hour DESC) = 1

SELECT

date,

pool_ids,

n_action,

n_l_provider,

sum(net) over (ORDER BY date) as Liquidity,

AVG(n_l_provider) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as moving_avarage_7,

AVG(n_l_provider) over (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as moving_avarage_30

FROM (

SELECT date_trunc('d', block_timestamp) as date,

pool_ids,

round(sum(amount_usd),2) as net,

count(DISTINCT tx_id) as n_action,

count(DISTINCT LIQUIDITY_PROVIDER_ADDRESS) as n_l_provider

FROM (

SELECT pool_id[0] as pool_ids,

(amount/pow(10,decimal))*b.price as amount_usd,

tx_id,

block_timestamp,

LIQUIDITY_PROVIDER_ADDRESS

from osmosis.defi.fact_liquidity_provider_actions a

LEFT JOIN token_price b using(currency)

WHERE action = 'pool_joined'

AND pool_id[0] ilike '{{pool_id}}'

AND tx_succeeded = 'true'