DATE_TRUNC('day', block_timestamp) AS txn_date,

CASE

WHEN to_address = '0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee' THEN 'Game Daily Log'

WHEN to_address = '0xf083289535052e8449d69e6dc41c0ae064d8e3f6' THEN 'Pixels Farmland'

WHEN to_address = '0x7ac8de4b2d37ac6136be285ee7060e8676c9644f' THEN 'Guild Membership'

WHEN to_address = '0xb806028b6ebc35926442770a8a8a7aeab6e2ce5c' THEN 'Pixel Pets'

WHEN to_address = '0x7eae20d11ef8c779433eb24503def900b9d28ad7' THEN 'Pixel Tokens'

WHEN to_address = '0x0e6bce8f215c52b53fad009e3bfacbb3be3774a6' THEN 'Pixel Dungeons Rewards'

WHEN to_address = '0x36278457936460c0e7429aebf1bccd5061e1d0c1' THEN 'Pixel Dungeons Shop'

WHEN to_address = '0x263eebee6ced9ff47fc9208cd461114e79a03f27' THEN 'Multi Send'

END AS contract_name,

SUM(tx_fee) AS total_gas_used,

AVG(tx_fee) AS avg_gas_price

FROM ronin.core.fact_transactions

WHERE to_address IN (

'0x02790f32ad7e7eaaecfb0ad21950829932f1a2ee',

'0xf083289535052e8449d69e6dc41c0ae064d8e3f6',

'0x7ac8de4b2d37ac6136be285ee7060e8676c9644f',

'0xb806028b6ebc35926442770a8a8a7aeab6e2ce5c',

'0x7eae20d11ef8c779433eb24503def900b9d28ad7',

'0x0e6bce8f215c52b53fad009e3bfacbb3be3774a6',

'0x36278457936460c0e7429aebf1bccd5061e1d0c1',

'0x263eebee6ced9ff47fc9208cd461114e79a03f27'

)

AND tx_succeeded = TRUE

GROUP BY txn_date, contract_name

ORDER BY txn_date DESC, total_gas_used DESC;