with tx_dedup as ( select * from ( select transfer_id, row_number() over(partition by transfer_id order by amount desc) as rn_ FROM `reby-cloud.temp_eu.delete_kernel_transactions` ) where rn_ = 1 ) SELECT date_trunc(datetime(time_t),month) as month, sum(abs(amount))/100 as amount, count(*) as num_tx FROM `reby-cloud.temp_eu.delete_kernel_transactions` where transfer_id in (select transfer_id from tx_dedup) group by 1 order by 1 desc ; select transfer_id, count(*) as count_, array_agg(amount) as amounts, array_agg(type) FROM `reby-cloud.temp_eu.delete_kernel_transactions` group by 1 order by 1 desc
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter