with RUNE_volume AS (
SELECT
date_trunc('day',block_timestamp)::DATE as DAY,
SUM(RUNE_AMOUNT) AS Volume_of_RUNE_transferred,
SUM(RUNE_AMOUNT_USD) AS Volume_of_RUNE_transferred_in_USD
FROM thorchain.transfers
WHERE ASSET='THOR.RUNE'
GROUP BY 1
ORDER BY 1 ASC)
SELECT
DAY,
Volume_of_RUNE_transferred,
avg(Volume_of_RUNE_transferred_in_USD/Volume_of_RUNE_transferred) AS Rune_price_in_USD
FROM RUNE_volume
GROUP BY 1,2
ORDER by 1 ASC