WEBVTT 00:00.120 --> 00:00.480 All right. 00:00.510 --> 00:03.570 So we have this data in our Instagram clone database. 00:03.600 --> 00:06.030 Now we're going to ask some questions. 00:06.120 --> 00:08.160 And the reason I'm phrasing it that way. 00:08.160 --> 00:14.180 Asking some questions is because that's usually what you're actually doing if you work with sequel as 00:14.320 --> 00:15.060 well. 00:15.090 --> 00:22.660 Some sequel database or any database really even if it's like a mongo D-B or couch D-B post gresse MY 00:22.680 --> 00:23.290 as well. 00:23.310 --> 00:26.620 Any database out there and you have a bunch of data in there. 00:26.960 --> 00:29.690 Yeah some time a lot of the time or just sticking stuff in there. 00:29.850 --> 00:34.680 But when you're actually working with the data and writing queries a lot of what you're doing is asking 00:34.680 --> 00:39.570 questions where you have a motivation you're trying to figure something out whether it's for an ad campaign 00:39.870 --> 00:42.440 or a post-mortem after a campaign. 00:42.640 --> 00:44.940 I know I'm coming back to ads a lot. 00:45.060 --> 00:48.480 It might just be sales like you could be a woodworker. 00:48.510 --> 00:54.120 You make furniture and you want to figure out how much you sold last year because you're compiling your 00:54.390 --> 00:59.060 tax returns or something and you need to figure out the total volume if sold. 00:59.100 --> 01:00.940 Those are the questions you're asking. 01:01.050 --> 01:07.290 So you think in terms of English or whatever language you speak you you think in terms of questions 01:07.590 --> 01:12.480 in a non sexual language like how many acts did I sell last year. 01:12.480 --> 01:17.280 And then you have to translate that to a some a select something from something else and you have to 01:17.280 --> 01:19.710 use some most likely anyway. 01:20.010 --> 01:25.420 So that's how we're going to work here and we'll start out nice and simple in this first video. 01:25.500 --> 01:29.820 Suppose that we want to reward the users who have been around the longest. 01:29.850 --> 01:36.700 So you may have seen something like this on Instagram but other applications you'll sometimes get you 01:36.690 --> 01:39.520 know a coupon or just a thank you email. 01:39.600 --> 01:44.190 You know thanks for being loyal over our five years or however long we've been around. 01:44.250 --> 01:49.890 So all we want to do is reward our users who've been around the longest and to do that we need to find 01:49.890 --> 01:53.770 the five oldest users the users who have been around the longest. 01:53.820 --> 01:57.590 So this one is should be simple not crazy. 01:57.590 --> 01:59.160 We don't need to work with many tables. 01:59.160 --> 02:00.190 Short video. 02:00.360 --> 02:03.120 Try this on your own the video of course. 02:03.270 --> 02:05.750 And in like three seconds I'll be right back. 02:07.290 --> 02:08.420 And I'm back. 02:08.610 --> 02:15.750 So to do this it's just a matter of basically selecting start from users and then we just want to order 02:15.750 --> 02:21.940 them based off of the date that they were created the date they joined and then limit it to five. 02:22.410 --> 02:31.430 So if we hop over what I'll do is make a new file new file where are you and others call it solutions 02:31.700 --> 02:32.910 as well. 02:33.740 --> 02:40.190 And in here the first one is just finding the numbers here so it's easier for you when you are looking 02:40.190 --> 02:50.550 at this finding 5 oldest users and we know it's going to be a select something from users. 02:51.020 --> 02:57.890 And if we start there you can see we've got 100 users which is one thing I wanted to point out 100 users 02:57.890 --> 03:05.600 is not very much but I kept it at 100 because basically the formula that I imprecisely came up with 03:06.200 --> 03:13.760 for how often users are commenting and posting and liking and tagging it grows exponentially depending 03:13.760 --> 03:14.950 on how many users you have. 03:15.080 --> 03:20.200 So having a hundred users we still have you know almost 20000 records for everything else. 03:20.330 --> 03:24.930 And I upped it to 200 or I tried a thousand users at one point. 03:25.160 --> 03:30.270 It really started going crazy and our queries take a lot longer. 03:30.290 --> 03:31.550 So I decided not to do that. 03:31.610 --> 03:36.440 But if you want to try it could be fun too if you feel comfortable writing code to insert a bunch of 03:36.440 --> 03:40.490 users do that in javascript or Python or whatever language you like. 03:40.670 --> 03:47.530 But for now 100 users it's just interesting to note how much data we get even with just a hundred users. 03:47.630 --> 03:53.270 If the average person is posting you know X number of times and then we get certain celebrities who 03:53.270 --> 03:54.550 post all the time. 03:54.650 --> 03:59.660 Not to mention people who are liking all the time and we get bots who like every single photo. 03:59.660 --> 04:08.480 Things quickly grow so select start from users but we want to order by and our field is called created 04:08.480 --> 04:10.570 at right there. 04:10.680 --> 04:12.290 So just order by created. 04:12.660 --> 04:20.400 And if we just do it this way you'll notice that at the bottom we have today's date basically roughly 04:20.400 --> 04:22.070 today state. 04:22.300 --> 04:28.220 And then if we scroll up we've got the oldest one so we've only been around for a little bit about a 04:28.220 --> 04:28.990 year. 04:29.150 --> 04:31.030 According to the data that I created. 04:31.070 --> 04:36.640 So what we'll do is just limit 5. 04:37.150 --> 04:38.130 And there we go. 04:38.200 --> 04:39.220 That's all we need. 04:39.220 --> 04:40.900 So nice and simple for this one. 04:40.900 --> 04:46.580 I want to start off with some review but don't worry if this felt way too easy it's good that's good. 04:46.630 --> 04:52.000 We're going to move on and get into some more exciting slash potentially nastier territory. 04:52.300 --> 04:52.740 All right.