Reading · Analytics Sages

When you first step into the world of blockchain data, it feels like standing in the middle of a bustling city where everyone’s talking at once.
Transactions, contracts, wallets; all happening simultaneously.

Most people freeze. It looks too messy. Too complex.

But an analyst?
They lean in. They know that beneath the noise, there’s a story waiting to be told.
And with the right query, they can turn overwhelming chaos into insights that guide decisions, shape markets, and even launch careers.

That’s the journey we’re unpacking in today’s edition of The Data Edge 👇

Query Breakdown

The query is composed of two main parts: a Common Table Expression (CTE) and the main query.

The WITH daily_prices AS (...) block is a temporary, named result set. Its purpose is to prepare the necessary price data before the main calculation.

SELECT DATE(hour) AS dt, AVG(price) AS price: This line selects the date from the hour column and calculates the average price for that day.
FROM dex_solana.price_hour ph: It gets the hourly price data from the dex_solana.price_hour table.
WHERE contract_address = '7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr': This filters the data to only include prices for the token with the specified contract address.
AND DATE(hour) <= DATE '2025-07-25' AND DATE(hour) >= DATE '2025-07-25' - interval '83' day: These lines restrict the price data to the 12-week (84-day) period ending on July 25, 2025.
GROUP BY 1: It groups the data by the first selected column (dt), which ensures the average price is calculated for each individual day.

The main SELECT statement uses the daily_prices CTE to calculate the total volume.

SELECT SUM(COALESCE(tr.amount_usd, tr.amount * pr.price)) AS total_12_week_volume_usd: This is the core calculation. It sums up the volume for all transfers. The COALESCE function is used to handle potential null values:
- If a transfer already has an amount_usd value, it uses that.
- If amount_usd is null, it estimates the USD value by multiplying the native token amount (tr.amount) by the daily average price (pr.price) from the CTE.
FROM tokens_solana.transfers tr: It gets the transfer data from the tokens_solana.transfers table.
JOIN daily_prices pr ON DATE(tr.block_time) = pr.dt: This joins the transfer data with the daily prices, linking each transfer to the average price on the day it occurred.
WHERE tr.token_mint_address = '7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr': This filter ensures that only transfers for the specific token are considered.

AND DATE(tr.block_time) <= DATE '2025-07-25' AND DATE(tr.block_time) >= DATE '2025-07-25' - interval '83' day: These lines apply the same 12-week date range to the transfers, aligning them with the price data from the CTE.

Our learners don’t just study SQL, they create real dashboards that solve real problems.

Here’s an image of one of our Alumni’s Dashboard;

Finding meaning in the noise

Comments