SELECT

decoded_log:subject AS subject,

decoded_log:supply AS supply,

BLOCK_TIMESTAMP AS evt_block_time,

ROW_NUMBER() OVER (PARTITION BY subject ORDER BY evt_block_time DESC) AS rn

FROM

base.core.fact_decoded_event_logs

WHERE

contract_address = '0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4'

AND

block_timestamp > '2023-08-09'

),

prices AS (

SELECT

subject,

POW(CAST(supply AS DOUBLE), 2) / 16000.0 AS share_price

FROM

ranked_trades

WHERE

rn = 1

),

share_stats AS (

SELECT

decoded_log:trader AS trader,

decoded_log:subject AS subject,

SUM(CASE WHEN decoded_log:isBuy::string = true THEN decoded_log:shareAmount ELSE 0 END) AS shares_bought,

SUM(CASE WHEN decoded_log:isBuy::string = false THEN decoded_log:shareAmount ELSE 0 END) AS shares_sold,

SUM(CASE WHEN decoded_log:isBuy::string = true THEN decoded_log:shareAmount ELSE 0 END) - SUM(CASE WHEN decoded_log:isBuy::string = false THEN decoded_log:shareAmount ELSE 0 END) AS shares_held,

SUM(CASE WHEN decoded_log:isBuy::string = true AND trader = subject THEN decoded_log:shareAmount ELSE 0 END) - SUM(CASE WHEN decoded_log:isBuy::string = false AND trader = subject THEN decoded_log:shareAmount ELSE 0 END) AS self_shares_held

FROM

base.core.fact_decoded_event_logs

WHERE

contract_address = '0xcf205808ed36593aa40a44f10c7f7c2f67d4a4d4'

GROUP BY

trader, subject