Dune Analytics 101 Workshop Session 2

fine I guess anyway I have a sweeter should be okay yeah right I guess we have enough people it's like 2 four six 78 is me that's okay right I guess we can start guys yeah let's get started thank you for coming for today's event because we we don't have like enough people actually it should be like more than 10 maybe like 11 but okay anyway thank you for coming thank you for I'm always with you and yeah today let me get started from our agenda for today it should be like more practical day for us for Dune Dune class uh did we start to recording yes all right cool cool cool uh then yeah we will be talking a bit about like new query edor like table overview how to collect all of this data what is like row data what is like decoded projects and like all of this and it should be like more practice because on last session we had a lot of like theoretical stuff from like you know aggregation functions so like more more and more but today is going to be more uh like coding stuff so I I I hope I will be a good teacher and you will understand all of this thing but yeah um actually actually let's get started and we can jump to like everyone because I see everyone got laptops so we can jump to to the Dune page main page and yeah we can see now uh the most popular dasboard is like about Stark Nets uh because stet airdrop is coming but yeah like good luck guys who is farming this but but actually I would like to say because uh The Third One dashboard is from richer it's my friend and he is a student from Sunway and he made this dashboard about fet uh fa it uh Bridge yeah like if he's listening us like yo bro welcome welcome but yeah um we also have our Dune class um team so feel free to send your actually how I can add you if I remember I can add you guys invite more people yeah using your email or username so feel free to send your it's better to send me your username than email uh to the chat so then I can invite you to the group and then we can share like some queries maybe disboard maybe then we can do something together like feel free let's go and let's grow uh this this this team yeah but then regarding our agenda let's go to new query and all right raise your hand if you have ever used dune before and D query if you CAU it like any squl queries raise your hands right see all right that's okay that's okay all right but yeah anyway tldr will repeat like everything and yeah that is this is like uh new query editor sorry about my voice because I got seek a bit so I will be not so loud and yeah we don't have enough people so it should be quiet and yeah thank you um so yeah and here as you can see we can get like some uh coder stuff so here we have like a main um how you say it's like code editor where we can type our SQL uh query like select from from Dex uh trades and I guess everyone knows what is Select and what is from okay let's repeat uh in SQL to get like main request you should write select it's kind of like you select some uh colums from obviously from some uh table or database so it might be like Dex trates or anything else uh but yeah uh a bit story actually how to get you can send your username to the chat like feel free because you are in chat uh yeah I will add everyone uh after this session I guess yeah and a bit story about how to get this uh all of these tables because you actually know because there is like a lot of tables like from I know a deposit from Dex trades from USC transfers from like ethereum trans actions like like everything and every like everything and everyone but how to get it uh so basically there's like a three layers of data uh it should be like like obviously layer zero layer one and then Layer Two uh it's like like on chain uh but layer zero when you get your yeah let's say you have like some script that grab data from onchain uh it maybe uses like I know RPC or AP I from other scan I don't know like anything so and it kind of works like you have this uh let's say python uh script right uh and some function like da uh get data uh and if you have like a new block because you kind of like um see at each block and get this transaction from block and for example your latest block is I know second and there's like like a new block third block and you get this uh data like okay uh if like my my my block uh is uh not like latest block then like you know uh get this data right like uh new data equals data for a new block like that and you get this as Json actually data it should be like okay you have I know uh block number three and then you have like a Json file or Json data uh like some I know block hash uh like I know Miner anything else right and like transaction TXS let me say TXS and then you have a list of transaction here like I know transaction one two three and it hashes and everything and you get this new block and then you can run a loop through all of these transactions so again you got this new block uh information right then you got like transaction uh hashes or transaction lists and then you can uh run a loop it's kind of like like everything in is in the script and you get for example like uh let's say transaction um should like like like this Json right again and it should be like inside hash like 0x blah blah blah blah blah like from uh again 0x blah blah blah blah blah uh to 0x and there and some logs right and logs again it's Json and it's kind of like how it works on script layer it's kind of like layer minus one and then you get this information about your transaction and then you can say Okay I hold this information inside like I know any of my database or local file or local machine right and then it's kind of like layer zero uh you hold this in some like storage and then you can say like okay I want to get like some tables because it's really uh uncomfortable for us to grab this data as like you know python or GS because there's like two t two not tables libraries it's python web tree and python GS so you can grab this data from onchain using GS or Python and it's not really uh useful when you use Python for example to uh like aggregate some of this data it's more comfortable to use SQL and for example like there is there should be some connection between like my python database like here like Jon and SQL editor like like Dune for example or uh there is like a icon it's a de Beaver uh we will talk a bit about about that a bit later um but yeah uh there is a script that convert your gson data from like layer one to layer from layer zero to layer one and it converted to and convert it and create a table and it grabs like this information like some hashes from two logs like everything and create this data frame if you have yet if you have ever used python you understand what what I'm talking about but for for other guys uh it should be not like Json file but uh data frame frame damn frame right and you have like like like a big table with your data uh like let's say first colum it's hash second colum it's from uh third colum it's like to and like other other other information and for example regarding Dune and yeah that that is like layer one so you hold somewhere like new database that is not like Json but that is a like data frame or like I know Excel table or just a table like a big table of your uh data and then yeah that is like layer one and for example regarding Dune there is like some main let's say it's main tables uh that you can build like like everything from these tables like I know gmix trades Unis swap trades I know a deposites like like everything from these tables because it's like main information about your transactions and this if we are going to yeah uh jump to row tables and then eum select etherum for example but there is like another chance but okay for for example let's select eum and there is like a four or three main tables uh it's ethereum do blocks it's ethereum do uh logs and traces and trans oh transactions like that so regarding blocks obviously it's some information about uh like block so remember you get this Json data and then for example if we going to have like uh let's say from layer one some information from layer one uh it should be like ethereum dot blocks and limit 10 for example let's run it and then yeah we got some information main information about blocks it's kind of like we collect this block and then collect some data from this block like I know time number gas uh difficulty size of this block hash of this block like parent hash minor knowns of this block date but date remember how to get date it's uh dat date from our time and it equals date in this colum and yeah this is kind of like main information about the blocks and it might be like on if on I know what else polygon BNB right like optimism salana but yeah salana it's not evm yeah let's say a bit later uh arbit room like an another other AR uh and yeah for example if you uh open either scan for example and select yeah let's don't select because yeah tldr on Dune they have some delay it's maybe like one minute so if you select like let's say first transaction from here like 15 seconds ago uh it might be impossible to get it from Dune because there is some di it's maybe like 1 minute or something like that so and for example if you like uh make some query uh that is like updatable every minute let's say or every second so uh it's kind of like blast breach for example right and you are tracking this tvl on blast uh and it's not yeah like on sometime it might be not the same amount of like money of if or uh like other tokens on uh blast trct on either scan and on Dune just because Dune has Del it's maybe like 1 minute but yeah regarding this let's select some latest transaction uh 9 second ago let's go to last 11 hours ago yeah why not and let's say it should be deposite why not looks like R okay um and then yeah for example here uh we see some main information about this transaction obviously it's like hash status block Tim stamp action but it's like feature of either scan right like from two uh d right like some information about like if like value gas uh gas price like gas fees uh input data and like all of this so it's kind of like you have this in information from like you remember layer zero you can get this information when you get this transaction it's kind of like you find this transaction grab this data from onchain and then you can like show it on the table right and obviously Dune has this information in ethereum transa right let me just use this feature transactions uh and where hash equals this hash and let's run it let me close this one and then I will have hopefully yeah I will have some information about this it's kind of like main information about your transaction uh and it's like block time block number value but you should divide it by uh e in power of 18 if I remember because like that's decimals for if if is 18 and it should be like you know maybe 0 do uh 62 right but just let let's check where's value no it's like 6.2 but just okay uh let's make a proof so we should type like value divide e in power of 18 as like TR value value and let's run it again and it should works now yeah and we got like 6.2 it's like uh your if that were transferred in this transaction and then like some information about gas about like gas price gas used but again you should it divide by here uh you should divide like to get like true guess price you should divide it by e in power of of nine and it should be like all right let's do it uh gas price divide nine as like truth guess and let's let's let's compare it yeah it's like 116 where's our guess H yeah here 116 90 yep 19 yeah and then like gas used Max gas fee price like some priority nouns um index and regarding this index it's kind of like a um number like like a order of this transaction in the block so if we going to have a look here it should be like yeah position in Block it's 96 and it's 96 here so just in case if if you can grab some data for like specific block and order some transaction like in order right uh by like index you can do it using index column uh then like success from two uh block hash some data and data it should be like our data that is here so yeah don't don't think it's like lo lo is like other other uh Direction so here in like ethereum do transaction or it might be like zking it might be like optimism but yeah data here in transactions it's data it's like a method in your transaction it's not like log it's not like some other stuff it's like your input data like for example here it's deposit right uh then like hash of this transaction typ and as at least I know what's that so you might be know uh but I don't really use it uh it might be like some maybe really useless information I don't know but yeah uh then what do we have else um we have actually like kind of the same information uh traces so for example we or okay me I use it usually for some if transactions if transfers so for example if you going to have like [Music] some transfers of your if like outcome or income to like some specific wallet like it might be my wallet or anyone else so you can use traces uh and analog of this table uh it's actually if yeah if we jump to let's say this uh wallet so analog on either scan it's internal transaction and traces it's kind of like let's say internal transaction on either scan and yeah you can see it's like really useful thing for tracking like if transfers uh like outcome and in income to your to your wallet and then the last one is locks looks uh let's go to to this transaction we have like only one look uh uh and it should be where is lo lo look it should be like TX hash if I remember data yeah TX hash so yeah don't get confused because some of like tables uses TX hash as uh transaction hash and some like uh uh transaction and and traces if I remember they uses uh they use hash as Hash Hash of transaction and yeah here yeah we see like some information about this transaction right as we saw for transaction table like block time block number uh block hash contract address but it's not a contract address that we um kind of use for I know your C20 transfers or it's nft no it's actually address here so you see it's like cc2 and you can remember that cc2 is robbed if and it's kind of like this guy sent uh this it's kind of like this guy called this function uh named deposite from robed if contract right and it's kind of like he sent this let's say message to Rob if contract uh and contract address here it's actually address is here uh and yeah we have like some topics here and yeah for those of one of you who doesn't know like what is topic zero uh it's kind of like um what's the name kick Chu of name of this function so I don't know like how to correctly pronounce kchu but yeah we usually Ki check 256 and if we have like let's say deposit here we can just copy and pass here remove all spaces it should be address address uint oh and it should be like this yeah correct so it's kind of like this uh I know evm machine right he uh converts your name of function function to to the like hash to the CCH uh using uh function name and yeah for example if you're going to grab like some um I know transaction specific for some logs let's say it's deposite right like uh R of if so you can just like okay you know that function name is deposit like blah blah blah address blah blah blah you and then you can just convert it to kchu and use like this topic for for um your your your query so you can just type like and topic Z equals um this this topic and yeah if it works because yeah you see topic zero is is our yeah and then topic one topic one here is DST I know maybe it's destination should be like that yeah and for example it might be like up to four topics if if I remember you can fix me if you know correctly but yeah uh for example here like um topic one why it's actually like 64 symbols here and it's uh just an address here because on Dune you have like um not decoded uh topics not decoded you see like here is data is like0 x00 z right because uh here on either scan you see it's uh deck or like decoded right decoded decoded here and if you click here like hex or here like hex you will see the same it's like zero year EB uh 0x yeah it's like topic one or data it should be like our data and for example on Dune of course you can decode it but you should know AI of this like function or this contract so you you can know that for example here it's number or it might be like topic one as address right or it might be like I know text or anything else so you should know to like like to understand to like like what what type of your value you should convert this uh this data or topic and then yeah like what else TX hash uh index but again uh it's not an index that we so in transaction it's index of this log it's like uh 215 it's 215 here so it's kind of like if you have a lot of logs like I know 1 2 3 4 200 1,000 like anything else and it's in order of like like some order from uh lowest to higher uh and and yeah here it's index of lock but not of transaction and yeah you see transaction index here they added this colum I guess recently because I have never used it before and yeah again block date from two uh it's like uh yeah you see like it's TX and it's going to be like information about transaction so it's kind of like you join this table with ethereum transaction and you got this information about transac action and you add add this colum to to logs yeah and just type like TX index or TX from or tx2 because yeah of course uh here in logs your log is actually uh yeah like sent to to like some contract but not to to like someone it's kind of like if you um for example what what what what what yeah for example if you have like year C transfer uh you sent your year C 20 or nftc 721 to someone and in transaction you see like okay you send from your wallet to someone like for example to Ellie uh some nft right and we don't see in the lock like Ellie's uh wallet just because we we sent this let's say message to to to that like nft or your C20 contract and call transfer function so for example here I have an agenda yeah because we should have a look at year C20 as well and yeah let's let's going to have like here see on if for example right and limit let's have the first random transfer yeah of some token and yeah you see here's like event TX hash because it's event uh we will talk uh about difference between event and call a bit later but yeah if you jump to scan copy past yeah you see for example here oh it's actually a really really complex transaction but yeah okay um and again we see like contract address it's actually a contract address of your C20 uh cc2 remember it's robed if and for example uh here we see like from Two and it's not like uh from home transaction was sent but it's kind of like a wallet who sent this talking like your C20 or nft to someone so yeah for for example here uh it's like uh 0x C9 right it should be oh it should be like our pool yeah here he sent this pool I mean he sent to to the rotor should be 88d 88d right uh some red if right on this value and again value it's uh and this value without this deal so you should connect like tokens uh table and grab this decimals from table or uh if it's like some specific token that you know you can just type I know value divide uh in power of decimals or yeah if you know it's like Rive so you you should divide by uh 18 decimals right um and yeah about about about yeah about here here for example how it's going in logs because remember you have like your row data on layer zero it just like Json files uh Json datas and you have like all logs like let's say you grab this transaction from on chain and you see like it's five five logs right and you see like a lot of like transfers syns swaps withdraws like everything but again we remember that we can can convert uh we we know that every token every year C20 standard token uses transfer function to uh get like this trans transfer from someone to someone right and we just can like again kick this uh I I I don't think we need this but just okay uh TR trust me uh we got like this topic because you see again we have like two of these topics so we can grab like two transfers from this transaction and we kind of like check it uh on our layer zero if my topic in this log equals this topic then okay like I I I got a transfer right and I can write or add some transfer like this log decoded again because uh we have like you see oh actually let me use this yeah because cc2 it's our contract that we got on Dune and we have like CRC I guess it's like okay from and DST it's destination maybe it's like from two right that we got in our table here right we just like decode our table because before decoded uh it's it looks like this but yeah we no zoom out yeah we just decod it to like uh we know that from and to its addresses we decode like all of two uh not first but okay first and second topic to address and data because there is like only one data and only one value inside data and we know that and we can convert this kick Chu to to the value not to to numbers sorry uh not to like text or address we know that it's number and yeah when we conver it we got like like this number and we show it on Dune right and the same with trans nft transfer and yeah I I think I hope you got it all right on this stab any questions any any any stupid questions or clever questions it doesn't matter anything nope all righty then and oh yeah uh regarding the qued project uh here we see like some other tables it's like okay most popular data sets that's okay just like uh I know top uses tables uh on zun like I know if everyone uses Dex trades right it's on the top if anyone you everyone uses uh your seat when you transfers it's on that off right obviously uh and but then we see like decoded projects and spells and what's actually that there is like a small difference between uh decoded projects and spells so uh for example for decoded projects like me as new project like I know g mix or AI I can come to Dun and say like okay you have like remember this layer zero uh with like like this uh let yeah let's say that I don't know uh you have like this information about like every transaction right you have like a really really huge data and me as I know AA or Unis swap I can come to do and say like hey yo can we create like a I know Unis swap trades table or a deposit uh table or like Bros or anything else right because me as a project I know that okay for a I have like four main functions right it's like deposit borrow repay and withdraw and okay flash Lo five uh and I know the schema of this transaction like uh how to build it like all of these hashes logs I know all of that and I can come and just say oh okay I know that um uh the contract that for example are uh like deposite contract right I know this hash and then I know that there is like topic zero it's like topic zero of deposit on a right and I can say like okay I can grab this data using my information uh and just may make some uh I know like where and uh like I forgot the word but yeah create like a new table because I know information about my project and I can say like okay for a um I can say like okay uh it should be like contract address right uh like 0x something and like topic about that and then uh if I will run it right I got my uh a deposit uh data set right for example here I have prepared this table for example because I'm working for red up it's kind like Dune on ton ton telegram Network and we are building like the same data set as dune but for tone blockchain and for example here just like my my work for this company I'm working for time part time here and for example yeah like I'm a gon project right I came to me and say like hey yo okay I have a root uh like address right I have like some toal wallets that works like I know binance deposites H did they connect No it should works but yeah all right uh just in Cas oh my mark is really bad failed oh because I connected to some we yeah but all right uh just for example here you can create like new table uses I cannot show you what the hell all right one minute let me connect to my Wi-Fi uh and I will show you let's go bro let's go what just happened uh all right for calls and events yeah uh I guess I saw like I have seen a lot of questions about calls and uh events because for example again on a V3 let's say um we have V3 let's say it should be if and then pull and we see that there is like a two types of tables it's like function and event and yeah for example there is like a two uh like table it's borrow function and borrow event and do you guys know how solidity Works raise your hand if you know if you have ever quoted on solidity cool cool cool cool cool then you know uh because actually how it works on like solidity side uh I'm not an expert in solidity but I have learned a bit about solidity and it's kind of like you have some function I know let's say it's a borrow right and you send some parameters and then inside this borrow uh you have let's say get um some a data um function like like I mean before here uh and you say like okay a equals this and then I know like something happens uh and then I say like okay my new apy is like something plus uh a I know zero like that right you you just like update your values inside this function right and then there is a um can I say function it's like emit uh or in in like like in our world it's event and you can say like okay Emit and I have borrow right with some parameters and it's actually event here so for example how it works on chain so it's kind of like me or you or anyone else or contract right like other contract he can call some function let's say it's borrow then he uh this this contuct this function call like get a data right then like something happened I know like uh he I mean I mean from from this function he get like let's say apy like tvl something else right then we update yeah we we we do something with this uh uh values like apy TL right it's maybe changed or not it doesn't matter and then we say like okay we have an event or emit um like uh uh I know data updated right and send like new API tvl new I know amount and something else and then uh maybe we have another event like like borrow right but then yeah it's kind like an event and here call for example get a because there is like some yeah uh receive data so and you see like call TX has so someone called this function right and this function get like some some values from onchain so kind of here it's uh I guess it's not empty yeah uh it's like contract address I guess it's like a pool for AI uh call hash call Trace uh right like some asset and some output like configuration data liquidity index like apy right uh current valuable borrow index stable borrow and like all of this and then after like you're uh you do something with with all of these values um you will have like some event emit uh called where is update yeah receive data updated and you kind of like show to everyone that you updated this values right here uh it should be like yeah like new liquidity index new liquidity rate like for this receive for this contract like new borrow rate new uh like there is like three three rates borrow rates uh two rates and borrow Index right yeah and kind of like that I guess I guess you understood uh what's the difference between call and event it's kind of like okay tldr uh event it's AIT or event inside your function right uh when you like do something inside the function and then you get this event and then this even show you something like this data for example as on this table um to onchain it's kind of like lock on on your uh transaction locks so it should be like event as well because transfer is event as well actually and you have like this event named transfer that print you like address it should be like yeah from address to address and value so it's kind of like uh you see on chain like three values from Two and value of of this transfer and call it's all right we can just go I guess to this contract right it's if we can go here we can write some contract and then uh withdrawal no there's no okay uh let's go to a maybe a constructs um not sure which one but let's say it's a d why not it should be like get data or something like that mean redeem redeem transfer transfer transfer yeah but it should be kind of like that like get something but it should be not not like here like you read some function and you get it and you don't need to pay a gas for this but it should be like like here right contract and it's kind of like a rule I know uh for some contracts like for UNIS for a he calls it and say like hey what's liquidity rate now and he get it on chain and he pay gas for this right and that is that is a call here for example right any questions I hope you got it any any because if you a table that shows all the W from into what why not you can just say like okay these thing wallets that ASN s like you need to get like wallets that have ever deposited to a and yeah you just can like use uh like like repay or redeem table who like got this money from AA right and distinct wallets from this table and then like okay connect to uh I forgot this this project radiant yeah uh and just just join it and it is but uh we we can get some task later I guess yeah like any question regarding calls and events if you didn't get it like feel free to ask anyway like really I will try my best to explain what's going on for solidity guys did you get it I hope so all right um then yeah regarding View and materialized view I I cannot connect to my table but yeah you see that there is like a view and materialized view here and for example for some tables on Dune you you you see uh materialized view as well here but it's empty now but it's going to be not empty I guess soon but yeah for example for again decoded project spells like uh not for row tables only for spells decoded project projects maybe like Community tables right you yeah regarding Community tables um it's kind of like you can upload your data set feel free right you just click here uh then you uh like upload your uh CBB file or anything else or just like Excel tables I know or numbers if you use Mac or anything else so you can name these tables and then uh you can use it here when I know let's say this guy and he got I know result referral rewards for rabbit hole I know why not so he got this table like uh yes he he grabbed this table like from somewhere like using API or anything else or it might be like I know uh let's say I got um like your IP from ZK sync for example right and I have this like in information about like uh number one column it's like your wallet and second colum it's like your IP so I can feel free like I can upload this table to to the Dune right and then got it uh and everyone can use it as well but yeah regarding views um there is for example spells uh like let's say you have uh again right I don't remember but it should be like your C20 oh damn where is where is where is where's my table Yeah here uh so for example when you run this okay let's see like okay I need to run this for like thousand no 10 100 100,000 rows for example right because you remember what is limit right cool cool and yeah I need to get like I know 100 rows 100, rows of this table and how it works uh because it's like a spell from someone so someone created this table uh like maybe me no I'm kidding not me um yeah and it's spell and how it works it's View and when you call this uh table right you you say like okay dun I need to get like this uh information from like this table and like some conditions like I know for specific contract or for specific wallet or anything else right and what what does happen on Dune side so when you call this uh table so actually here runs so you can just convert it like I know to uh select like everything from remember ethereum do loocks and I know like where topic zero equals and you remember like if I remember K Chuck yeah and trans fa like this so here on this uh like like query underground of your query uh and yeah like if you call it like more and more more time so if you need like to more um I know rows like let's say I know it's million or Millions right uh and he runs it again and again like new and new and he doesn't remember previous results just because like he run it again and again and he doesn't care right but for materialized view what's going on so you can just say like okay I have this table but every let's say 1 hour I need I need to update this table and I say to Dune or to my Deb for example because uh I guess Dune uses this one and you can say like in some parameters that okay I need to like upgrade or update or run my table every 15 minutes or every hour or every like 30 minutes right and you can set up it manly so and of course it will be faster but it depends on your machine if you have enough memory so you can run it and you get this table and then like okay uh if I would call This And if it would be a materialized view uh it should be already done but you see because it's running like uh logs uh tables like underground uh and yeah we we need to wait but if it would be a materialized view we don't need to wait because it's already done uh on on the back end like this any questions yeah and you see and we have some no damn it's laggy now and then let me close it all right then I have some practice task for you yeah and I'm going to do it with you as well so we can do like more practice I guess yeah we have time cool uh yeah let's go to Doom create new query and our task for today it's get average gas price for a deposit I know I just like a um and yeah for this one uh we should use like okay we remember that we have information about gas in if do transaction table right and for a deposit we we yeah we already have this table and we have what do we have okay for example I don't know I'm like a newcomer I have never used dune before I just come to Dun and okay I know that there is like a table are with three for example for deposits right uh so I need to find it I find it like no it's tokens I three right for then for if uh yeah right it's like um specific information but yeah let's say I know that uh this information is in pool uh table let's say I know and then yeah I select event and I see Supply it should be deposit but yeah for example right and how it works for example for for me if I've never used this table before as maybe like gmix for example I've never used gmix table so I can just type like here I need to see what's going on on this table and I can run it and see like okay I have contract address what does it mean I can go to either skin find it and it should be like okay I we pull with three so it doesn't care because you see it's the same for everything then I have like transaction hash it's like our unique transaction hash then we understand like okay I have it like information about this transaction in if do transaction table then I can connect my ethereum do transaction table uh using uh hash of this transaction right and then like some event index remember it's like our yeah let's just open it um let's go this and then yeah I have like 20 logs I have 155 yeah and it's our supply remember it's event it's like a uh a me to on chain that AI shows us that okay there is a supply for like uh with like such topics that is Ki CH of our supply function uh for or reive it's our token but okay for example I don't know uh and there is Hash okay we know uh there is our lock number uh there is time uh like some block number amount of something for example I don't know what's that I just come to view overv and see like okay what's going on here uh I see like this guy D4 where is my D4 it's uh on behalf of it's actually like our depositor and I know because there is like some proof because D4 is our guy who sent like a to a pool right and we see like amount it's like 287 287 with point because we need to divide by decimals right all right we know that this guy is our depositor we know that it's amount like referral code I don't know res it's actually our token it's all right I don't know I don't know then I can just copy this pass to my either scan and all right see that yeah it's contract address of AI and then user uh sometimes it might be not the same uh because as I remember user here is a wallet who sent this transaction so for example there is a bot like my bot who sent like some tokens to a or to Unis swap right and I just called this bot like sent a message to this bot okay like uh do a trade or do a deposit right and on like backend uh our user who deposited to AI it's my bot but Center of this transaction it's me so and user here if I remember it's uh Senter of transaction and yeah this coloon is like depositor and yeah we know all right our task is average price for deposites so I know that there's a hash then I can send say left join ethereum dot transactions uh let's say it's TR It's s on tr. equals s event hash right then we join two of our tables uh to to the same hashes right we have like a unique table here unique table here we just need to connect two of our tables and we can use like uh hashes because it should be the same and we should see uh like for this specific hash let's like what's going on on a side and on chain side I mean like for hashes like transaction sender gazes like maybe value if value why not and let's run it and try it should be not so long I hope so and while we are waiting any questions on this step any any no right I hope there's nothing uh okay just because because it should be really long I can type where uh my S my S my S even T HH equals yeah let's have an example as this transaction let's run it and and see yeah be careful with ethereum transaction or ethereum locks when you join two of these tables it might be okay like for transaction maybe not but for locks might be because you remember that this table storage or locks and if you join it like for uh some specific hash transaction hash right you get like uh not not one row with like all of your information from like a like Val you like depositor user I know to like from sender I mean like transaction sender uh value of if guess and like other but no you you will get like all of these 20 rows for each lock from like approval from uh receive data updated uh from like delegated power to like like more more more more more to transfer approval transfer means and like all of this and just be careful and if you need to get like um let's say okay here you left join just let's say uh ethereum dols oh let's let's actually try okay so you can understand it clear um so we can left join logs no let's say for this transaction uh L L one L uh it's t should be TX HH equals s s s and then you will get not not like this not like one row but you will get 12 row because should be fast let's go let's go let's go because obviously there is like 20 yeah 12 logs and you can see it's like for each log from let's order it by where is uh where where is index yeah it should be yeah it should be my index yeah and for example for topic zero it should be our first lock approval yeah and yeah just just be careful so for example if you need to get like uh supply for example or lo you need to type here you need to find it so for example it's like our latest row and then you need to say like and my topic zero equals uh this one right if if I need to get like Supply log or anything else so yeah should works I hope so yep and then you will get like only one row with your supply loog uh you see like topic zero it's our topic one it should be like our Reserve Yep this and like topic two it's our uh like depositor referral code and then yeah you see for example here in data you have like a two values it's user and amount and for example User it's uh like address and amount it's number but uh uh if it's like undecoded data it should be like like one line uh and for example like some technical stuff technically you need to separate it by 64 if I remember symbols and you will get like uh this for example right first and then like 0x 00000000 Z right like uh until 0 to 7f and like uh uh or if you don't know so you can just uh check if you have data like land of your data more than 64 then yeah you have like more than two values in your data and then yeah you just separate like uh 64 64 and then convert it from um from hex to and that is a question you need to get AI to know what is the first one what is the second one because if I don't know okay I can convert my first for example step okay I maybe think it's number or let's say it's text so and if I will convert this to text of course it's wrong but if I get AI yeah I can convert it and it should be like address and then amount uh it's the second one and yeah let's go back to our task um I hope we got it with looks let's join this one p p p let me go to overview and yeah for example here we have like a gas price is 31 G and where is my yeah gas limit and gas used here and it should Works faster I hope so no way but yeah all right back to our task we need to get average gas price so yeah for example we can actually get it in GUI or in USD how do you feel guys what is better in USD or B USD right let's go let's go there yeah and we got it and yeah like okay uh why it's so long I mean like your row is so long just because you have like everything and first part it's the part about your uh like first table it's I Supply right and it's like user refer code uh user and then like this one from from from this Until the End uh it's information about your transaction like block time again like value because it's zero right yeah it's zero uh from like gas gas gas so yeah we need to get a gas and let's check it should be gas price 31 31.4 yeah exactly so then we can say like okay um all right let's say block time for okay let's just change it for last one month let's say right and then we can type like where uh my block time it's now it's going like now uh minus interval Fu interal one actually okay 30 days and then average gas price s AVG uh guas price for deposits um should BS now let's try so yeah and here for example I guess it's really s oh yeah because I forgot to divide it don't forget it guys yeah you need to divide it by nine nine decimals and yeah all right and for example for now for like last one month like every user in average they pay 32 gu for deposit and it's actually true because yeah I used a uh like one week ago and I paid 27 maybe G because it's really massive I I can say yeah like compared to like compound to Mor for to other like um AER yeah it's more massive in in terms of gas but yeah for example USD how we can how can we get a USD we need to join our right so we we we get like um G price but then we need to multiply it by gas gas use divided by 9 uh because again it's um I don't know why 9 but it's kind of like if you multiply gas price by uh multiply gas used uh it should be maybe in if value and then decimals of if it's 18 and then you need to divide by 18 sorry sorry uh you need to divide by 18 but then yeah um we need need to connect price USD price price price price price [Music] price oh here here here yep price on let's say it's B on let's have a look at Price table it should be like yeah minute blockchain contract address decimal symbol price then yeah we need to say like our minutes equals uh date to rank um minutes from our block time uh because if I remember it's included like seconds and when you connect like your minute to seconds it's actually not equal obviously because it's kind of like um 2023 I know like uh December 2nd then it's like um 1 p.

