The following query returns the histroical price action of WAVAX on Avalanche. To build this analysis for another token, insert a new contract address and outline which chain the token contract is live on.
Also, addresses stored in the contract_address field are in lower case. So when you use an address that is all upper case - e.g C02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2 - you must wrap the address in the lower() function. The AND statement down below would then look like the following:
AND contract_address = lower('C02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
SELECT dt AS date
, price_in_usd AS price
FROM reports.token_prices
WHERE [signed_at:daterange]
AND contract_address = 'b31f66aa3c1e785363f0875a1b74e27b85fd66c7'
AND chain_name = 'avalanche_mainnet'
The following query will retrieve all the entries in the reports.token_usd_prices data table.
SELECT *
FROM reports.token_prices
-- This will be quite a large query, so we will need to refine it a bit further!
The goal is to transform this query into one that tracks the daily price action of a token on Avalanche. A tokens price can be found within the reports.token_prices table in the price_in_usd column.
SELECT price_in_usd
FROM reports.token_prices
WHERE chain_name = 'avalanche_mainnet'
Add the [signed_at:aggregation] and [signed_at:daterange]parameter filters to pivot analysis via date aggregation and range.
SELECT price_in_usd as Price
, [signed_at:aggregation] as Date
FROM reports.token_prices
WHERE chain_name = 'avalanche_mainnet'
AND [signed_at:daterange]
Finally, add the token address you want historical price action for. In this example, we will use WAVAX (Wrapped AVAX) -0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7
Read more about why this address needs to be in the unhex() function here.
SELECT dt AS date
, price_in_usd AS price
FROM reports.token_prices
WHERE [signed_at:daterange]
AND contract_address = 'b31f66aa3c1e785363f0875a1b74e27b85fd66c7'
AND chain_name = 'avalanche_mainnet'
Congrats! You just added a new query to your arsenal.