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:27,270
Yeah.

9
00:00:27,270 --> 00:00:29,730
Sometimes a lot of the time you're just sticking stuff in there.

10
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

11
00:00:34,710 --> 00:00:38,670
questions where you have a motivation, you're trying to figure something out, whether it's for an

12
00:00:38,670 --> 00:00:42,450
ad campaign or a post mortem after a campaign.

13
00:00:42,660 --> 00:00:44,550
I know I'm coming back to ads a lot.

14
00:00:44,970 --> 00:00:48,660
It might just be sales like you could be a woodworker.

15
00:00:48,660 --> 00:00:54,270
You make furniture and you want to figure out how much you sold last year because you're compiling your

16
00:00:54,270 --> 00:00:58,200
tax returns or something and you need to figure out the total volume of sold.

17
00:00:58,980 --> 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,830
to use some most likely anyways.

22
00:01:19,830 --> 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 an 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:05,790
And in 3 seconds, I'll be right back.

35
00:02:07,250 --> 00:02:08,300
And I'm back.

36
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

37
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

38
00:02:20,750 --> 00:02:21,620
it to five.

39
00:02:22,250 --> 00:02:26,020
So if we hop over, what I'll do is make a new file.

40
00:02:27,620 --> 00:02:28,490
New file?

41
00:02:28,490 --> 00:02:29,240
Where are you?

42
00:02:29,240 --> 00:02:32,540
And I'll just call it Solutions SQL.

43
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

44
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.

45
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.

46
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

47
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

48
00:03:13,400 --> 00:03:14,960
depending on how many users you have.

49
00:03:14,960 --> 00:03:20,150
So having 100 users, we still have almost 20,000 records for everything else.

50
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

51
00:03:28,490 --> 00:03:30,170
queries take a lot longer.

52
00:03:30,170 --> 00:03:31,550
So I decided not to do that.

53
00:03:31,550 --> 00:03:33,530
But if you want to try, it could be fun to.

54
00:03:33,560 --> 00:03:39,200
If you feel comfortable writing code to insert a bunch of users, do that in JavaScript or Python or

55
00:03:39,200 --> 00:03:40,400
whatever language you like.

56
00:03:40,520 --> 00:03:45,350
But for now, 100 users, it's just interesting to note how much data we get.

57
00:03:45,350 --> 00:03:52,640
Even with just 100 users, if the average person is posting X number of times and then we get certain

58
00:03:52,640 --> 00:03:57,170
celebrities who post all the time, not to mention people who are liking all the time.

59
00:03:57,170 --> 00:04:00,920
And we get bots who like every single photo things quickly grow.

60
00:04:01,610 --> 00:04:08,990
So select start from users but we want to order by and our field is called created at.

61
00:04:09,830 --> 00:04:10,610
Right there.

62
00:04:10,610 --> 00:04:12,290
So just order by created that.

63
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

64
00:04:20,450 --> 00:04:21,230
today's date.

65
00:04:22,160 --> 00:04:23,600
And then if we scroll up.

66
00:04:24,790 --> 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

67
00:04:29,710 --> 00:04:30,880
data that I created.

68
00:04:30,880 --> 00:04:34,480
So what we'll do is just limit five.

69
00:04:37,080 --> 00:04:38,200
And there we go.

70
00:04:38,220 --> 00:04:39,070
That's all we need.

71
00:04:39,090 --> 00:04:40,820
So nice and simple for this one.

72
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.

73
00:04:45,960 --> 00:04:46,580
That's good.

74
00:04:46,590 --> 00:04:51,900
We're going to move on and get into some more exciting slash, potentially nastier territory.

75
00:04:52,200 --> 00:04:52,770
All right.