m. 39 you know like 40 right and then you try to um to compare it with like now for example it's blah blah blah but then it's like like this so for for example it's not equal so then you need to convert your uh block time to minute it's going to like remove this and then yeah you get it easy um then yeah and uh I usually use blockchain is new for if because I'm not sure but if I remember they they collected prices from coin gek as well but it's kind of like offchain prices so blockchain here is no and yeah let's just have a look a bit uh from prices uh where for example Block Chain is null right and okay limit limit 10 let's have a look yep and then you see it's like you know some some Sim I know what's that but yeah they collected it from from coin geeka or from coin market cap I don't remember but uh or maybe from binance I don't know but it's like offchain prices and it's really useful for E for Bitcoin so for example on ethereum blockchain you have like Rob Bitcoin and Rob Bitcoin and bitcoin price sometimes it's not not like the same so and if you need to get like a true price of Bitcoin it's better to use like blockchain is n and and symbol equals eth and then yeah question for you um so yeah we connected our table to like our supply and transaction table then we have like this long long long row uh with price and then question we need to kind of multiply it by price but we need it to do like a price here or multiply it by Price here where just like really simple and quick question so you can understand it h you think like outside of brackets okay what what else how do you feel guys just feeling inside every minute has why why inside uh I'm almost as good as G but maybe every minute has this price yeah basically when you say like okay you have like this aggregator function like um okay let's say you have like like uh this table right and you have like a lot of prices or gas prices and you just like convert it to average number right and you get it as number here so it's kind of like let's remove it uh it's number already so and it's not like like a row right and it's number uh and here you average price so you have like prices for last 30 days and you just converted to average price so you have like I know daily or mly price and you just get an average number and then uh let's say I know here you have like uh 31 B right here and then you multiply it by I know what's the average price for if for last month I know maybe it's like uh 1,900 like this and you just multiply this by this and it's actually wrong because you need to uh multiply um like every gas in USD already in USD right it's hard to explain again um you have a row is like gas gas price gas used and price right and for specific transaction you have this like gas gas and price and you need to get like USD gas multiply all of these three parameters right you don't need to every like this like this and like this for like all transactions because you will get like this unique number this unique number and this unique number and it's actually wrong because uh sometimes for example some guy can pay gas when price like I know 4,000 right and it's actually like not average because he paid let's say 100 USD right for transaction and we need to include it in our average so we don't need to calculate like gu because maybe he paid like I know 30 or 20 gu but if price is very high and he paid more and we need to average uh USD price but not g so and then we need to include it here everyone got it I hope so all right let's try to culate it it should be not wrong but I will show you oh oops it's 31 bucks oh wait a minute it cannot be 31 guess let's say G oh yeah we got this error um so how to explain there is like really really interesting thing yeah let's just have a look for example for our transaction again we need to do like some uh like examples and take some transaction as an example and it should be like event uh TX HH if remember and let's select everything let's try because it shouldn't be like 31 bucks oh what H but actually price price is real but contract address is not oh so he paid 23 bucks oh yeah actually right oh yeah because I'm stupid sorry guys I forgot to divide it by nine let's try again what root gas price gas each you mean like here yeah because the gas use is the unit of the gas the amount of the gas cuz cuz now you you have like 0 something and then 0 something and you multiply . but yeah we we should divide by nine but do you after like yeah like you mean inside average or outside average so that means like braet gas price gas used and then divide one like outside of average you mean or no inside aage huh yeah how come we need to why do we decimals by decimals is like but it should be like that okay like let's try why do we need to divide G used by one9 yeah because gas use is the amount of gas it's not in price it's not inight right it's the unique it's not in way H what the heck am I stupid no what the hell oh yeah I guess yeah yeah yeah yeah that's my bad but it should be like this right bait but yeah guys just look at this Sky it should be like let's say gas right and then everything for this transaction yeah it should be like this this amount of gas it's transaction fee oh yeah then it's correct if it's let's say 23 or 30 bucks yeah maybe maybe all right all right yeah I'm stupid sorry sorry sorry guys just because I'm I'm sick and I'm thinking really slowly but yeah it's actually true but we need to just yeah g is here and price is here and it should be correct now all right comma yeah finally finally yeah like interesting it's kind of like 32 B and 31 USD not bad not bad it's kind of like you deposited 100 USD and PID 30 for gas profitable yeah and for borrow it should be like the same but just just just just um here you need to change it to borrow and it should works as well but let's say for borrow what's going on here it's a 36 USD you need to pay for gas when you borrow something or on a and yeah that is actually a task but for example here if again you need to see like what's the average gas metric for specific let's say talking right again I don't remember what's the toking where is the talking and I just type here I3 I3 uh where is my if if pool then event then Supply and I have Reserve here and what do you think guys for what talking what's the most interesting talking for you on AI you would like to check the average gas price no no no please not my metamask oops base let's say let's say USD what's the USD contract we can just where where where copy past here and then we get yeah average prices for USD and you see it's actually 20 bucks wow so in average guys pay 20 bucks for gas uh for last one month to deposit usdt to a well basically did you see a gas uh on base no no it's zero I was really surprised because I deposited my funds to a on Bas and it shows me like zero let's actually try sure sure sure uh oh cool I see your names yeah like for example for Bas uh right because we are so interesting uh what's the average gas price on base uh we can go to base and yeah like the difference between like L1 pools because you you have like um like I know polygon BNB no BNB there is no BNB uh like like Avalanche what else uh it's like some marketing for from for a it's like polygon gsis because it's X die native talking right uh Phantom Avalanche so you have like a lot of l1s but actually you have L2 as Wells as well and yeah for L2 uh you need to use L2 pool um and it should be the same event Supply and I guess it should be the same on behave of uh what else hash has hash um yeah hash uh and if I remember you need to pay in if on base right it's not like a base token but some Alpha for you base token is coming so you can find oh it's zero yeah it's like like oh because yeah yeah yeah yeah so for this uh re we forget to remove it because I guess there is no usdt on base let's try now oh oh oh oh oh oh oh oh we connected ethereum transactions with base right uh should works now base transactions base yep and you see it's like one cent they need to pay me for marketing because now you can go to base and deposit your funds and actually farming based toing op oh yeah yeah sure yeah you just need to change here and here optimism is lwa as well so you don't need to remove L2 El yeah my Russian is coming El what uh price are optimism Lo it's optimism uh oh because it's oh yeah it's just pool I guess they don't know that optimism is L2 but that's okay that's um I guess it should be the same like one cent maybe yeah it's seven wow it's a bit more all right any questions any maybe remarks any questions you would like to see here I mean for gazes maybe you need to get like specific query specific guess something specific and good question but do you remember what's gu I guess it's maybe gu because Bas really uh okay just because because I'm here and I'm a good teacher for you I'm not sure because I used base only um yesterday it was my first time when I used Bas but arbitrum is using op stock as well if I remember it's like L3 maybe that is why it's more cheaper than optimism but yeah for example here get us how much oh no you see my funds yeah pleas right let just check switch switch to if right and I need to pay like less than one cent for this transaction when I approve for example right it should be where is my internet yeah it's like like zero yeah you know because um usually usually like official teams they don't speak about tokens before like I know ZK seeing base they don't say like yeah like we have a plans for tokens but for example back in my Thailand trip I was talking with some guys with some fans who talk to base team coinbase team and it's kind of like you know just like uh talking with like some coffee cups and they just oh okay maybe like in the future yeah we are planning to deploy base talking but just you have a time to farm this and I guess base is not like a really cheap company so they have money but yeah it's actually our session for today thank you for coming and I hope your favorite human will say you let's get married thank