aj314Average Sales & Transaction Volume
Updated 2023-12-10
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
›
⌄
-- Identify the average daily sales price, sale count, and transfer count for the 'Doodles' project
-- Create CTE to calculate daily sales metrics
WITH DailySales AS (
-- Select the truncated month, average sales price, and sale count
SELECT
DATE_TRUNC('month', block_timestamp) AS sales_month,
ROUND(AVG(price), 2) AS avg_sales_price_eth, --Calculate avg price
COUNT(tx_hash) AS sale_count
FROM
ethereum.nft.ez_nft_sales
WHERE
project_name = 'Doodles' --Filter by project_name
GROUP BY
sales_month --Group by the sales_month
),
-- Create CTE to calculate transfer counts
TransferVolume AS (
-- Select the count of transfers and truncated month
SELECT
COUNT(tx_hash) AS transfer_count,
DATE_TRUNC('month', block_timestamp) AS date
FROM
ethereum.nft.ez_nft_transfers
WHERE
project_name = 'Doodles' --Filter by project_name
GROUP BY
date --Group by the date
)
-- Final SELECT statement to combine results from both CTEs
SELECT
ds.sales_month,
ds.avg_sales_price_eth,
ds.sale_count,
tv.transfer_count
Run a query to Download Data