SELECT

date_trunc('day', a.block_timestamp) as ddate,

a.tx_hash,

receipt_index,

receiver_id,

tx_signer,

tx_receiver,

logs,

method_name,

CASE

WHEN logs[0] ilike 'swapped%' THEN 'swaps'

WHEN method_name ilike '%liquid%' THEN method_name

ELSE 'other methods' END as cat,

tx

from near.core.fact_receipts a

LEFT JOIN near.core.fact_transactions t ON a.tx_hash = t.tx_hash

LEFT JOIN near.core.fact_actions_events_function_call f ON a.tx_hash=f.tx_hash

WHERE 1=1

AND receiver_id='v2.ref-finance.near'

--AND (a.tx_hash = '9PTe5i2iV7CgHdHFj5zyL7TTRATKege6bG3z7PzD2VGN' OR a.tx_hash='zzwK6JTxtSNFhWX2k7gfvuA7ZgfkxrfokpS4F71y9K8')

--AND (tx_receiver = 'v2.ref-finance.near' OR args LIKE '%\"receiver_id\":\"v2.ref-finance.near\"%')

AND tx not ilike '%"status":{"Failure"%'

COUNT(DISTINCT tx_hash) as totals,

COUNT(DISTINCT tx_signer) as users,

cat,

ddate

from base

GROUP BY ddate,cat ORDER BY ddate ASC