SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS "Trans_Date",

PUBKEY_SCRIPT_ADDRESS AS "Address",

SUM(VALUE) AS "Daily Inflows"

FROM bitcoin.core.fact_outputs;

WHERE date_trunc('day', BLOCK_TIMESTAMP) >= '2024-05-12'

AND date_trunc('day', BLOCK_TIMESTAMP) <= '2024-05-18'

GROUP BY date_trunc('day', BLOCK_TIMESTAMP),

PUBKEY_SCRIPT_ADDRESS

),

outflows AS (

SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS "Trans_Date",

PUBKEY_SCRIPT_ADDRESS AS "Address",

SUM(VALUE) AS "Daily Outflows"

FROM bitcoin.core.fact_inputs

WHERE date_trunc('day', BLOCK_TIMESTAMP) >= '2024-05-12'

AND date_trunc('day', BLOCK_TIMESTAMP) <= '2024-05-18'

GROUP BY date_trunc('day', BLOCK_TIMESTAMP),

PUBKEY_SCRIPT_ADDRESS

SELECT

inflows."Trans_Date" AS "In_Trans_Date",

inflows."Address" AS "In_Address",

outflows."Trans_Date" AS "Out_Trans_Date",

outflows."Address" AS "Out_Address",

COALESCE(inflows."Daily Inflows", 0) - COALESCE(outflows."Daily Outflows", 0) AS "Net Daily Balance"

FROM inflows

FULL OUTER JOIN outflows

ON inflows."Trans_Date" = outflows."Trans_Date"

AND inflows."Address" = outflows."Address"

ORDER BY "In_Trans_Date",

"In_Address";