Data Hunters 2024-06-13 01:28 AM
Updated 2024-06-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- Define the chains and their details
WITH chains AS (
SELECT * FROM (VALUES
('Ethereum Mainnet', '1', 'ETH'),
('Arbitrum One', '42161', 'ETH'),
('BNB Smart Chain Mainnet', '56', 'BNB'),
('Avalanche C-Chain', '43114', 'AVAX'),
('Polygon Mainnet', '137', 'MATIC')
) AS a (chain, chain_id, currency)
),
-- Aggregate transaction data from all chains
all_chains_data AS (
SELECT
date_trunc('day', block_timestamp) AS date,
origin_from_address AS user,
COUNT(DISTINCT decoded_log:bridgeData:bridge) AS num_bridges
FROM
(
SELECT *, 'Arbitrum One' as src_chain FROM arbitrum.core.ez_decoded_event_logs
UNION ALL
SELECT *, 'Polygon Mainnet' as src_chain FROM polygon.core.ez_decoded_event_logs
) AS a
WHERE
event_name = 'LiFiTransferStarted'
GROUP BY 1, 2
),
-- Count the number of users for each number of bridges
bridge_counts AS (
SELECT
num_bridges,
COUNT(DISTINCT user) AS num_users
FROM
all_chains_data
GROUP BY 1
QueryRunArchived: QueryRun has been archived