with txs as (

select

distinct tx_id

from solana.core.fact_sol_balances a

where 1=1

and owner = 'AVUCZyuT35YSuj4RH7fwiyPu82Djn2Hfg7y2ND2XcnZH'

and balance > pre_balance

and block_timestamp between '2024-07-01' and '2024-08-01'

--this gets all the txs where photon receives fees

,trades as (

select

a.tx_id

,max(swap_from_amount_usd) as usd

,swap_to_mint as token

,'buy' as buysell

,swapper

,case when swap_to_mint in (

'FL6XicwNJJV4LN6SoxR8qBhj3CVzLT7H6itE82xnPpJq','65Q8EaQjdH1zrAPthXb3uj6RCHwNd6fEr22BW76q648n','8c6cgn1LUjNFpF6LjaKwQVV69bhbUMukYVr3qNvsouF9','GNLEixQ2987RwRqsVmpj7WdaaDvwRo6cdYLu8xZMo4a7','2orbVLVVjQTuPSxdEPkLYNRHe1u5oJ2pjG59yGyKqpyC','3ZdDSbzifsXnd7uZYg79XnQxiQatPsV8SGvFS2634nBw','HgHFe7o9dAaMC9AgEgMcHAEffyF6csG8mdBLzMeQczbe','FPTECJbhciAysiv9VTD9ZNNX1SgY1Ei4kYuQDEsCvPZD','8hhD7BWnckWvEbAnCLFnKinUDzZBMuN3LiXKJTP9xKxh','5keovKMg3viMENFQsC6Z1r69JMe8CYLQM1NnsGvXfCYx','BmaHNyQcABWaJ4Rw1x3ad4fjHSE3Z6efoCP7attpvyZo','4cZpoZg3pPbgUt23zTi9Ty5jFkGP575KtkvsXA4eY5xw','DfdqAFExb59Mfu4KM8KzGfeCZMdWkBSeJgbnWN8nQab1','GcZmiMN1Gphukj3WzDGdzwCoNVeQpt63EaUjviXnmPCP','9S9f2MggHnf9EdKeHJyH8MpPQBnDZV7HvQqjGuYarHfS','4fWWweV3CwC9a7cXm8SLYDXNFZSnMn5gPAHHxjenEefJ','BUnBTQp9ndHHZmCRo9wq8SR6RB3BuavbNC2y8kNqtzYe','6UtpLrkgrquXkyc7DQdm9pn7Wus2UMRAmNpKmpHQ6eJw','Hmu5wMatr3iDTKnMGViuvPACcfGUno9g3ANgFTyMSDZo','2WxKi6wA7FwW4KPKCWLx2brCye1HsbTtuC3iJvgSES42','B9DMrytraYJT7iB3GRRgsngxDGL5kSDSRVH3BeNbWMC4','Eb9BZGusyzCxtJrUqM4rHf9Y5CHM6t3A1nV4EZdDCacR','CpdAChzFf365DcVu64QfVbdWVqGrsZ5X5FMcd1oBSou9','2dDwh7n2Tsjadr4XvPV7gXRChh9kP5ZgXr2LvYnKEJ6r','AowPKT4NHkx4eMax7nS5ZxijwuDfoeedBryLqq6nDZfH','AusTcg8xcfNz6FAz3ZASRy2yW2cZ12f4UxZQKac92YxE','Eb3VvC3ZiL9Tz3GNatcgqoCh32mZ3kmgSNW9aDUG7y5f','HDMA1qpgH3buZEVVxmAHVb1aLESjRzHmsgRDKZFLs2w1','GkaoCmgvX1Q93wH7rgKMCSTidzMEK523kL3hiTW7ntxS','7P5Qh5rMPyE5Cn47aedz93NKyjM83rA47isv43R1jUhA','DChbEmk2Eo3yZZuu8sGb3AXcnNAyBG9VKyqM66MTBZVv','J6cSqDQBmyfHmDJMzvABZarhgeB3k6uZ96YPPRHcQWDd','EkBAzM5mjKRnLTCUgz7T2JYUNy1gkkZpUoUHcaJXYoH7','44EP4AXi1x2u69Q5L6axci1NpWw42dY88HP1FL1cFi4L','BN5wdJ8D2P3eqhe838n2Z591nFbicdDChNDKN9QfdUKq','DhBSoopwAMNQB1H3etvkercdSpkq2X9JZxrFMe6Hwb1B','FArQbF1CZS4gCgVdn1RBmvNwT3RWrcR1QAqytaEhJCkq','63B8TM7jfNQwgnvkCoGrgkZamSMF4Qod6mDaMSMqAL8','Aw2TVf37xsUoRRHY1GWEzhhonmpPDcMWFQpmF9pD3UKT','FabCA9sPrLo4kckfXcvpNYQ2nBtndhnjUSy7otJXMYPM','5eLMc3zq9MffMEPu7qJiTsJn8XVtuLh1L9QHP2Fgi57m','9JnvZUKqrjBQF2E9ivZEXu9uP6P2by9ss2q9858nG2rs','6S2zTzu4E7k6GV4Tazb52FZNE9bjyxFz3KCGKT2WNPRw','Fg4bAiK7uMCR1UCkht2NyxXks5jgnqierVKqWkERXv52','HMDQsL5pv2BHhM12ynryGNGTocu8zTSSyJQMkixLSmjm','E26oe2htH2r9GMhP8tZXMkFrKBvPZYr1zeQ7RBJsh2kX','7RPCR3L3npW846jxpQj2Sq5JTYMRvQ1iU92qGeLfTpXq','6h5VDfdrmyWm2ebrzBcBtJf3DpkJygc4inX38HTdkkRv','9NM4gxhDwP8Vem19BFLe6jK1TwqLa5jbym8Zsn9CyZFU','9

) then 'rug' else null end as rug

from solana.defi.ez_dex_swaps a

join txs b on a.tx_id=b.tx_id

where swap_from_mint = 'So11111111111111111111111111111111111111112'

GROUP BY a.tx_id, swap_to_mint,swapper

,case when swap_to_mint in (

'FL6XicwNJJV4LN6SoxR8qBhj3CVzLT7H6itE82xnPpJq','65Q8EaQjdH1zrAPthXb3uj6RCHwNd6fEr22BW76q648n','8c6cgn1LUjNFpF6LjaKwQVV69bhbUMukYVr3qNvsouF9','GNLEixQ2987RwRqsVmpj7WdaaDvwRo6cdYLu8xZMo4a7','2orbVLVVjQTuPSxdEPkLYNRHe1u5oJ2pjG59yGyKqpyC','3ZdDSbzifsXnd7uZYg79XnQxiQatPsV8SGvFS2634nBw','HgHFe7o9dAaMC9AgEgMcHAEffyF6csG8mdBLzMeQczbe','FPTECJbhciAysiv9VTD9ZNNX1SgY1Ei4kYuQDEsCvPZD','8hhD7BWnckWvEbAnCLFnKinUDzZBMuN3LiXKJTP9xKxh','5keovKMg3viMENFQsC6Z1r69JMe8CYLQM1NnsGvXfCYx','BmaHNyQcABWaJ4Rw1x3ad4fjHSE3Z6efoCP7attpvyZo','4cZpoZg3pPbgUt23zTi9Ty5jFkGP575KtkvsXA4eY5xw','DfdqAFExb59Mfu4KM8KzGfeCZMdWkBSeJgbnWN8nQab1','GcZmiMN1Gphukj3WzDGdzwCoNVeQpt63EaUjviXnmPCP','9S9f2MggHnf9EdKeHJyH8MpPQBnDZV7HvQqjGuYarHfS','4fWWweV3CwC9a7cXm8SLYDXNFZSnMn5gPAHHxjenEefJ','BUnBTQp9ndHHZmCRo9wq8SR6RB3BuavbNC2y8kNqtzYe','6UtpLrkgrquXkyc7DQdm9pn7Wus2UMRAmNpKmpHQ6eJw','Hmu5wMatr3iDTKnMGViuvPACcfGUno9g3ANgFTyMSDZo','2WxKi6wA7FwW4KPKCWLx2brCye1HsbTtuC3iJvgSES42','B9DMrytraYJT7iB3GRRgsngxDGL5kSDSRVH3BeNbWMC4','Eb9BZGusyzCxtJrUqM4rHf9Y5CHM6t3A1nV4EZdDCacR','CpdAChzFf365DcVu64QfVbdWVqGrsZ5X5FMcd1oBSou9','2dDwh7n2Tsjadr4XvPV7gXRChh9kP5ZgXr2LvYnKEJ6r','AowPKT4NHkx4eMax7nS5ZxijwuDfoeedBryLqq6nDZfH','AusTcg8xcfNz6FAz3ZASRy2yW2cZ12f4UxZQKac92YxE','Eb3VvC3ZiL9Tz3GNatcgqoCh32mZ3kmgSNW9aDUG7y5f','HDMA1qpgH3buZEVVxmAHVb1aLESjRzHmsgRDKZFLs2w1','GkaoCmgvX1Q93wH7rgKMCSTidzMEK523kL3hiTW7ntxS','7P5Qh5rMPyE5Cn47aedz93NKyjM83rA47isv43R1jUhA','DChbEmk2Eo3yZZuu8sGb3AXcnNAyBG9VKyqM66MTBZVv','J6cSqDQBmyfHmDJMzvABZarhgeB3k6uZ96YPPRHcQWDd','EkBAzM5mjKRnLTCUgz7T2JYUNy1gkkZpUoUHcaJXYoH7','44EP4AXi1x2u69Q5L6axci1NpWw42dY88HP1FL1cFi4L','BN5wdJ8D2P3eqhe838n2Z591nFbicdDChNDKN9QfdUKq','DhBSoopwAMNQB1H3etvkercdSpkq2X9JZxrFMe6Hwb1B','FArQbF1CZS4gCgVdn1RBmvNwT3RWrcR1QAqytaEhJCkq','63B8TM7jfNQwgnvkCoGrgkZamSMF4Qod6mDaMSMqAL8','Aw2TVf37xsUoRRHY1GWEzhhonmpPDcMWFQpmF9pD3UKT','FabCA9sPrLo4kckfXcvpNYQ2nBtndhnjUSy7otJXMYPM','5eLMc3zq9MffMEPu7qJiTsJn8XVtuLh1L9QHP2Fgi57m','9JnvZUKqrjBQF2E9ivZEXu9uP6P2by9ss2q9858nG2rs','6S2zTzu4E7k6GV4Tazb52FZNE9bjyxFz3KCGKT2WNPRw','Fg4bAiK7uMCR1UCkht2NyxXks5jgnqierVKqWkERXv52','HMDQsL5pv2BHhM12ynryGNGTocu8zTSSyJQMkixLSmjm','E26oe2htH2r9GMhP8tZXMkFrKBvPZYr1zeQ7RBJsh2kX','7RPCR3L3npW846jxpQj2Sq5JTYMRvQ1iU92qGeLfTpXq','6h5VDfdrmyWm2ebrzBcBtJf3DpkJygc4inX38HTdkkRv','9NM4gxhDwP8Vem19BFLe6jK1TwqLa5jbym8Zsn9CyZFU','9

) then 'rug' else null end

UNION

select

a.tx_id