REGEXP_SUBSTR(VALUE, 'Kernel App: ([^,]+)', 1, 1, 'e', 1) AS kernel_app,

REGEXP_SUBSTR(VALUE, 'Periphery App: ([^,]+)', 1, 1, 'e', 1) AS Periphery_app,

case

when Periphery_app = '0xf85E084b9Fb2079d47CAe15AE8db528c37551162' then 'Virtuals'

when Periphery_app = '0x40481E0F5B77FCEA85bD9012f0400A80564B0310' then 'Resolv'

when Periphery_app = '0x1572BF31cE83BeF0f772B0694D51bc9e8676c813' then 'Lombard'

when Periphery_app in ('0x44bD74B62739de2412b617FD9B2Ee85C998B1494','0x9E63DdD095A4306D9f509Fa8E04A270fD847069B') then 'Pendle'

when Periphery_app = '0x4611D8AC19a40084bAc1a3D3e5Dd816Dc0D16985' then 'Angle'

when Periphery_app = '0xfC639DE6E751140B19Da43a9aE25A5ab9EF55721' then 'GMX'

else '?' end as "Intracted platfrom",

case

when Periphery_app = '0xf85E084b9Fb2079d47CAe15AE8db528c37551162' then 'Base'

when Periphery_app = '0x40481E0F5B77FCEA85bD9012f0400A80564B0310' then 'Base'

when Periphery_app = '0x1572BF31cE83BeF0f772B0694D51bc9e8676c813' then 'Base'

when Periphery_app = '0x44bD74B62739de2412b617FD9B2Ee85C998B1494' then 'Mantle'

when Periphery_app = '0x9E63DdD095A4306D9f509Fa8E04A270fD847069B' then 'Arbitrum'

when Periphery_app = '0x4611D8AC19a40084bAc1a3D3e5Dd816Dc0D16985' then 'Arbitrum'

when Periphery_app = '0xfC639DE6E751140B19Da43a9aE25A5ab9EF55721' then 'Arbitrum'

else '?' end as "Destination chain",

tx_id,

SIGNERS[0] AS address,

BLOCK_TIMESTAMP

FROM

eclipse.core.fact_transactions,

LATERAL FLATTEN(input => LOG_MESSAGES)

WHERE BLOCK_TIMESTAMP::date >= '2025-01-01' and

Periphery_app in ('0xf85E084b9Fb2079d47CAe15AE8db528c37551162', '0x40481E0F5B77FCEA85bD9012f0400A80564B0310',

'0x1572BF31cE83BeF0f772B0694D51bc9e8676c813','0x44bD74B62739de2412b617FD9B2Ee85C998B1494',

'0x9E63DdD095A4306D9f509Fa8E04A270fD847069B','0x9E63DdD095A4306D9f509Fa8E04A270fD847069B',

'0x4611D8AC19a40084bAc1a3D3e5Dd816Dc0D16985','0xfC639DE6E751140B19Da43a9aE25A5ab9EF55721') and

INSTRUCTIONS[1]:programId = '4Ze9GP4pgjUJVwbpxwiQF7wsQYxzywuqS5ku3QiVSCF5'

AND VALUE LIKE '%Kernel App%')

select count(DISTINCT tx_id) as "Total txs", count(DISTINCT address) as "Total users"

from shuffle