Analyzing Euro 2024 Soccer Data in SQL | Exploratory Data Analysis

Key Takeaways

welcome data scamps and data champs this is Richie so uh today we' got some more Sports analysis action for you I'm going to let you all argue in the chat about whether this is football or soccer and for those of you who missed it last Friday we had a session on building a powerbi dashboard around historic Euro Championship uh Team performances and today we're going to make use of sequel so I also there's a question uh in the chat from B about are there going to be any predictions for the your 2024 uh results in here so actually if you go to the resources there's an article in there someone did a machine learning analysis uh on uh the 2024 uh I think they predicted a Spain Netherlands final I don't think that's going to happen obviously England are going to win tomorrow so uh yeah uh that might not happen but if you're interested in like predicting the winners then please do check that out in the meantime uh Our Guest is Thomas Schmidt he's an analytics engineer at metabase and Thomas really is a full stack data scientist with experience across analytics engineering modeling and data storytelling he's also the creator of SQL mock which is the framework for unit testing SQL data models and previously he was a senior data scientist at Shopify and a lead data scientist at a grandom so uh very uh credential uh guest today uh please take it away Thomas thank you Richie and welcome folks um I'm very curious and happily looking forward to work with you through our uh task today um today is all about soccer again um regarding predictions we might look into some things but we mostly look into historical data but I will have a small dashboard for you at the end where you might be able to figure out your own way through euro 2024 um so let's see what we will cover today um I prepared a quick overview on what we want to achieve today so the data we are going to work with is originally sourced from sport monks that's a API provider that we use at metabase as well to create this dashboard that I'm going to show you later on um we already did some pre-modeling for you on the data so that it's a bit easier for us to analyze today and that we can like um save some time and look at some interesting stuff uh instead of just um yeah working with the data and bringing it to a format that is analyzable um so this is the data we're going to cover today um what we want to achieve in this session is that I want you to learn how to approach an analytics project from end to end uh with the focus on using SQL obviously the time is pretty limited today so we will try to go over those steps on a on a higher level but also dive into some things here and there and I'm also trying to give you some tips from my past experience that helped me a lot to walk through such analyzis projects and also to yeah not dump into some r holes that you can avoid maybe further down the road um a secondary goal is that there might also be some new SQL stuff that you haven't seen before so we will generally stay pretty basic um but there are some things that I'm going to show you Small Tricks that I'm using from time to time that sometimes make your life a bit easier so um can look forward to that um why is this something interesting so I mean there must be something interesting to you which is why you are here today um but in general analyzing data sets and especially identifying and covering the issues that we find in those is a key skill that you need for your data career so um I hope that when you go out of this session that you learn something that you can apply in your career or in your free time for your hobbies whatever you do when you analyze data um so um I'm looking forward to your feedback on that stuff and hope this is going to be helpful for you but now um enough intro let's dive into the problem we want to cover today so imagine you have a boss and your boss is invited to a garden party of his boss um where his boss is a huge soccer fan but our uh boss is not really savy with soccer or football or however you call it um so he would like to have at least a few interesting facts about the Euro 2024 that he could mention at this garden party so that it doesn't look like a complete loser during the conversations so this is what we want to cover today so we want to find some interesting insights that we can give our boss at hand for this garden party that he doesn't look like a complete loser how we're going to cover that is with four steps so we're going to start off by looking at the data at first so understand how it looks like whether we can see potential issues that we need to deal with somehow um what the distributions look like and so on um after that we're going uh to try a step that not everybody does in an analytics project but I found this quite handy in the past um which is modeling your data so sometimes you do a lot of repetitive tasks while working through an analysis you might use the same SQL Snippets all over join the same data all over and so on and this is something that can save you a bunch of time up front so we will try to look at this a bit further as well um after we've modeled the data we will do some deep dive analyzis and today we're going to look specifically at game events more specifically at goals because this is usually the interesting stuff um and then at the very end we will keep a couple of minutes to show you a potential form of presentation so since this is an kind of on still ongoing event so it's almost at the end unfortunately Germany is not in there anymore um but uh since this is an ongoing thing it usually makes sense to have some kind of monitoring format where at dashboard is a pretty good format of communication in this case uh normal ad hoc analyzers sometimes only have reports as an outcome but in our case a dashboard makes a lot of sense so I will show you um how we build a dashboard on top of that at least on a high level I will show you the dashboard and then there will be another session if you're interested on how we build it exactly later on okay but now let's jump into our session um I hope this is big enough if not uh feel free to comment and stuff and then we can adjust things um so this first part in The Notebook this should be the notebook that you folks also hopefully have open um there are some some data resources in there everything should be covered so we basically have no setup to do ideally um what uh it's written here is basically the intro that I just gave you so we can skip over that and we can dive right into the first task so whenever you start a data analysis there are two things that I recommend you do up front so the first thing that a lot of people neglect and don't really do is make yourself clear what your audience is so um this is an very important point in terms of how you want to communicate to them later so in our case it's our boss we might know like what language he speaks uh kind of what terms he uses and then we also know the whole condition around it so we know this is going to be at a garden party so whatever we bring out there as interesting facts should be maybe easy to digest not super complicated to explain so that it can happen naturally that they are integrated into a conversation so this is a really important thing you need to think about up front uh and I recommend you to really spend some time on that before you even dive into something the other thing is that you should make yourself clear what you actually want to achieve uh and this sounds silly but um I bet if you look back at certain analyzes that you might have done and I'm super guilty of that as well there are a bunch of opportunities for rabbit holes so usually you start looking at the data and it's like oh this looks interesting so you um try to follow that path a bit more and you follow it more and there's another interesting insight and get another one and then suddenly when this is your path where you normally would like to go you're totally off um and maybe spend three or four hours just looking at some stuff then totally wasted your time um so this is why it's important to think about what you want to achieve and in our case what we want to look at as I said is that we want to look at goal events and understand those a bit further um especially we want to see when they are scored in a game and then I want to look at a phenomena which I learned that some people only sometime somehow only people in Germany know uh but in Germany the commentators often talk about Joker players that are substituted very late in the game um with the hope that they would then score a goal right away so we also want to look at that whether we can see such patterns and such effects in the data because this might be a pretty interesting fact uh especially if it's only known in Germany maybe uh maybe not um but it might be an interesting uh conversation start as well okay so now enough about audience and what we want to achieve let's have an actual look at the data we want to work with today um so usually when you start looking at the data you want to get a first highlevel overview um initially so you want to understand how does does it look like can I already identify any weird values in there any missing values or something like that and for that you can simply start off with a super simple select star um and in our case there's something special to this because we have these game events as a CSV file um and the query engine that we're using here in this notebook is duct DB duct DB is a super hot new thing it's very flexible in our case it allows us to also basically analyze a CSV file using SQL which is quite handy for the session today um so you can treat the CSV file as a table and just select from it and do aggregations on top of it so let's have a first glimp at maybe the first 100 rows to get an understanding of how this data set looks like so what you see here is that there's a one row per game event um there is an ID field that's the ID of that event the name of the game what type of event is it like a substitution Yellow Card Gold and there are a bunch of others as well um there is a created ad field of when the uh event actually happened a team name for the team uh this event happened to player name so the player who kind of did the event depending on uh the context or here in substit subtitution it means that this player was uh substituted basically went into the game and this player went out so this is the related player we already see here that seems that there's not always a value for the related player so maybe something to keep in mind for later um we have a result which seems to be the result at the end of the event uh and here you see it's null almost everywhere here and only for the goals we seem to have those values so that's also an interesting observation and then if I scroll further here you see that there are some game minutes and an extra minute um there's this pattern let's see if we can find something yeah here so um the minute basically reflects the minute in the game as the name says but then when you have some extra minutes on top so let's say the game is a minute 90 the the referee says okay we play two additional minutes then those extra minutes will be reflected here so if the event happened in the second minute after uh 90 basically in this kind of minute time additional time then this will be reflected here another interesting thing which we're going to use later as well is previous player events so those are events that happened to that player previously um you also see that there's a bunch of null values here but here's one example if I scroll further here so there's an yellow card event uh for this player here from Romania and the event that happened right before that um was a goal event then there's also a time stamp for this PR previous event and also a column that we added already for the seconds after the previous event so basically 1,56 seconds after the goal this player um got a yellow card assigned and then at the very end here we have a couple of ID fields that we could technically use to join the data and we will not use this today so don't worry too much about those but just that you saw those um in case you walk through this thing on your own once more then there's you also another description on what those columns mean so we can skip that since we walk through that and then now after having like a broad overview we want to dive a bit deeper into things so another really important thing to do up front is to understand whether you find any duplicates in the row and with uh in the data and with how many data points we actually uh are going to work so what I like to do up front a look uh up front often is that I want to have a look at the row count but at the same time understand whether I can see any duplicated values so what you can do for that is pretty simple so we just do a count to get the rows but we at the same time can do a count distinct on the ID field to get the unique IDs and do this from our game events. CSV table and then we see whether those two match um if they wouldn't match then that's already a sign that there might be some duplicates but in our case we see that there's 838 events uh and there's also the exact same number of unique IDs that you see here um by the way in case you haven't seen this pattern of count one so usually um people learn this count star um they basically do the same thing uh the count one sometimes is a bit more performant but this really depends on the SQL engine um so I'm using those interchangeably so um if you see me doing count one that's the exact same thing as count star okay so now we know there seems to be no duplicates at least on the ID level but let's look a bit closer uh on the individual column levels um this is something where you usually can do a bunch of aggregation stats so you do do a average a median mean uh q24 etc etc um but in our case duct DB actually provides us with a very handy function that a bunch of other Curry engines also support uh in maybe a same or a bit different format which is the summarize function uh the summarize function is super basic so you can call it directly on a table or in our case also on a query so um if we go back to our select star query when CSV um then we can just put a summarize up front and what this will do for us is that you get one row per column with a bunch of aggregation stats that help us understand understand what this column might look like so you see the name of the column the type minimum maximum value approximate unique counts and so on uh and another thing that's quite handy for us in our case is this Nile percentage at the very end so this helps us to understand how many missing values are there and then we can look at that and try to reason whether this actually makes sense or whether there are some potential data issues that we need to take care of so I like to look at those null percentages first to understand what's going on so if we look at this one here you see like those don't have any null values and then this one is the first which is the related player name based on what we saw initially in the data that kind of makes sense because we saw not every event has a related player so that's kind of reasonable um uh that we don't always have a value here so that could be okay then we see the 84% here on the result since we saw initially at least in this small sample we looked at that usually only the goals have those results that could also make sense given that goals are not the most frequent event it could be that maybe there are more yellow cards and other things going on so this could also make sense uh maybe we can later get a better sense of how many goals we actually have to see whether this also fits or not then if I go to the next page we see that those three here have the exact same number and they are all related to those previous events which is a good thing because if those numbers wouldn't match then that would be a sign that something might be off so if like all those col columns relate to the previous event and some of them have values where others don't then that's maybe a yellow flag where we might want to discover and uh look look into it a bit further then what do we have here there's the player ID that's an interesting one um we won't dive into that today but if you look back then we saw that the player column actually always has a value or at least not a null value maybe it could be an empty string that could also be a null like value but it's not null uh and here we have 1% without um so this could be an issue as soon as we want to wanted to join the player data um but since we don't need any player data today since everything is in our event table we don't need to investigate further here but that's just I want to show you how you can identify those yellow flags but also comparing those columns with each other and see whether something might be off and then last we have those 37.1 for the related player ID if we quickly look back to the related player name we see it had the exact same uh null percentage so that's also sanity check that makes us a bit more confident that this could be okay okay nice so that's a thing um you can do so understand what are the missing values is it okay that they are missing uh and then we can dive into those aggregated stats a bit more in our case I only want to focus a bit on the minute and extra minute so there we see that the minimum value is one uh the maximum value 120 for the minute um which kind of makes sense if we take into account the overtime that can happen as well so it seems that we also have the overtime events here um we see that the unique count doesn't really match this one but that's also quite reasonable because like um the Euro 2024 is quite some games but not so many that we would expect an event to happen in every single minute so that's quite okay um and we see the extra minute also goes from zero to 11 with 12 unique values that also makes sense since we need to count the zero as well so we have those one one up to 11 plus the zero so that's 12 it's uh it's fine for our case and this is how we can go through those columns and try to understand what's going on it's very important to do those for the columns you want to analyze um and for the other one sometimes it also doesn't hurt to have a closer look because you might also identify certain issues but for our case we're going to skip that part and go back to the go back to the next task which is that I want to look at some distributions so usually if you have integer float or some numeric values then it can be quite handy to look at their distribution this also works for categorical values where you can do counts and then see how they are distributed but in our case we try to focus on the these numeric values now so let's start off by getting understanding of how those minute values are actually distributed um and to do so we can do a simple um count per minute value so we start off with the minute we count the number of events uh that happen at a given minute get this from our games uh game events. CSV table um and then we Group by uh the First Column I'm not sure whether you saw this pattern so when I started using SQL I always went like that so just put the column name here which is sometimes a bit nicer and easier to parse um but especially for the exploratory part I'm I went to always using like this format so one would mean uh this is the First Column we Group by if let's say there's some other column we had here then we could also Group by two and so on and so forth so this is maybe a small shortcut where you can also especially for these exploratory things you can easily exchange those stuff so I could put just another column name here real quick and I don't need to change the group by all the time so that's like a small trick I use from time to time um okay so let's see how this is distributed um so now we get a weird table with a bunch of values um but in order to make sense of that we can actually visualize it so we can just create a bar chart with the uh minute on the xaxis and the count of events on the y- AIS uh don't have any color because there's no additional um column here and now if we look at that so the first thing I see is that down here it's super messy so very hard to read um which minutes certain things happen in uh but that's something we can take care of later so we can maybe bucket those things together um that we don't spend so much time um trying to figure out what this actually is when you look at this chart um what I see immediately if I look at this chart are those two spikes here so it seems that a lot of events happen in the first minute of the second half of the game um there's also if you look at this first period uh it's kind of evenly distributed but then here at the end there's also a bunch of things happening uh which maybe could be due to the extra time since we saw that extra time the minute would stay 45 and then we just have one two three four Etc as the extra minute um then we have a bunch of events happening here in the second half which maybe could be a bunch of substitutions and stuff that don't happen in the first part so that's maybe something how I would reason about it if I just look at the data in this chart format and then what's pretty weird is this huge Spike at minute 90 so it's 76 events which is quite a lot compared to all these others um we know that the extra minute thing could have an effect but I would still like to have another quick look at what this spike is actually about um so let's try to dive into that a bit further and let's check what actually happens in this minute 90 um what we can do there is that we just go about so we select from our game events. CSV table again uh we only select the events where the minute is 90 and then what we want to do we want to count per event so I would like to see U which event occurs how often there so we could take the event type as we did before with the minute count the number of events and then we Group by the First Column again and maybe to make it easier to like visualize that stuff we just also order by the second column so I can do events now or I put the two here descending so that we see what is the most occurring event um okay so what we see here is that there are still a lot of substitutions happening in this uh overtime period maybe that's our Joker Players let's find out later um there are also a bunch of yellow card events which kind of makes sense because if I recall correctly there were a lot of games where there were many many yellow cards um being assigned to coaches players Etc at the very end of the game um so that's maybe something that's actually fine in that case we also see that actually a bunch of goals are happening here and then some other events as well um since we said earlier it might be interesting to see what how those events are generally distributed we can also easily uncomment that quickly and see how they are generally distributed so we see a lot of those events are substitutions uh some yellow cards also goals and then like a bunch of other stuff that we see there as well um by the way if you want to also like get an understanding of where those gos are distributed you can also easy try to add another the event type here to group by it and then try to uh tweak that graph that you see those things but I will skip over that for for the sake of time but feel free to do this on your own okay so the next one is your turn so I give you two minutes time um and I want you to try to do the same thing as we did before so get it a distribution but this time we want to check the distribution for the extra minute and special exercise uh usually before you run queries it's a pretty healthy um Habit to think about what you expect um so I want you to try that as well so type the query and before you run it think about how you would this bar chart expect to look like and then we come together in two minutes and we check how things look like so let me open that timer here um reset it and I give you two minutes and then we come back together to look at your results all right just while waiting for everyone to complete that um I noticed you were making use of those uh duct DB extensions to sequel so summarize uh is very very cool feature that you don't get another dialc of SQL um are there any other um duct DB SQL extensions that you recommend people look into um honestly I also haven't worked too much with ddv yet so that's still on my curriculum so I definitely want to check it out um I saw that they have a pretty good documentation so you can also go to this dub website and and check out that documentation maybe we can also link it later in some resources uh but you will definitely find it if you search for for dub um so apart from the summarize and stuff I also haven't used too many things but I know they that they can some do some pretty crazy stuff so also um read that you can also uh like draw stuff from multiple databases within dub and those types of things so that's pretty handy for an analytics projects um so definitely worth checking out as I said it's definitely on my list as well yeah there a lot of very cool stuff there actually one of my favorite things is the fact that you don't need to write select star so if you want to select all the com you go straight into the front claw just uh save you a bit of time there oh that's good to know try that all right super I'm gonna dive off I'll let you go through the answers nice okay so let me see uh how we can quickly go back withit it's the wrong one oh it's the time I was like okay some weird sound uh coming here uh wait a second uh let me quickly make this small and go to full mode again okay um so you might have seen there is a solution here already for you uh when you work through this on your on your own at home so let's copy that and quickly check how things look like um so we get these results again let's do a chart and what's pretty interesting if we now again plot the minutes extra minutes on x-axis on the y- axis the event count um uh is that you see that there's a bunch of events or like most of the events in the extra minutes zero um which I'm not sure whether you expected it but if you think about it based on what we saw in the data it actually makes a lot of sense because the entire game is with extra minut zero and only in a really small fraction of the game we have those extra minutes uh which is why we see some events here um what I also would have expected is that there might be more events towards the zero and then they might uh like get less frequent because like obviously not every game has 10 minute 10 extra minutes so it's more like in the range of one two three maybe four so this is also where you see most of the events piling up and then here there are not so many events H not so many games where there could even be events during that time okay so let's continue um this was a brief look at the data so just a recap what we did we dived into it we tried to understand how the values look like what the distributions are where there are missing values and maybe potential issues we would need to take care of um so that's usually a good first practice to un get an understanding for the data before you move on um now what we want to do next is that we try to model that data so we want to get it into a format that we save us a bunch of time later down the road um so as you might imagine so in this case we don't need to join any data but in case you need to join it then it makes sense to pre-join information that you don't need to do this over and over again with every query you do so this is why um such a data modeling process might make sense and this is nothing that usually happens up front where you say ah okay this is how my model needs to look like but it's more like an iterative process so what I can recommend is that you you start just with a blank model it could literally be a select star from your raw data Maybe with some prej joints and then when you go into the analyzis part and you figure out that you uh repeat yourself a lot of times by transforming a column or something then it makes sense to add this to your model um rerun it and then you can reuse the data so before we dive into the model I want to cover two things that not everybody might have worked with um the first part is this lpad function um this lpad function uh the format in your in your query engine might be different or similar so uh but I'm pretty sure that this is supported by most of the query engines um what this lpad does that is that it takes a string um you so this is the string here you provide it with a count a character count so what should be the final character count you're looking for um instead of a string in this case duct to be so flexible that we can just use a number as well and then at the end you provide a character that it should use to fill the missing character Counts from the left hand side so there's also an rpad for the right hand side in our case it's lpad for the leftand side and how this looks like is shown in this example here so you see if I use a single character and want to have a character count of one filling with Zer um then nothing happens here if I change this to two character counts then you see that one zero is going to be pre-filled from the left hand side same if I um increase to three then you see we add two zeros here um if I have a string that's already two characters long and I want it to be two stays at that way same here for that integer 10 and what's something to be aware of is that it actually trims the stuff so that's something you really need to take an eye on so if we for example have 100 and want a character count of two it would just cut off after two characters so that's something you need to be aware of um if you want to use that then you don't mess up with your data that can easily happen in that case so now you might ask yourself where is this actually useful in our analyzis and there are those cases where we want to do certain operations to Strings uh to numbers and turn them into Strings by some stuff on top we will do this in a second so um if I take numbers like this here so going with the number one five 10 and 100 as strings and I order by that column what you will see is that the order is not what we expect from a number perspective but it's ordering them as a string so which means uh one comes first and then there's not five but then 10 and 100 because they both start with a one um and this can get quite annoying if you then create charts where you want to visualize that data and it still should have some kind of order um and for that you can use the lpad function uh to apply a simple trick um and what I'm usually doing there is that um you take the character count of your longest character to expect so in our case for when we work with minutes with the game minutes we saw the maximum value is 120 so it's three characters so we just fill this thing with empty spaces from the left and what happens if you sort it then by that column is that now the order is preserved or at least similar as for the numbers so it might sometimes look a bit weird but for this exploratory cases it's quite handy because now we can still keep these orderings as if they would be in the number World um and this can be quite helpful then for the bar charts and stuff like that um we will see this in action in in a couple of seconds another trick I want to show you is uh a small trick for creating histogram so that's something um I need at least I need a lot in my day-to-day life um that I want to quickly explore things want to do a quick histogram first to understand how those values are distributed because I don't like to just look at means or medians uh because they can give you a skewed picture so it's sometimes nicer to have have a look at the distribution as a whole and understand what's going on there and some of the query engines support a histogram functionality but sometimes it's pretty messy to work with it uh with the data you get out of that so what I'm Sometimes using is the seal function approach and how this works is that you use the seal function so this always rounds to the next upper number uh you give it a field you work with so in our case for example we can use those the game minutes you divide it by a bucket size so let's say if we want to have a bucket size of five minutes then we can divide this by five um then you seal the result and then you multiply it again with the bucket size so that might sound complicated but I will walk you through this example now so you see here if we start with those minutes so we just select the uh distinct minutes from our game events and order them by the minute then what you see here is that we have all those minutes we divide them by five then we get some fractional numbers um some integers um then if we apply the ceiling you see that we basically get our bucket numbers so this is the bucket number one going from zero to four then this is the bucket number two going from 5 to 9 and so on and so forth and then we just multiplied with the bucket size again and then you see you get those kind of nice bucket numbers so it's basically saying zero to five 5 to 10 not including the the five by the way um and uh yeah then it this goes further like that so you see now we transformed all those things into nice buckets um technically what you can also do we will skip this for for the sake of time is that you add a um a bucket start and then you can combine those into a nice string that says like 0 to5 or something like that and then it's even more readable um if you work with it but this can be a quick workaround if you want to create a histogram um real quick from your data um okay so now let's use those two things that we just learned to do some fixing on our data so there are two things that I would like to fix with you one is the minute and extra minute so on their own they are not super useful but I would like to combine them in a game minute category um and I would like this to be just like the minute whenever there's no extra minute and when we have a extra minute we would do something like 90 plus 5 if it's minute 90 with five extra minutes and combine those two like that um the next thing I want to do is that we um as we saw earlier up above here if we have all those minutes is super messy to work with them so let's create cre some uh buckets also some some categories out of those minutes that we can easier work with them um and how we're going to do this is in a multiple step approach um so you can Avo uh ignore this copy stuff so this is just for basically bringing it into another CSV file so we store this as ghost USV later um what we do here is a multistep query with some CTE um in case you haven't used CTE before it's a pretty basic concept in like a simple mind model could be that it's different steps that you do uh different query steps so in our case we do a step here first where we um only get the goal events with a new minute string and then based on that outcome here we s just select from that step um we do some additional Transformations so what we do first is that we use this lpad function that we had before that we can easily sort on the minute string and we create a quick intermediate column for minute string sort mble so that's using what we just used before and another thing that we're going to do is that we also only filter for goal events because we want to analyze goals later so we're not interested in the other events too much so this is why we can do this in our data model then we don't need to repeat this line all over again and there's also fewer chance of messing up and looking at all the events and suddenly communicating something that doesn't reflect the goal events so this is our first step after that um there's some more complicated logic here but I want to walk you through it step by step so we use these case when functionalities um uh first to create our game minute so when the extra minute is zero then we would just use this sortable minute string that we just created uh so this would just be I don't know 1 two 3 90 45 uh and if there are extra minutes we combine this sort of a string with a plus and the extra minutes so to get something like this here above so this is our game minute and now after the game minute we create our game period bucket uh and for that I want to do multiple things to simplify the data a bit more so first of all if the minute is above 90 then we just label this as overtime so we don't care too much about all those individual minutes that happen afterwards so we just give this a overtime label um Whenever there are extra minutes we also simplify it a bit bit so so if the extra minute is not zero then we concatenate this minute string here uh with a plus to get something like 45 Plus 90 plus um and for all the other cases we use our bucketing so you see here this is the the bucketing stuff that we used before to create those histogram buckets of five minutes um here we need to convert it to integer because otherwise if you if it's converted to string by this lpad later it converts it into a float that's something we don't want um so we just convert it to integer and then we also use this lpad functionality that we can easily sort for that stuff um and that's basically it the results will be stored to the ghost CSV then we can select from that table and the result just scroll to the very right to see our new columns um now you see we have this minute string sortable technically we don't need this column anymore but let's keep it for now uh we have the game minute and the game period bucket and now if we scroll down a bit Yeah you see some cases where there are extra minutes so for example here's 90 + 5 90+ 8 they both would be mapped to 90 plus and there are also some overtime things here for example minute 91 would be mapped to the overtime and so on and so forth so it seemed that what we did here actually worked now it's your turn again um what I want to do now is a small deep dive into goals so the first thing we want to analyze is when are those goals actually scored and for that we're going to use this new column that we created so this game period bucket um and you can try to figure out um when in the game are the goals actually uh scored so make sure to use our goals CSV this time so the new model that we created and the game period bucket column from there um this time you get three minutes because there's another bonus task so um if you make it in time you can also try to figure out what was actually the fast goal that was scored um you will also find some hints here um also on the fastest goals there are some hints so feel free to work through that and then I take a quick drink break and give you three minutes to work through that see you in 3 minutes by the way in case you're wondering this is not beer so this is a beer mck uh but I drink my tea out of that so don't worry I'm not drunk it's just noraly I got the time on pause now so got a couple of extra seconds we're going to have a few minutes at the end uh for questions from the audience so uh for anyone in the audience if you finish ear feel free to uh ask some questions to Thomas in the chat we're going to get to them at the end of this session e e so still take your time just slowly preparing to come back here okay so I hope you all had a chance to walk through that um let's look at that together and compare our Solutions so let me just copy that over and then we can talk through this a bit um so to get the um the number of goals that happen in a certain period we can take the Super stra forward as we did sorry this close this this is annoying um okay um to do this we can just count the number of events in a specific game period bucket so similar as what we did before by the way I'm also using this um CTE pattern a lot that's sometimes helpful if you do this up front even though there's just to select star at the end but this helps to like keep the rest of your query and if you have multiple Parts you can sometimes exchange them so this is why I'm Sometimes using that pattern uh but don't worry if you don't have that um so let's run this um again we get a couple of weird things here you see that our ordering actually works quite nicely um with what we did before and now we can try to chart this thing so let me put this game bucket on the x-axis the count on the y- AIS and then we see this here so let's dive a bit more into that um first thing I noticed is that seems that our bucketing and categorizing seems to have a nice effect so now we can actually see the numbers here on the x-axis so that's a bit more helpful than what we had before um we still see a spike here in the kind of uh extra time after 90 minutes so that seems to be an interesting time to start your TV for watching the game um there's also a couple of things happening in the first half here but also in the second so I guess if I would like need to recommend someone I would say maybe you can skip the first 10 minutes but then you should definitely be on screen um and then yeah after 40 minutes you can maybe also skip some things and then you should definitely tune in from 55 again uh that you see all those gos happening um so as you see I think a pattern that we could give to our boss is that the um the extra time is very exciting so it seems that there after 90 minutes seems that there's a lot of stuff happening and maybe many games are even decided during that period so that's definitely something interesting um that we can take away from that um next thing is the fastest goal so let's look at that um how we going to do this is also very simple so we just select from our goals model the game name the player name and the game minute um and we order by the game minute just look at the first 10 results and then we see actually that there are even two games where there were goals in the first minute the interesting one is the second one because this is actually um the fastest goal that was ever scored by a European in a European Championship um because here the goal was scored within the first 23 seconds so that's quite fast so maybe you've heard this in the news uh but that's definitely a cool Insight that we can give to our boss so um I mean now I told you this inside but how you would usually go about this is that you see this here you see that there are some interesting first minut goals so then you might start to Google for those goals and then you might also come towards that fact um that you can tell our boss okay then last exercise um in this whole thing is something that I already prepared and we will just walk through this and this is about these Joker players um so as I told you earlier at least in Germany we often talk about those Jokers and I would really like to understand whether we see something in our data so since we have the game events and also this previous event that happened to a player we can at least see whether like after a substitution there was a goal right away if there were other events in between we couldn't tell so let's say you got substituted then you get a yellow card and then you um score a goal that's something we cannot easily get from that data we need to do some more fancy modifications but for our simple case we can try to check that the last event of the player was a substitution and I think what's reasonable could be something that we say okay they should have scored a goal within 15 minutes which would be 900 seconds after that substitution so let's go to that here and look at the query and walk through it um together so what this does is that we look at the game period buckets so we use them again um we create a new goal type where we um differentiate between Joker goals and normal goals and then later we count how many goals happened in a bucket per goal type um how we identify this goal typee is using case when again so whenever the previous player event is substitution and the seconds after the previous event are small equal 900 seconds which would be these 15 minutes then we call this a joker goal otherwise we call it a normal goal um and then we just count those things and order them by this bucket again well let me run this real quick I think it need ah hidden output um so looking at the chart here if we plot the x-axis and the y-axis and the go type as a color we actually see that there are quite some uh Joker goals happening at the end of the game which is an interesting thing especially here in the um additional minutes extra minutes after 90 uh minutes we actually have four choker goals um so that's also something which if we had more time we could also try to dive a bit deeper into that and see which types of goals those are um I know that one of our the German players Nicholas filr I think he's one of those choker guys and I think he even scored two goals um that were like that uh so that might be an interesting thing to further explore if you want so feel free to do this um after the session as a small homework thing um speaking of homework uh I also left you a bunch of other things which we won't work through that you can try on your own so feel free to do this after the session but now as a last step I want to quickly show you what you can build on top of such a data um set so in our case as I said earlier it's something which makes sense to monitor since it's like an ongoing event so you could build a uh a dashboard out of that and since I'm working at metabase uh we try to build something like that on metabase so let me show this to you real quick zo in a bit um so this is a uh the dashboard um you basically have an overview of the things and you will also notice a few things that we look so for example here when are the goals goals scored um so you see those things nicely visualized here technically um if you have such a dashboard uh so in your case the link that you're using is a public link where it's embedded so there you cannot drill through but in my case I could for example say hey I'm interested in what are those goals and then I could try to explore further so that's the advantage of a dashboard um that's something that we try to do well at metabase that you can easily drill through those things without a bunch of setup so I could go here click at see those goals and then I would see the the raw records from that here but going back to the dashboard um feel free to explore further after the session as well um you see that there's like a general stats overview um we have some kind of the past and upcoming games so these are the upcoming games there's not so much left anymore um I Heard Richie saying that he's really interested in England winning um so we could maybe have a look at that one so maybe we click on the game stats here uh to dive a bit deeper into that game so you see there's like the the whole overview but then we also have things for specific games so we could for example in this case try to see what are the winning odds so it seems that actually the odds are quite good for England so maybe that's a good sign for Richie um we have some comparison of the age match predictions we could also look at England itself to maybe see how they performed over time so how many wins draws goals they scored so free to explore that on their own on your own so there's many more you can look at and there's also some crazy stuff that you're able to build um on top of such data so if we for example go back to the German game where we unfortunately lost um you might also find a few things U where we compare certain stats against those teams so that's something you can do on top of such data to monitor stuff um you can for such games it's also interesting to look at the pressure index so like which team is putting more pressure on the other one over time or what we also try to look at is some ball heat maps and stuff um so but that's already uh covering a lot of that stuff so uh in the sake of time I will leave this uh exploration to yourself if you're interested in how we build this there is uh going to be another webinar which is also linked in the notebook um for metabase side where we will go through how we build this dashboard and maybe you can also learn a couple of Tricks there if you're interested um so that's it from my side thank you for walking through the session uh with me and and now I'm really curious about the questions so we still have some time left all right super U that was very very cool stuff and I do like that you got that um compliment of using data lab to do the exploratory analysis and then you got metabase for uh visualizing it and if you want to sort of share the results then uh uh yeah that metabase dashboard is bring actually I have to say um because it's using SQL rather than having to like learn a completely new language like um with parbi you going learn Dax and them and things like that just being able to create dashboards with seers is uh incredibly powerful wonderful all right uh we just got one question for the audience so far so um for anyone in the audience uh if you want to ask a question please do so quickly because we only got a few minutes left uh all right so uh this question from live is not really directly related to the train um saying is there a group for data analysis training so if you want to meet with other people data Camp has um a slack community so uh if you log into Data Camp you go to the top right click on the picture of your head there's a there's a link to the Select Community th