__LeMacelixir user count 24HR
Updated 2023-05-29
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 elixir user old/new @ https://flipsidecrypto.xyz/edit/queries/0097107b-85d4-4d30-a902-fec68b2d2b98
-- forked from elixir buyers volume 1 year @ https://flipsidecrypto.xyz/edit/queries/81a87a6e-e241-4215-8c8b-3bfa4efc1fab
-- forked from elixir user retention @ https://flipsidecrypto.xyz/edit/queries/02a7a0ca-47d5-4e48-ba43-f309d766c92f
WITH sol_nft AS (
SELECT
DISTINCT contract_name AS contract_name
, contract_address
, project_name
FROM solana.core.dim_nft_metadata
)
-- using ASOL to get sol prices
, sol_price AS(
SELECT
DATE_TRUNC('day', RECORDED_HOUR) time
, AVG(close) AS price
FROM solana.core.ez_token_prices_hourly
WHERE symbol LIKE '%aSOL%'
AND RECORDED_HOUR >= CURRENT_TIMESTAMP - INTERVAL '1 year'
GROUP BY 1
)
, elixir AS(
WITH elixir_base AS (
SELECT
block_timestamp
, DATE_TRUNC('day', block_timestamp) day
, signers[0] AS user
, (pre_balances[0] - post_balances[0]) / POW(10, 9) AS sol_spent
, fee/ POW(10,9) AS fee
, SUBSTRING(log_messages[4], 9, 44) AS contract_address
, value:"accounts"[1] AS nft
Run a query to Download Data