COUNT(USER) | USER_TYPE | |
|---|---|---|
1 | 1 | only approved |
2 | 52 | deposited, redeemed, transferred |
3 | 272 | deposited and transferred |
4 | 16 | only transferred |
5 | 15 | other combination |
6 | 55 | deposited, approved, transferred |
7 | 17 | deposited, redeemed, transferred, and approved |
LittlerDatauser type lron copy
Updated 2025-03-24
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
›
⌄
-- forked from user type lron @ https://flipsidecrypto.xyz/studio/queries/7e8d6462-af51-42ea-a587-ee9b65bba44c
WITH core_lron_txs AS ( --improved with aI
SELECT
origin_from_address AS user,
topic_0
FROM ronin.core.fact_event_logs
WHERE tx_succeeded = 'TRUE'
AND block_timestamp > '2025-03-19'
AND contract_address = '0xcad9e7aa2c3ef07bad0a7b69f97d059d8f36edd2' -- LRON contract
AND topic_0 IN (
'0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7', -- deposit
'0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db', -- redeem
'0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925', -- approval
'0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- transfer
)
GROUP BY 1, 2
),
sect AS (
SELECT
CASE
WHEN COUNT(CASE WHEN topic_0 = '0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7' THEN 1 END) > 0 THEN 1 ELSE 0 END AS deposit,
COUNT(CASE WHEN topic_0 = '0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db' THEN 1 END) AS redeem,
COUNT(CASE WHEN topic_0 = '0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925' THEN 1 END) AS approval,
COUNT(CASE WHEN topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' THEN 1 END) AS transfer,
user
FROM core_lron_txs
GROUP BY user
),
user_type AS (
SELECT
user,
CASE
WHEN deposit = 1 AND redeem = 0 AND approval = 0 AND transfer = 0 THEN 'only deposited'
WHEN deposit = 0 AND redeem = 1 AND approval = 0 AND transfer = 0 THEN 'only redeemed'
WHEN deposit = 0 AND redeem = 0 AND approval = 1 AND transfer = 0 THEN 'only approved'
Last run: 20 days ago
7
238B
2s