view.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS daily_volume AS
WITH parameters AS (
    SELECT  
        '0x9cdAA94733a682013Ff8AfD72BA59FB63619C98d' AS pool_addr,
        EXTRACT(EPOCH FROM NOW() - INTERVAL '24 hours') AS last_time
),
combined AS (
    SELECT amount, "blockTimestamp", pool
    FROM "AddLiquidity" 
    WHERE "blockTimestamp" >= (SELECT last_time FROM parameters)
    
    UNION ALL
    
    SELECT amount, "blockTimestamp", pool 
    FROM "OpenLong" 
    WHERE "blockTimestamp" >= (SELECT last_time FROM parameters)
    
    UNION ALL
    
    SELECT amount, "blockTimestamp", pool
    FROM "OpenShort" 
    WHERE "blockTimestamp" >= (SELECT last_time FROM parameters)
)
SELECT SUM(amount) AS daily_volume, pool from combined
group by pool;