SELECT to_address, MIN(BLOCK_TIMESTAMP) AS first_time FROM thorchain.transfers

where from_address in ('thor1ty6h2ll07fqfzumphp6kq3hm4ps28xlm2l6kd6', 'thor1t60f02r8jvzjrhtnjgfj4ne6rs5wjnejwmj7fh',

'thor1uz4fpyd5f5d6p9pzk8lxyj4qxnwq6f9utg0e7k')

GROUP BY to_address

),

transfer_dtls AS (

SELECT nw.to_address as ADDRESS,

Case when trn.from_address = 'thor1ty6h2ll07fqfzumphp6kq3hm4ps28xlm2l6kd6' then 'Crypto.com'

when trn.from_address = 'thor1t60f02r8jvzjrhtnjgfj4ne6rs5wjnejwmj7fh' then 'Binance Cold Wallet'

when trn.from_address = 'thor1uz4fpyd5f5d6p9pzk8lxyj4qxnwq6f9utg0e7k' then 'Binance Hot Wallet'

end as cex,

nw.first_time FROM new_wallets nw

LEFT JOIN thorchain.transfers trn

ON nw.to_address = trn.to_address

and first_time = trn.BLOCK_TIMESTAMP

where trn.from_address in ('thor1ty6h2ll07fqfzumphp6kq3hm4ps28xlm2l6kd6', 'thor1t60f02r8jvzjrhtnjgfj4ne6rs5wjnejwmj7fh',

'thor1uz4fpyd5f5d6p9pzk8lxyj4qxnwq6f9utg0e7k')

GROUP BY nw.to_address, cex, nw.first_time

SELECT --date(date_trunc('day', first_time)) AS day,

cex, COUNT(ADDRESS) AS no_of_users FROM transfer_dtls

GROUP BY --day,

cex --order by day desc