mamad-5XN3k3Untitled Query
Updated 2022-11-06
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
›
⌄
with flowpricet as (
select TIMESTAMP::date as day, avg (price_usd) as flowprice
from flow.core.fact_prices
where symbol ='FLOW' and source ='coinmarketcap'
group by 1),
wallets as (
select payer,
case
when payer = '0x39e42c67cc851cfb' then 'lilico'
when payer ='0x18eb4ee6b3c026d2' then 'Dapper'
when payer ='0x55ad22f01ef568a1' then 'Blocto'
when payer= '0x1b65c33d7a352c61' then 'CricketMoments'
when payer= '0x6f649aee955bef6d' then 'RCRDSHP'
when payer= '0x94bb84386cfc3b5d' then 'Everbloom'
when payer= '0x9b00972a3ecb364b' then 'Seussibles'
when payer = '0x9e06eebf494e2d78' then 'Flow'
else null end as Payer_Name,
proposer
from flow.core.fact_transactions a join flow.core.fact_events b on a.tx_id = b.tx_id
where EVENT_TYPE='TokensWithdrawn'
and a.TX_SUCCEEDED ilike 'true'
and a.block_timestamp >= CURRENT_DATE - 30
and payer_name is not null),
maintable as (
select payer_name,
substr(nft_collection,20) as project,
count (distinct tx_id) as Sales_Count,
count (distinct buyer) as Buyers_Count,
sum (case when a.currency ='A.1654653399040a61.FlowToken' then a.price * c.flowprice else a.price end) as USD_Volume
from flow.core.ez_nft_sales a join wallets b on a.buyer = b.proposer join flowpricet c on a.block_timestamp::date = c.day
and a.block_timestamp >= CURRENT_DATE - 30
group by 1,2),
finaltable as (
Run a query to Download Data