Fetching Prices, Volume, and Market Cap using Santiment Queries

Santiment Queries’ asset_prices_v3 table serves as a critical tool for procuring accurate and detailed information about cryptocurrency asset prices, volume, and market capitalization at any given timestamp. This guide aims to introduce you to the table’s structure and its versatile applications.

Data for the asset_prices_v3 table is sourced from two primary platforms - Coinmarketcap and Cryptocompare.

The asset_prices_v3 table offers extensive information about cryptocurrency asset prices at various timestamps. Here are the key columns you should be familiar with:

• dt (DateTime): This column signifies the timestamp of the data.
• source (LowCardinality(String)): Specifies the data’s origin, either coinmarketcap or cryptocompare.
• slug (LowCardinality(String)): The name of the cryptocurrency project (e.g., ‘bitcoin’, ‘ethereum’).
• price_usd (Float64): The price of the asset in USD.
• price_btc (Float64): The price of the asset in Bitcoin.
• marketcap_usd (Float64): The market capitalization of the asset in USD.
• volume_usd (Float64): The rolling total trading volume of the asset in USD for last 24 hours

The following SQL query allows you to extract the daily closing price of Bitcoin for the last seven days.

1
SELECT
2
    toDate(dt) as date,
3
    argMax(price_usd, dt) as closing_price
4
FROM
5
    asset_prices_v3
6
WHERE
7
    slug = 'bitcoin'
8
    AND dt >= (now() - interval 7 day)
9
GROUP BY
10
    date
11
ORDER BY
12
    date DESC

Test in Queries

To retrieve the top 20 cryptocurrency projects ranked by their average trading volume in USD over the last 24 hours, use the following SQL query.

1
SELECT
2
    slug,
3
    AVG(volume_usd) as avg_volume
4
FROM
5
    asset_prices_v3
6
WHERE
7
    dt >= (now() - interval 24 hour)
8
GROUP BY
9
    slug
10
ORDER BY
11
    avg_volume DESC
12
LIMIT 20;

Test in Queries