SELECT

block_timestamp,

tx_hash

FROM polygon.core.ez_decoded_event_logs

WHERE contract_name ilike '%Azuro%'

AND block_timestamp::date >= '2023-01-01'

SELECT

CASE

when decoded_log: affiliate :: string in ('0x82875517c2b4bd534de4b2c0d21bff5f40b25da6',

'0x307838323837353531376332623462643533346465346232633064323162666635663430623235646136',

'0x0000000000000000000000000000000000000000') then 'bookmaker.xyz'

when decoded_log: affiliate :: string = '0x1a60e0981631cea879c59b356e957ca1907dd795' then 'Magma'

when decoded_log: affiliate :: string = '0x2e6816ce8e1ac9588a245ceb1b39dd75b6066d25' then 'PureBet'

when decoded_log: affiliate :: string in ('0x825462271f9c4d091c9a04bbb902f77370daa0f6',

'0x237fBF0076A0872a62278036dBBE6d3B443442E8') then 'JuicyBet'

when decoded_log: affiliate :: string = '0x832e8460a0af78a0d0f7fb6c4faf12caaa1fee62' then 'DexWin'

when decoded_log: affiliate :: string in ('0xfa695010bf9e757a1abcd2703259f419217aa756',

'0xf14c79a7fa22c1f97c779f573c9bf39b6b43381c',

'0x6e36cfcd5d59b96aabc8699c2ad31a874224d86e') then 'betswirl'

when decoded_log: affiliate :: string in ('0x63a8afe49b8870f60c9d6511d123e3b5202f3114',

'0x229db45ba7d82e89bf7314ba2e21e9e9d22974a4') then 'CoWagerBot'

when decoded_log: affiliate :: string = '0xf3331347f937496570a2e525c671623a81dbc930' then 'NormieBet'

when decoded_log: affiliate :: string = '0xacaad22871bcaf73d355db291bf848f2c23dedd8' then 'BookieBot'

when decoded_log: affiliate :: string = '0xe27a0d20aaeb6e631a2b142e5db2f97aaea394db' then 'gamblr.xyz'

when decoded_log: affiliate :: string = '0x6A09f42f9A42676FaFdB434c9d7933D09D4e2de7' then 'BoxBet'

when decoded_log: affiliate :: string = '0x46d9018d7ad0051435632f3233d11a2351cbe54d' then 'BetIT'

when decoded_log: affiliate :: string = '0x1605D43545b6D333B01295b6B6302ED1ecA01258' then 'Gamblino'

when decoded_log: affiliate :: string = '0x73bd16136bd6359f12356253a7f03de535b8c3d4' then 'DegenBookie'

else 'other' end as frontend,

count(DISTINCT a.tx_hash) as n_bets,

count(DISTINCT decoded_log:bettor) as n_bettor,

sum(decoded_log:amount/1e6) as volume

FROM polygon.core.ez_decoded_event_logs a