WITH count_new_wallets_FIFA_week AS ( SELECT date_trunc('hour', B.block_timestamp) as fecha, count(distinct A.address) as New_addresses FROM algorand.account A INNER JOIN algorand.block B ON A.created_at = B.block_id WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' group by fecha order by fecha ), count_active_wallets_FIFA_week AS ( select date_trunc('hour', block_timestamp) as fecha, count (distinct sender) as active_addresses FROM algorand.payment_transaction WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' group by fecha order by fecha ), new_wallets_FIFA_week AS ( SELECT A.address as New_addresses FROM algorand.account A INNER JOIN algorand.block B ON A.created_at = B.block_id WHERE block_timestamp >= '2022-05-02' and block_timestamp < '2022-05-11' group by 1 ), active_wallets_FIFA_week AS ( select sender as active_addresses FROM algorand.payment_transaction WHERE block_timestamp >= '2022-05-02' and block_timestamp < '2022-05-11' group by 1 ), ALGO_total_transactions_fifa_week AS ( select date_trunc('hour', block_timestamp) as fecha, count(distinct tx_id) as number_ALGO_txs, sum (amount) as ALGO_transferred FROM algorand.payment_transaction WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' and sender IN (select active_addresses from active_wallets_FIFA_week) group by 1 order by fecha ), ALGO_new_senders_transactions_fifa_week AS ( select date_trunc('hour', block_timestamp) as fecha, count(distinct tx_id) as number_ALGO_txs_new, sum (amount) as ALGO_transferred_new FROM algorand.payment_transaction WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' and sender IN (select New_addresses from new_wallets_FIFA_week) group by 1 order by fecha ), ALGO_new_receivers_transactions_fifa_week AS ( select date_trunc('hour', block_timestamp) as fecha, count(distinct tx_id) as number_ALGO_txs_new, sum (amount) as ALGO_transferred_new FROM algorand.payment_transaction WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' and receiver IN (select New_addresses from new_wallets_FIFA_week) group by 1 order by fecha ), ALGO_sent_from_cex AS ( SELECT date_trunc('hour', p.block_timestamp) as fecha, count (distinct p.tx_id) as number_ALGO_txs_cex, sum (amount) as ALGO_from_cex FROM algorand.payment_transaction p INNER JOIN algorand.labels c ON p.sender = c.address WHERE fecha >= '2022-05-02' and fecha < '2022-05-11' AND c.label_type = 'cex' group by 1 ), ALGO_swap_influx as ( select date_trunc('hour', block_timestamp) as fecha, count (distinct tx_group_id) as swaps, sum (swap_to_amount) as ALGO_swapped_to from algorand.swaps where fecha >= '2022-05-02' and fecha < '2022-05-11' AND swap_to_asset_id = 0 group by 1 ), total_swaps as ( select date_trunc('hour', block_timestamp) as fecha, count (distinct tx_group_id) as total_swaps from algorand.swaps where fecha >= '2022-05-02' and fecha < '2022-05-11' group by 1 ), ALGO_price AS ( select block_hour as fecha, price_usd, swaps_in_hour from algorand.prices_swap where fecha >= '2022-05-02' and fecha < '2022-05-11' and asset_id = 0 ) SELECT w.fecha, w.New_addresses, sum (w.New_addresses) over (order by w.fecha) as total_new_addresses, t.active_addresses as unique_active_addresses, ttx.number_ALGO_txs as ALGO_txs, sum (ttx.number_ALGO_txs) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as txs_daily, sum (ttx.ALGO_transferred) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as transferred_daily, sum (nstx.number_ALGO_txs_new) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as txs_new_senders, sum (nstx.ALGO_transferred_new) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as transferred_new_senders, sum (nrtx.number_ALGO_txs_new) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as txs_new_receivers, sum (nrtx.ALGO_transferred_new) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as transferred_new_receivers, sum (cex.number_ALGO_txs_cex) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as txs_from_cex, sum (cex.ALGO_from_cex) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as transferred_from_cex, p.price_usd as ALGO_price, sum (si.swaps) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as ALGO_to_swaps, sum (si.ALGO_swapped_to) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as ALGO_swapped_to, sum (ts.total_swaps) over (partition by DATE_PART(day, w.fecha) order by w.fecha) as total_to_swaps FROM count_new_wallets_FIFA_week w LEFT JOIN count_active_wallets_FIFA_week t ON w.fecha = t.fecha LEFT JOIN ALGO_total_transactions_fifa_week ttx ON w.fecha = ttx.fecha LEFT JOIN ALGO_new_senders_transactions_fifa_week nstx ON w.fecha = nstx.fecha LEFT JOIN ALGO_new_receivers_transactions_fifa_week nrtx ON w.fecha = nrtx.fecha LEFT JOIN ALGO_sent_from_cex cex ON w.fecha = cex.fecha LEFT JOIN ALGO_swap_influx si ON w.fecha = si.fecha LEFT JOIN total_swaps ts ON w.fecha = ts.fecha LEFT JOIN ALGO_price p ON w.fecha = p.fecha order by 1 desc

Algorand x Fifa Partnership