SELECT

DATE(RECORDED_AT) AS DATE,

SYMBOL,

AVG(PRICE) AS DAILY_PRICE

FROM osmosis.core.dim_prices

GROUP BY 1,2

ORDER BY 1)

SELECT

BLOCK_TIMESTAMP,

TX_ID,

LIQUIDITY_PROVIDER_ADDRESS,

ACTION,

POOL_ID,

CASE WHEN PROJECT_NAME = 'INJ' THEN AMOUNT/POW(10,18) ELSE AMOUNT/POW(10,DECIMAL) END AS JUSTIFIED_AMOUNT,

CASE WHEN PROJECT_NAME = 'INJ' THEN AMOUNT*DAILY_PRICE/POW(10,18) ELSE AMOUNT*DAILY_PRICE/POW(10,DECIMAL) END AS USD_AMOUNT,

CURRENCY,

PROJECT_NAME

FROM osmosis.core.fact_liquidity_provider_actions

FULL JOIN osmosis.core.dim_labels ON CURRENCY = ADDRESS

FULL JOIN PRICE_TABLE ON SYMBOL = PROJECT_NAME AND DATE = BLOCK_TIMESTAMP::DATE

WHERE TX_STATUS = 'SUCCEEDED'

AND PROJECT_NAME NOT IN ('IOV')),

tab0 as (

select

TX_ID,

count(*) as "Number of Proivdings"

from MAINTABLE

where action = 'pool_joined'

group by 1

order by 2),

all_join_pool_actions as (