SELECT DATE_TRUNC({{timeframe}}, block_timestamp) AS time, SPLIT(to_asset, '-')[0] AS asset, SUM(to_amount) AS to_amount, SUM(to_amount_usd) AS to_amount_usd

FROM flipside_prod_db.thorchain.swaps

WHERE block_timestamp >= TO_DATE('2022-03-11')

AND (to_asset ILIKE '%/%')

GROUP BY 1, 2

),

from_synths AS (

SELECT DATE_TRUNC({{timeframe}}, block_timestamp) AS time, SPLIT(from_asset, '-')[0] AS asset, SUM(from_amount) AS from_amount, SUM(from_amount_usd) AS from_amount_usd

FROM flipside_prod_db.thorchain.swaps

WHERE block_timestamp >= TO_DATE('2022-03-11')

AND (from_asset ILIKE '%/%')

GROUP BY 1, 2

),

base AS (

SELECT COALESCE(t.asset, f.asset) AS asset

, COALESCE(t.time, f.time) AS time

, COALESCE(to_amount, 0) AS to_amount

, COALESCE(to_amount_usd, 0) AS to_amount_usd

, COALESCE(from_amount, 0) AS from_amount

, COALESCE(from_amount_usd, 0) AS from_amount_usd

FROM to_synths t

FULL OUTER JOIN from_synths f ON f.time = t.time AND f.asset = t.asset

SELECT *

, CASE WHEN from_amount = 0 THEN NULL ELSE to_amount * 1.0 / from_amount END AS amount_ratio

, to_amount_usd - from_amount_usd AS net_mint_amount_usd

, CASE WHEN from_amount_usd = 0 THEN NULL ELSE to_amount_usd * 1.0 / from_amount_usd END AS usd_ratio

, CASE WHEN to_amount = 0 THEN NULL ELSE from_amount * 1.0 / to_amount END AS amount_ratio_2

, CASE WHEN to_amount_usd = 0 THEN NULL ELSE from_amount_usd * 1.0 / to_amount_usd END AS usd_ratio_2

FROM base

WHERE (from_amount_usd + to_amount_usd) > 10000