SELECT

block_timestamp::date as date, nft_id as hero, price,

CASE WHEN currency = 'A.ead892083b3e2c6c.DapperUtilityCoin' THEN 'USD' ELSE '??' END as currency,

CASE WHEN buyer = '{{player_address}}' THEN 'self' else buyer END as buyer,

CASE WHEN seller = '{{player_address}}' THEN 'self' else seller END as seller,

CASE WHEN marketplace = 'A.b8ea91944fd51c43.OffersV2' THEN 'Offer' ELSE 'Market' END as type,

CASE WHEN block_timestamp::date<'2023-02-04' THEN price*0.15 ELSE price*0.135 END as fee_collected,

CONCAT('https://flowscan.org/transaction/', tx_id) as tx

FROM flow.core.ez_nft_sales

WHERE (buyer = '{{player_address}}' OR seller = '{{player_address}}')

AND nft_collection = 'A.e3ad6030cbaff1c2.DimensionX'),

grouped_trades as (

SELECT date,

CASE WHEN buyer='self' THEN count(hero) else 0 END as heroes_bought,

CASE WHEN buyer='self' THEN sum(price) else 0 END as spent,

CASE WHEN seller='self' THEN count(hero) else 0 END as heroes_sold,

CASE WHEN seller='self' THEN sum(price-fee_collected) else 0 END as revenue

FROM trades

GROUP BY date,buyer,seller

),

daily_trades as (

SELECT date,

sum(heroes_bought) as heroes_bought, sum(spent) as expenses,

sum(heroes_sold) as heroes_sold, sum(revenue) as revenue

FROM grouped_trades

GROUP BY date)

SELECT

date,

(sum(revenue) over (order by date ASC))-(sum(expenses) over (order by date ASC)) as net_profit,

sum(expenses) over (order by date ASC) as total_expenses,

sum(revenue) over (order by date ASC) as total_revenue,

expenses, heroes_bought, revenue, heroes_sold

FROM daily_trades

ORDER BY date DESC