-- create a table with all possible combinations of dates and users

dates AS (

SELECT date_day FROM crosschain.core.dim_dates

WHERE date_day BETWEEN '2023-01-15' AND CURRENT_DATE

ORDER BY date_day ASC

),

users AS (

SELECT DISTINCT tx_to AS user FROM solana.core.fact_transfers

WHERE mint = 'BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn'

AND block_timestamp BETWEEN '2023-01-15' AND CURRENT_DATE

UNION

SELECT DISTINCT tx_from AS user FROM solana.core.fact_transfers

WHERE mint = 'BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn'

AND block_timestamp BETWEEN '2023-01-15' AND CURRENT_DATE

),

-- combine the two tables to get all possible combinations of dates and users

dates_users AS (

SELECT date_day, user FROM dates, users

),

-- get the transaction data and group by date and user

all_hades_txn AS (

SELECT

block_timestamp::date AS date,

tx_to AS user,

sum(amount) AS ainf,

0 as aoutf

FROM solana.core.fact_transfers

WHERE mint='BWXrrYFhT7bMHmNBFoQFWdsSgA3yXoAnMhDK6Fn1eSEn'

AND block_timestamp BETWEEN '2023-01-15' AND CURRENT_DATE

GROUP BY date, tx_to

UNION all

SELECT

block_timestamp::date AS date,

tx_from AS user,

0 as ainf,