FROM near.core.fact_Transfers

WHERE status = TRUE

AND (tx_signer LIKE '%sweat_oracle_%'

OR tx_signer = 'sweat_welcome.near')

AND NEAR < 0.06),

logs AS (SELECT block_timestamp, tx_hash, status_value, replace(value, 'EVENT_JSON:') as json, regexp_substr(status_value, 'Success') as reg_success,

try_parse_json(json):standard as standard, try_parse_json(json):event as event, try_parse_json(json):data as data_logs

FROM near.core.fact_receipts, table(flatten(input => logs))

WHERE 1=1

AND block_timestamp > '2022-09-12'

AND receiver_id = 'token.sweat'

AND reg_success is not null ),

sweat_transfer AS (SELECT block_timestamp, tx_hash, standard, event, split(value:memo, ' ') as memo, value:amount/pow(10,18) as amount, value:owner_id as owner_id,

value:old_owner_id as old_owner_id, value:new_owner_id as new_owner_id, nvl(old_owner_id, 'mint') as from_address, nvl(new_owner_id, owner_id) as to_address

FROM logs, table(flatten(input => data_logs))

WHERE amount > 0 )

SELECT date(block_timestamp) as day,

case when old_owner_id IN (SELECT distinct sweat_wallets FROM sweat_wallets) then 'Sweat Wallet'

else 'Other Wallets'

end as wallet_type,

count(distinct old_owner_id) as "Wallets", count(distinct new_owner_id) as receivers_sweat, sum(amount) as "SWEAT"

FROM sweat_transfer

WHERE 1=1

AND event = 'ft_transfer'

AND block_timestamp > '2022-09-14'

AND new_owner_id !='deposits.grow.sweat'

AND CURRENT_DATE - date(block_timestamp) < 7

AND memo[0] != 'Claiming'

GROUP BY 1,2

ORDER By 1,2 DESC