-- forked from Automate buyback checker @ https://flipsidecrypto.xyz/edit/queries/2526dfaf-1acc-419e-8b3f-71d5509fb99a with launchpad as ( select 'QUO' as launchpad, lower('0x6B1f72E2f0FCdE115290Be8CB208Da07860E1E3e') as sale_contract, '0.01' as ido_price, 'Standard Sale' as sale_types union select 'QUO' as launchpad, lower('0x61e518042D7783406b273cd29862244805368A75') as sale_contract, '0.01' as ido_price, 'Vidia Standard Sale' as sale_types union select 'QUO' as launchpad, lower('0xfFdc77189033F5f5aa0748B58539d574f97cc038') as sale_contract, '0.01' as ido_price, 'Vidia Unlimited Sale' as sale_types union select 'QUO' as launchpad, lower('0x5c937F89D117Bba82fE49B93F37a0c0420f004cA') as sale_contract, '0.01' as ido_price, 'Whitelist $250' as sale_types union select 'PINE' as launchpad, lower('0xeCCFe6A531E1bb379E32CbdD4e9AFd5d30465a6d') as sale_contract, '0.2', 'Standard Sale' union select 'PINE' as launchpad, lower('0x3Fd53a1B0D1eef5aC963aFB979b47E9e4b13B24e') as sale_contract, '0.2', 'Vidia Standard Sale' union select 'PINE' as launchpad, lower('0xa800614528FE1360c0AC1Ff2268d1687A8CF97d3') as sale_contract, '0.2', 'Vidia Unlimited Sale' union select 'PINE' as launchpad, lower('0x80050189e95fb2A27Fc90b1d931F2415Edca3247') as sale_contract, '0.2', 'Whitelist $50' union select 'PINE' as launchpad, lower('0x4f2F9CD3049882e0dbC9e1c5A1910A4dd65d9CB33') as sale_contract, '0.2', 'Whitelist $100' union select 'PINE' as launchpad, lower('0xd978A18f30E08b049b3406A1cA71234176418a2e') as sale_contract, '0.2', 'Whitelist $200' union select 'PINE' as launchpad, lower('0xCD675Ec17Ab0E935C4A70557d47C6Bd785e2aA38') as sale_contract, '0.2', 'Whitelist $250' union select 'PINE' as launchpad, lower('0x388AF868359e2Ec7d19184705621B285e5C83a9D') as sale_contract, '0.2', 'Whitelist $500' union select 'PINE' as launchpad, lower('0x27ce3CD4264bfAcd86CcB7c2Ad5748387Faa0746') as sale_contract, '0.2', 'Whitelist $2000' union select 'ARKEN' as launchpad, lower('0x0078E4Fb61d29bC6655f106B44Ff29C581b23889') as sale_contract, '0.004', 'Standard Sale' union select 'ARKEN' as launchpad, lower('0x87d148F53826177c2a76877FCf250442BcC2200f') as sale_contract, '0.004', 'Vidia Standard Sale' union select 'ARKEN' as launchpad, lower('0x56C6Be7Fcde5393053B0778628eb25D5C39DA9E1') as sale_contract, '0.004', 'Vidia Unlimited Sale' union select 'ARKEN' as launchpad, lower('0x0a9B02b4fB0e0B149B680b8639c3657691bbca48') as sale_contract, '0.004', 'C98 Unlimited Sale' union select 'ARKEN' as launchpad, lower('0x881d614fdea1fe38B1F6c7f5C97ca107258f6d71') as sale_contract, '0.004', 'Whitelist $100' union select 'ARKEN' as launchpad, lower('0xB81CD85E44dCEeD1EC8B6132eFfe3f80283Df6Af') as sale_contract, '0.004', 'Whitelist $200' union select 'ARKEN' as launchpad, lower('0x5FF7af2d33E0B74d2333BbB456a33D753AF62B12') as sale_contract, '0.004', 'Whitelist $300' union select 'ARKEN' as launchpad, lower('0x37D5eD3419E24E994Bb12AA4f3e1E81906E91822') as sale_contract, '0.004', 'Whitelist $400' union select 'ARKEN' as launchpad, lower('0x75072fc181Ab1C1224f7948f39Bf0A64ef78c0a5') as sale_contract, '0.004', 'Whitelist $500' union select 'ARKEN' as launchpad, lower('0x0f7Ab6d4EabB8a019E889Cb5F1779DAc180D7174') as sale_contract, '0.004', 'Whitelist $600' union select 'ARKEN' as launchpad, lower('0x15A389b521d2731684EBd920315DcC23400581D8') as sale_contract, '0.004', 'Whitelist $700' union select 'ARKEN' as launchpad, lower('0x91599fc79235F7942Abc97A9193f3005B06e5f71') as sale_contract, '0.004', 'Whitelist $800' union select 'ARKEN' as launchpad, lower('0xc5A2fBFAFA03Bb90eFDE3b44Ce13DD99489CDf91') as sale_contract, '0.004', 'Standard Sale Arbitrum' union select 'ARKEN' as launchpad, lower('0x1Eb57F5c754A019fe953Ba42fcd80418674b804D') as sale_contract, '0.004', 'Unlimited Sale Arbitrum' union select 'HUMANODE' as launchpad, lower('0xD29398EA89Ff93513081609063fa196329e2e3DF') as sale_contract, '0.15', 'Standard Sale' union select 'HUMANODE' as launchpad, lower('0xd9c0Ca6D9A56fe5D4B8262BED36F6b1F1D32741f') as sale_contract, '0.15', 'Vidia Standard Sale' union select 'HUMANODE' as launchpad, lower('0xd724Dc39FAc11B25F364c22BCA8046928822f010') as sale_contract, '0.15', 'Vidia Unlimited Sale' union select 'HUMANODE' as launchpad, lower('0xa4367Be0a694BB3D13fC3373B93B00D850529298') as sale_contract, '0.15', 'C98 Unlimited Sale' union select 'HUMANODE' as launchpad, lower('0x996083D5499e6bEB2f88A07654b6DcECeBA3028E') as sale_contract, '0.15', 'Whitelist $100' union select 'HUMANODE' as launchpad, lower('0xb8868D7e83b3DDd34E4b8af9d6E8446372416465') as sale_contract, '0.15', 'Whitelist $200' union select 'HUMANODE' as launchpad, lower('0xB9fC31dFEd66f25CFb03306C5532Bb78c0583d79') as sale_contract, '0.15', 'Whitelist $400' union select 'CRUIZE' as launchpad, lower('0x32F8BaB58891deDc226Ce760f155362ab44C1920') as sale_contract, '0.111', 'Standard Sale' union select 'CRUIZE' as launchpad, lower('0x18c6255Dc4C724FA434bd85F15Ba94E9739b556D') as sale_contract, '0.111', 'Vidia Standard Sale' union select 'CRUIZE' as launchpad, lower('0x288910AeFd8fCC99F7D6b4181154120aC00Ca85B') as sale_contract, '0.111', 'Vidia Unlimited Sale' union select 'CRUIZE' as launchpad, lower('0xa0445faa507B0dB311B3ab2549c27f12c1CF7792') as sale_contract, '0.111', 'Standard Sale Arbitrum' union select 'CRUIZE' as launchpad, lower('0x0F63351b3BcF77f36c51EeDbb1bD9417458046ee') as sale_contract, '0.111', 'Unlimited Sale Arbitrum' union select 'CRUIZE' as launchpad, lower('0x61114DC4b724beE3dFE8D61eC1B6146F1549c027') as sale_contract, '0.111', 'Whitelist $50' union select 'CRUIZE' as launchpad, lower('0x8062B74d14EfCD25A554eD04eD9be67Ae5F9E1A7') as sale_contract, '0.111', 'Whitelist $100' union select 'CRUIZE' as launchpad, lower('0x55834FD9AdB389992341D79048732B2CAfb1BB87') as sale_contract, '0.111', 'Whitelist $150' union select 'CRUIZE' as launchpad, lower('0x8CB23526F3e86533F825d40f661572Fa5Cb0C994') as sale_contract, '0.111', 'Whitelist $200' union select 'CRUIZE' as launchpad, lower('0x47E72666B840f2Ec30e2322D26B766dd9e8d525f') as sale_contract, '0.111', 'Whitelist $400' union select 'BRICKKEN' as launchpad, lower('0x1ccBF402baBeB5304ebC7a6f089d1d351e5076d8') as sale_contract, '0.13', 'Standard Sale' union select 'BRICKKEN' as launchpad, lower('0xD2a41F8b537Bc859e1f85315954789383a74D9ea') as sale_contract, '0.13', 'Vidia Standard Sale' union select 'BRICKKEN' as launchpad, lower('0x7C23332D4B58E56DCE93276Fe683d98E95BD45D4') as sale_contract, '0.13', 'Vidia Unlimited Sale' union select 'BRICKKEN' as launchpad, lower('0xE8e7B5564a1Bc0d6061e48a55532f4b03a948C87') as sale_contract, '0.13', 'Whitelist Gleam' ), all_purchase as ( select sum(amount_purchase) as amount_purchase, buyer from ( select raw_amount/1e18 as amount_purchase, from_address as buyer from bsc.core.ez_token_transfers where contract_address in (lower('0xe9e7CEA3DedcA5984780Bafc599bD69ADd087D56'), lower('0x55d398326f99059fF775485246999027B3197955')) and to_address in (select sale_contract from launchpad where launchpad = '{{launchpad}}') and block_timestamp >= '2022-11-01' and tx_hash in (select tx_hash from bsc.core.fact_event_logs where topics[0]::string = lower('0x2499a5330ab0979cc612135e7883ebc3cd5c9f7a8508f042540c34723348f632')) union select raw_amount / 1e6 as amount_purchase, from_address as buyer from arbitrum.core.ez_token_transfers where contract_address in (lower('0xFd086bC7CD5C481DCC9C85ebE478A1C0b69FCbb9'), lower('0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8')) and to_address in (select sale_contract from launchpad where launchpad = '{{launchpad}}') and block_timestamp >= '2022-11-01' and tx_hash in (select tx_hash from arbitrum.core.fact_event_logs where topics[0]::string = lower('0x2499a5330ab0979cc612135e7883ebc3cd5c9f7a8508f042540c34723348f632')) ) group by 2 ), -- current_amount_in_wallet as ( -- select sum(total_transfer) as current_amount_in_wallet, to_address as user -- from ( -- select sum(raw_amount/1e18) as total_transfer, to_address -- from bsc.core.fact_token_transfers -- where contract_address = lower('{{token_address_bsc}}') -- and to_address in (select buyer from all_purchase) -- and block_timestamp <= '{{snapshot_date}}' -- group by 2 -- UNION -- select sum(raw_amount/-1e18) as total_transfer, from_address -- from bsc.core.fact_token_transfers -- where contract_address = lower('{{token_address_bsc}}') -- and from_address in (select buyer from all_purchase) -- and block_timestamp <= '{{snapshot_date}}' -- group by 2 -- union -- select sum(raw_amount/1e18) as total_transfer, to_address -- from arbitrum.core.fact_token_transfers -- where contract_address = lower('{{token_address_arbi}}') -- and to_address in (select buyer from all_purchase) -- and block_timestamp <= '{{snapshot_date}}' -- group by 2 -- UNION -- select sum(raw_amount/-1e18) as total_transfer, from_address -- from arbitrum.core.fact_token_transfers -- where contract_address = lower('{{token_address_arbi}}') -- and from_address in (select buyer from all_purchase) -- and block_timestamp <= '{{snapshot_date}}' -- group by 2 -- ) -- group by 2 -- ), sell as ( select sum(amount_sell) as amount_sell, sellers from ( select from_address as sellers, sum(raw_amount/1e18) as amount_sell from bsc.core.ez_token_transfers where tx_hash in ( select tx_hash from bsc.core.fact_event_logs where topics[0]::string = lower('0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822')) and contract_address = lower('{{token_address_bsc}}') and from_address in (select buyer from all_purchase) and block_timestamp <= '{{snapshot_date}}' group by 1 union select from_address as sellers, sum(raw_amount/1e18) as amount_sell from arbitrum.core.ez_token_transfers where tx_hash in ( select tx_hash from arbitrum.core.fact_event_logs where topics[0]::string = lower('0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67')) and contract_address = lower('{{token_address_arbi}}') and from_address in (select buyer from all_purchase) and block_timestamp <= '{{snapshot_date}}' group by 1 ) group by 2 ), buy_extra as ( select to_address as buyers_extra, sum(raw_amount/1e18) as amount_buy_extra from bsc.core.ez_token_transfers where tx_hash in ( select tx_hash from bsc.core.fact_event_logs where topics[0]::string = lower('0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822')) and contract_address = lower('{{token_address_bsc}}') and block_timestamp <= '{{snapshot_date}}' group by 1 union select to_address as buyers_extra, sum(raw_amount/1e18) as amount_buy_extra from arbitrum.core.ez_token_transfers where tx_hash in ( select tx_hash from arbitrum.core.fact_event_logs where topics[0]::string = lower('0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67')) and contract_address = lower('{{token_address_arbi}}') and block_timestamp <= '{{snapshot_date}}' group by 1 ), final as ( select coalesce(buy.buyer, cla.claimer, sel.sellers) as user_address, total_purchase_amount, coalesce(amount_claim,0) as claim_amount, coalesce(amount_transfer_out,0) as total_transfer_amount_out, coalesce(amount_transfer_in,0) as total_transfer_amount_in, -- coalesce(current_amount_in_wallet,0) as current_amount_in_wallet, -- coalesce(minimum_balance,0) as mimunum_amount_in_wallet, coalesce(amount_buy_extra,0) as amount_buy_extra, coalesce(amount_sell,0) as amount_sold from ( select sum(amount_purchase/{{IDO_PRICE}}) as total_purchase_amount, buyer from all_purchase group by 2 ) buy left join ( select sum(raw_amount/1e18) as amount_claim,to_address as claimer from bsc.core.fact_token_transfers where raw_amount > 0 and contract_address = lower('{{token_address_bsc}}') and tx_hash in (select tx_hash from bsc.core.fact_event_logs where topics[0]::string = lower('0x884edad9ce6fa2440d8a54cc123490eb96d2768479d49ff9c7366125a9424364')) group by 2 union select sum(raw_amount/1e18) as amount_claim,to_address as claimer from arbitrum.core.fact_token_transfers where raw_amount > 0 and contract_address = lower('{{token_address_arbi}}') and block_timestamp <= '{{snapshot_date}}' and tx_hash in (select tx_hash from bsc.core.fact_event_logs where topics[0]::string = lower('0x884edad9ce6fa2440d8a54cc123490eb96d2768479d49ff9c7366125a9424364')) group by 2 ) cla on cla.claimer = buy.buyer left join ( select from_address as transferrer_out, sum(raw_amount/1e18) as amount_transfer_out, 'Transfer' as types from bsc.core.fact_token_transfers where contract_address = lower('{{token_address_bsc}}') and block_timestamp <= '{{snapshot_date}}' group by 1 union select from_address as transferrer_out, sum(raw_amount/1e18) as amount_transfer_out, 'Transfer' as types from arbitrum.core.fact_token_transfers where contract_address = lower('{{token_address_arbi}}') and block_timestamp <= '{{snapshot_date}}' group by 1 ) tra_out on buy.buyer = tra_out.transferrer_out left join ( select to_address as transferrer_in, sum(raw_amount/1e18) as amount_transfer_in, 'Transfer' as types from bsc.core.fact_token_transfers where contract_address = lower('{{token_address_bsc}}') and block_timestamp <= '{{snapshot_date}}' group by 1 union select to_address as transferrer_in, sum(raw_amount/1e18) as amount_transfer_in, 'Transfer' as types from arbitrum.core.fact_token_transfers where contract_address = lower('{{token_address_arbi}}') and block_timestamp <= '{{snapshot_date}}' group by 1 ) tra_in on buy.buyer = tra_in.transferrer_in -- left join ( -- select current_amount_in_wallet , user -- from current_amount_in_wallet -- ) cur on -- buy.buyer = cur.user left join ( select amount_sell, sellers from sell ) sel on buy.buyer = sel.sellers left join ( select buyers_extra, amount_buy_extra from buy_extra ) extra on buy.buyer = extra.buyers_extra ) select * from final

IMPOSSIBLE BUYBACK CHECKER