-- 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 >= '{{target_date}}'

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

AND event_name = 'CreateIncreasePosition'

AND tx_status = 'SUCCESS'

ORDER BY block_timestamp

),

pear_accounts AS (

SELECT

block_timestamp AS execute_timestamp

, event_name

, decoded_log:account AS new_pear_account

, decoded_log:collateralToken AS collateral_token

, decoded_log:indexToken AS index_token

, name

, symbol