Hey, welcome to the solution video for this section. Just a reminder that this is really just kind of a worked exercise because writing to the public mode database isn't included in the free package. So the way we're going to do this is I'm going to walk you through an exercise, a worked example, and then you're going to answer some quiz questions at the end just to make sure you get the idea. Okay. First concept I want to point out here is something called Liquid tags. I think that's what mode calls it. Really it's just a variable. What you should notice is that there's something that doesn't look like SQL showing up there and down here. So really being able to put a variable into your SQL is one of the keys that you have to figure out in order to unlock scheduling queries to run. So I'm just going to show you what it looks like here. So I can pull the user ID, and then I just wanted to insert this date into the column just so you can see that I can put it anywhere I want. This query that I've written doesn't really make any sense, it's nonsense. The purpose of it is just to show you how it can fit in. I might want do something a little bit more meaningful if I wanted to just look at users who were created before a specific time. So we had created a less than or equal to this date. That might be a more meaningful thing to query. There we go. Okay. So that's just an example of how you can use this. Okay. Now let's jump back a bit and remember our task. Our task was to create a user info table. That needs to have a whole bunch of insert the user ID box we can see it. But also a whole bunch of other columns like whether or not the user was created today, whether they're deleted at all, whether they're deleted today, and then some other things related to them having quoted anything. So I'm going to hustle through the creation of this query because I want to focus on the date related parts of the query. In the next module, we'll spend a lot of time just talking about how to plan out big queries. If you want extra practice, this is a problem you can go over again and do on your own just the query part of it in the public version of mode. There might actually be other ways to solve it that you like better than the way I've done it. Okay. So let's recap what's going on. I want to start with a list of all the users who existed on the day in question since starting out with this from users here and that's where I'm getting the user ID. Let me put in the users table just so it's super clear where that was coming from. I've done a good thing which is to rename that user ID because it's no good to just have a column called ID in that query. Okay. Then just using the users table, I can answer some of the questions like created today. So I can say, if the user is created that day is equal to the number put single quotes. One thing I really like about the style guide that I use, is that makes it very easy to use this kind of bulk editing features. Okay. So I want to restrict the created ID date for the user to be before the day in question or equal to the day in question, and that will give me the created_at Boolean variable. I catch in the bottom of this, I'll catch anyone who has created after that and pull them out with this where clause. So I'm only looking at people who were created before here the 1st January, 2018. So you can do something similar for is deleted and is deleted today. Then when I want to ask questions about whether the user has ever ordered, what I'm going to do for that division with orders here so we have this sub table. Here, I'm just taking all of the distinct user IDs from the orders table where the orders created_at date is before 2018, January 1st. That gives me a table of people who've ever ordered. They've ordered multiple times. They'll just show up once in this list. So I don't have to worry about what this left outer join will do. If the user ID that I join in is null, that means they don't have an order. So that shows up here. Users with orders, that's the name of this table. If the user ID is not null, that means they have an order, so I'll put the answer as one, otherwise it's zero for have they ever ordered. We'd need that kind of a question, "has ever ordered"? A similar thing for users with orders today. So I have a very similar subquery where the created_at is equal instead of less than or equal to this date stamp for them to also put quotes. Then stuff all over the place. Okay. So and then I've joined that in kind of the same way. So I don't really love this because I'm taking the same table orders and I'm joining it in twice. But the reason that I'm doing that for this example is because I think gets more clear. I don't have to deal with any weird edge cases where the user has ordered in the past and ordered today. I don't have to bring any aggregation functions into this. I'm really just keeping it pretty simple. Let me just see if this runs the way I expect it because I need to do other lots of editing [inaudible]. Okay. Here is all those little apostrophes. I don't use this particular variable where not very often. Okay. So now we have this user info table that we want. If I wanted to do it on a different day, that would be very easy, I'd only have to change it in one place which is very convenient. Okay. So I'm going to create a table to put this stuff into. That is exactly the way I want it to be. So user ID is going to be an integer, the created_today, is_deleted, is_deleted_today has ever ordered, ordered_today are all integers with just one digit because they're just binary values, yes or no. Then ds I guess is the date. Okay, and there's no primary key for this table because users are going to show up once for every day that they've existed. Then I can insert this into the table by just sticking this in there [inaudible]. Here is it. [inaudible] You know reading their decision to join the orders table to itself [inaudible]. The fact that it's running as long is probably a good sign, at least as expected, all right. There you go. [inaudible] [inaudible] is normally if something is going wrong, it will very much more quickly. So here's the three things that I'm not going to cover in this video, I'm just going to leave to tickle your brain. Those are kind of data engineering questions. So the first question is, how do you check that the table is doing what you'd expect it to do? If I, for example left out that where clause at the bottom here where users are created out before this date stamp because mostly I was just running it on the data from today and so didn't really ever come up until later when I tried to rerun an old day. How would I even know that I was doing it wrong? We covered some of these skills in the first module, how to go through and check that the table contains what you would expect. So being able to group by day is one thing that you might be able to do. But you have some other tools already, and I'll let you think about that. The other thing that we haven't really talked about is how to insert into a table just by day without making a mess of it. If we want to insert into rather than replacing the whole table and just tack on day by day, how do we do that? If we mess up, I mean I certainly messed up a few things when I was trying to get the query ready. How do we make sure that we don't have to rerun it every single day because we mess up one day? So this is a little teaser for the next video where I talk about partitioning and Hive which is a tool that kind of helps solve part of that question. Then the last question to think about is, how do we backfill the data? I showed you how easy it was to fill any given day in. But we have data going back to 2013. I don't want to sit around and rerun the query that takes a whole minute for every single day, all the way back in time. So what's the right way to do it? Maybe the right way is to schedule it and just write a script that goes through and does that overnight while you're sleeping. Maybe the right way to do it is to write a query that is a fundamentally different query that's efficient for processing it over a whole wide range of time. The way to keep it updated is with this quick little cheap query that I've just created. So those are some questions, some data engineering questions to think about that I haven't really answered in this video.
