-- forked from 1.6 Pear Trade Status (Total opened, Total Closed, Total Liquidated, Total Trades open) @ https://flipsidecrypto.xyz/edit/queries/0585b804-b93a-4784-ae06-04180c73138a

with pear_opens AS (

-- get all tx initiated from the Pear app

-- contract address = '0x75f688604a58c720e7e4496139765498a2563c78'

SELECT

block_timestamp AS request_timestamp

, origin_from_address

, decoded_log:indexToken AS index_token

-- , name

-- , symbol

, decoded_log:amountIn / pow(10, 6) AS request_collateral

, decoded_log:isLong::boolean AS is_long

, decoded_log:account AS new_pear_account

, tx_hash AS request_tx_hash

, decoded_log AS request_decoded_log

FROM arbitrum.core.ez_decoded_event_logs

-- LEFT JOIN arbitrum.core.dim_contracts on index_token = address

WHERE block_timestamp::date >= '2023-09-30'

AND origin_to_address = '0x75f688604a58c720e7e4496139765498a2563c78' -- Pear Contract Address

-- AND origin_to_address = lower('0x4641D73523ABeeB0F83206DFf5E658692FCB1964')

--AND origin_to_address = lower('0x1DDCa7eaec57d24561e3Ba9A5A0c043868127018')

AND event_name = 'CreateIncreasePosition'

AND tx_status = 'SUCCESS'

ORDER BY block_timestamp