1 00:00:00,120 --> 00:00:00,480 All right. 2 00:00:00,480 --> 00:00:03,370 So we have this data in our Instagram clone database. 3 00:00:03,390 --> 00:00:05,939 Now we're going to ask some questions. 4 00:00:05,939 --> 00:00:11,670 And the reason I'm phrasing it that way, asking some questions is because that's usually what you're 5 00:00:11,670 --> 00:00:12,420 actually doing. 6 00:00:12,420 --> 00:00:19,380 If you work with SQL or SQL, some SQL database or any database really, even if it's like a MongoDB 7 00:00:19,380 --> 00:00:26,460 or Coach DB, post Grass, my SQL, any database out there and you have a bunch of data in there. 8 00:00:26,910 --> 00:00:29,730 Yeah, sometimes a lot of the time you're just sticking stuff in there. 9 00:00:29,730 --> 00:00:34,710 But when you're actually working with the data in writing queries, a lot of what you're doing is asking 10 00:00:34,710 --> 00:00:36,900 questions where you have a motivation. 11 00:00:36,900 --> 00:00:42,450 You're trying to figure something out, whether it's for an ad campaign or a post mortem after a campaign. 12 00:00:42,660 --> 00:00:44,550 I know I'm coming back to ads a lot. 13 00:00:44,970 --> 00:00:46,320 It might just be sales. 14 00:00:46,320 --> 00:00:48,660 Like you could be a woodworker. 15 00:00:48,660 --> 00:00:54,300 You make furniture and you want to figure out how much you sold last year because you're compiling your 16 00:00:54,300 --> 00:00:58,200 tax returns or something and you need to figure out the total volume of sold. 17 00:00:59,010 --> 00:01:00,960 Those are the questions you're asking. 18 00:01:00,960 --> 00:01:07,770 So you think in terms of English or whatever language you speak, you think in terms of questions in 19 00:01:07,770 --> 00:01:12,390 a non SQL language, like how many X did I sell last year? 20 00:01:12,390 --> 00:01:17,220 And then you have to translate that to a some or a select something from something else and you have 21 00:01:17,220 --> 00:01:19,800 to use some most likely anyways. 22 00:01:19,800 --> 00:01:24,510 So that's how we're going to work here and we'll start out nice and simple in this first video. 23 00:01:25,410 --> 00:01:29,850 Suppose that we want to reward the users who have been around the longest. 24 00:01:29,850 --> 00:01:34,470 So you may have seen something like this on not Instagram, but other applications. 25 00:01:35,370 --> 00:01:39,570 You'll sometimes get a coupon or just a thank you email. 26 00:01:39,780 --> 00:01:43,980 Thanks for being loyal over our five years or however long we've been around. 27 00:01:44,100 --> 00:01:48,300 So all we want to do is reward our users who've been around the longest. 28 00:01:48,300 --> 00:01:53,670 And to do that we need to find the five oldest users, the users who have been around the longest. 29 00:01:53,670 --> 00:01:57,600 So this one is should be simple, not crazy. 30 00:01:57,600 --> 00:01:59,070 We don't need to work with many tables. 31 00:01:59,070 --> 00:02:00,030 Short video. 32 00:02:00,210 --> 00:02:01,560 Try this on your own. 33 00:02:01,560 --> 00:02:03,090 Pause the video of course. 34 00:02:03,090 --> 00:02:04,860 And in 3 seconds. 35 00:02:04,860 --> 00:02:05,790 I'll be right back. 36 00:02:07,250 --> 00:02:08,300 And I'm back. 37 00:02:08,449 --> 00:02:15,500 So to do this, it's just a matter of basically selecting staff from users, and then we just want to 38 00:02:15,500 --> 00:02:20,750 order them based off of the date that they were created, the date they joined, and then we'll limit 39 00:02:20,750 --> 00:02:21,620 it to five. 40 00:02:22,250 --> 00:02:29,240 So if we hop over, what I'll do is make a new file, new file, where are you? 41 00:02:29,240 --> 00:02:32,540 And I'll just call it Solutions SQL. 42 00:02:33,620 --> 00:02:40,010 And in here the first one is just finding we put the numbers here so it's easier for you when you're 43 00:02:40,010 --> 00:02:50,450 looking at this, finding five oldest users and we know it's going to be a select something from users. 44 00:02:50,810 --> 00:02:57,230 And if we start there, you can see we've got 100 users, which is one thing I wanted to point out. 45 00:02:57,230 --> 00:03:05,000 100 users is not very much, but I capped it at 100 because basically the formula that I imprecisely 46 00:03:05,000 --> 00:03:13,250 came up with for how often users are commenting and posting and liking and tagging it grows exponentially 47 00:03:13,400 --> 00:03:14,960 depending on how many users you have. 48 00:03:14,960 --> 00:03:20,150 So having 100 users, we still have almost 20,000 records for everything else. 49 00:03:20,150 --> 00:03:28,490 And if I upped it to 200 or I tried 1000 users at one point, it really started going crazy and our 50 00:03:28,490 --> 00:03:30,170 queries take a lot longer. 51 00:03:30,170 --> 00:03:31,550 So I decided not to do that. 52 00:03:31,550 --> 00:03:36,470 But if you want to try, it could be fun to if you feel comfortable writing code to insert a bunch of 53 00:03:36,470 --> 00:03:40,400 users, do that in JavaScript or Python or whatever language you like. 54 00:03:40,520 --> 00:03:47,420 But for now, 100 users, it's just interesting to note how much data we get even with just 100 users. 55 00:03:47,420 --> 00:03:53,900 If the average person is posting X number of times and then we get certain celebrities who post all 56 00:03:53,900 --> 00:03:57,170 the time, not to mention people who are liking all the time. 57 00:03:57,170 --> 00:04:00,920 And we get bots who like every single photo, things quickly grow. 58 00:04:01,610 --> 00:04:09,020 So select start from users, but we want to order by and our field is called created at. 59 00:04:09,860 --> 00:04:12,290 Right there So just order by created at. 60 00:04:12,500 --> 00:04:20,450 And if we just do it this way, you'll notice that at the bottom we have today's date, basically roughly 61 00:04:20,450 --> 00:04:21,230 today's date. 62 00:04:22,160 --> 00:04:23,600 And then if we scroll up. 63 00:04:24,820 --> 00:04:29,710 We've got the oldest one, so we've only been around for a little bit about a year according to the 64 00:04:29,710 --> 00:04:30,880 data that I created. 65 00:04:30,880 --> 00:04:34,480 So what we'll do is just limit five. 66 00:04:37,080 --> 00:04:38,200 And there we go. 67 00:04:38,220 --> 00:04:39,040 That's all we need. 68 00:04:39,060 --> 00:04:40,820 So nice and simple for this one. 69 00:04:40,830 --> 00:04:45,730 I want to start off with some review, but don't worry if this felt way too easy, That's. 70 00:04:45,960 --> 00:04:46,580 That's good. 71 00:04:46,590 --> 00:04:51,900 We're going to move on and get into some more exciting slash, potentially nastier territory. 72 00:04:52,200 --> 00:04:52,770 All right.