-- MetricsDAO Blockchain Analytics 101
-- https://docs.metricsdao.xyz/analyst-resources/blockchain-data-101
SELECT
DATE_TRUNC('week', block_timestamp) as _date,
CASE
WHEN currency_symbol = 'ETH' then currency_symbol
WHEN currency_symbol = 'WETH' then currency_symbol
WHEN currency_symbol = 'USDC' then currency_symbol
ELSE 'Other'
END AS currency_symbol,
SUM(platform_fee) AS total_platform_fee,
SUM(platform_fee_usd) AS total_platform_fees_usd
FROM ethereum.core.ez_nft_sales
WHERE block_timestamp::DATE BETWEEN '2022-01-01' AND '2022-06-30'
AND platform_name = 'opensea'
GROUP BY 1, 2
ORDER BY 1, 4 DESC;