1 - We use data from the [osmosis.core.fact_transfers] to track transactions that transfer assets from Osmosis to Quasar.

Wallet addresses in Quasar start with "quasar".

The following query obtains the mentioned transactions:

SELECT *
FROM osmosis.core.fact_transfers
WHERE SUBSTR(RECEIVER, 0, 6) ILIKE 'quasar' AND TX_SUCCEEDED = 'true'

2 - To find the amount of transferred assets from Osmosis to Quasar, we perform the following steps:

I - we obtain the TX_ID of transactions that transferred assets from Osmosis to Quasar using data from [osmosis.core.fact_msg_attributes] . (explained in part 1).
II - using the obtained TX_ID and data from [osmosis.core.fact_msg_attributes], we can obtain the address of the transferred assets and the transferred amount. (Raw amount)
III - to find decimals and names of transferred assets, we use data from [osmosis.core.dim_tokens] .
IV - finally, to find the volume of transferred assets in USD, we use data from [osmosis.core.ez_prices] .

The following SQL obtains all transactions that transferred assets from Osmosis to Quasar with details:

WITH ASSETS_TRANSFERS_TO_Quasar AS (
SELECT BLOCK_TIMESTAMP, TX_ID, SENDER, RECEIVER
FROM osmosis.core.fact_transfers
WHERE SUBSTR(RECEIVER, 0, 6) ILIKE 'quasar' AND TX_SUCCEEDED = 'true' 
),

TRANSFERRED_TOKEN_ADDRESS AS (
SELECT ASSETS_TRANSFERS_TO_Quasar.*, ATTRIBUTE_VALUE AS "Token Address"
FROM ASSETS_TRANSFERS_TO_Quasar JOIN osmosis.core.fact_msg_attributes USING(TX_ID)
WHERE ATTRIBUTE_KEY = 'denom' AND MSG_TYPE = 'wasm'
),

TRANSFERRED_TOKEN_AMOUNT_AND_DENOM AS (
SELECT TRANSFERRED_TOKEN_ADDRESS.*,
 parse_json(ATTRIBUTE_VALUE):amount AS "Raw amount",
 SPLIT((parse_json(ATTRIBUTE_VALUE):denom),'/') AS "Details",
 ARRAY_SIZE("Details") AS LENGTH,
 "Details"[LENGTH-1] as "Denom"
 FROM TRANSFERRED_TOKEN_ADDRESS JOIN osmosis.core.fact_msg_attributes USING(TX_ID)
  WHERE ATTRIBUTE_KEY = 'packet_data' AND MSG_TYPE = 'send_packet'
),

TRANSFERRED_TOKENS_DETAILS AS (
SELECT BLOCK_TIMESTAMP, TX_ID, SENDER, RECEIVER, "Token Address",
 "Raw amount", DECIMAL, ("Raw amount"/ POW(10, DECIMAL)) AS "Transferred amount", "Denom", PROJECT_NAME
 FROM TRANSFERRED_TOKEN_AMOUNT_AND_DENOM LEFT JOIN osmosis.core.dim_tokens ON ("Token Address" = ADDRESS)
),

TRANSFERRED_TOKENS_LIST AS (
SELECT DISTINCT("Token Address") AS ADDRESS
 FROM TRANSFERRED_TOKENS_DETAILS
),

TOKEN_PRICES AS (
SELECT RECORDED_HOUR::DATE AS DAYS, AVG(PRICE) AS PRICE, ADDRESS
FROM TRANSFERRED_TOKENS_LIST LEFT JOIN osmosis.core.ez_prices ON (CURRENCY = ADDRESS)
WHERE DAYS >= (SELECT MIN(BLOCK_TIMESTAMP)::DATE FROM TRANSFERRED_TOKENS_DETAILS)
GROUP BY DAYS, ADDRESS
),

ALL_TRANSFERS AS (
SELECT BLOCK_TIMESTAMP::DATE AS DAYS, TX_ID, SENDER, RECEIVER, "Token Address",
 "Raw amount", DECIMAL, "Transferred amount", "Transferred amount"*PRICE AS "Transferred amount in USD",
"Denom", PROJECT_NAME
FROM TRANSFERRED_TOKENS_DETAILS AS TRANSFERS LEFT JOIN TOKEN_PRICES ON (BLOCK_TIMESTAMP::DATE = DAYS AND ADDRESS = "Token Address")
)

SELECT * FROM ALL_TRANSFERS

### Tips 💡

The reason why we have not directly used table [osmosis.core.fact_transfers] to obtain the transferred amount (RAW_AMOUNT) is that the data in this table was not accurate until the last few days the data in this table was recently updated to the correct values.

Currently, 5 assets transferred from Osmosis to Quasar:

Asset address	Asset name	Description
uosmo	OSMO	Price, label, and address are specified.
ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858	USDC	Price, label, and address are specified.
ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2	ATOM	Price, label, and address are specified.
ibc/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC	stOSMO	Label and address are specified, but the price has been updated until 2023-03-16.
ibc/C2A23A9BECC7305617D486D03547D1B1085A01CE43E8EE7DC7329742B87B1998	Not specified	Price, label, and address aren't specified in `[osmosis.core.ez_prices]` and `[osmosis.core.dim_tokens]`.

As previously mentioned, Quasar launched its mainnet on March 23, 2023, and price data for stOSMO is available until 2023-03-16.
Therefore in the calculation of the volume of transferred assets in USD, transactions related to transferring stOMSO and ibc/C2A23A9BECC7305617D486D03547D1B1085A01CE43E8EE7DC7329742B87B1998 will be automatically ignored.

Currently, there are 2 transactions related to transferring stOSMO and ibc/C2A23A9BECC7305617D486D03547D1B1085A01CE43E8EE7DC7329742B87B1998 from Osmosis to Quasar.

Osmosis - 13. Quasar Launch Analysis