1 00:00:00,120 --> 00:00:00,660 All right. 2 00:00:00,660 --> 00:00:07,200 So the next problem here, the next question is that we're trying to schedule an ad campaign, our own 3 00:00:07,200 --> 00:00:11,550 internal campaign that we're going to well, not internal, but our own campaign, where we're trying 4 00:00:11,550 --> 00:00:14,730 to get people to sign up new users on our application. 5 00:00:14,730 --> 00:00:17,850 Let's say we're advertising on Facebook. 6 00:00:18,570 --> 00:00:25,320 We're also considering maybe for a single day, we're going to go and put an ad out like on a billboard. 7 00:00:25,320 --> 00:00:29,130 We can't afford a billboard for an entire week, so we're just going to do one day. 8 00:00:29,640 --> 00:00:35,010 Yeah, it's kind of hypothetical, but still, let's say that's what we're working with and you could 9 00:00:35,010 --> 00:00:38,460 expand that obviously to whatever the budget is and whatever your constraints are. 10 00:00:38,460 --> 00:00:43,860 But for us, low budget and we're just going to run an ad for a day, we want to figure out what day 11 00:00:43,860 --> 00:00:46,560 we should do that on what day of the week is going to work the best. 12 00:00:46,560 --> 00:00:51,660 This is something that is definitely relevant, even though it's simplistic here, we're figuring out 13 00:00:51,870 --> 00:00:55,350 days of the week or time of the day just as a photographer. 14 00:00:55,350 --> 00:01:00,390 That's something that is useful that people are always trying to figure out is what's the best time 15 00:01:00,390 --> 00:01:01,590 to post something on Instagram? 16 00:01:01,590 --> 00:01:02,490 What's the worst time? 17 00:01:02,490 --> 00:01:04,019 What day of the week is the best? 18 00:01:04,019 --> 00:01:04,530 Do you wait? 19 00:01:04,680 --> 00:01:07,680 You know, for what time zone do you try and post according to? 20 00:01:07,830 --> 00:01:11,310 Do you post something so that people in Europe will be able to see it when they're waking up? 21 00:01:11,310 --> 00:01:16,650 There's this whole science and that's just for photographers and Instagram who are not even really making 22 00:01:16,650 --> 00:01:17,550 any money from it. 23 00:01:17,550 --> 00:01:21,660 So if you start thinking about advertisers, then that's where it really matters. 24 00:01:21,660 --> 00:01:26,730 So we're just doing this simple thing to figure out when users register, but there's all sorts of insights 25 00:01:26,730 --> 00:01:31,890 to be garnered about when people are posting photos and how many likes photos get, depending on what 26 00:01:31,920 --> 00:01:34,230 time of day they're posted and all that kind of stuff. 27 00:01:34,500 --> 00:01:35,490 But we're working with this. 28 00:01:35,490 --> 00:01:37,680 So what day of the week do most users register on? 29 00:01:37,860 --> 00:01:40,230 So we only need to work with users still. 30 00:01:40,230 --> 00:01:42,030 So I'm going to just add a comment. 31 00:01:42,660 --> 00:01:50,490 Most popular registration date so we can recycle part of this query. 32 00:01:50,520 --> 00:01:57,000 Select start from users and we don't really need to order buy anything at this point so I can get rid 33 00:01:57,000 --> 00:01:57,570 of that. 34 00:01:57,570 --> 00:02:03,900 So what we want to do is use a group by to collapse these into similar categories, but we can't do 35 00:02:03,900 --> 00:02:11,250 a group by created that because unless somebody registered at exactly the same time down to the second 36 00:02:11,250 --> 00:02:17,820 on the same day, group buy won't work and we'll just have 100 unique users. 37 00:02:17,820 --> 00:02:23,160 So what we can do is basically extract the day of the week, which we've seen how to do that. 38 00:02:23,160 --> 00:02:24,360 So let's start with that. 39 00:02:24,360 --> 00:02:32,280 Let's just do select username comma, and I'll do this on separate lines, username, comma, and then 40 00:02:32,280 --> 00:02:33,660 we want de name. 41 00:02:34,110 --> 00:02:35,250 This is one way of doing it. 42 00:02:35,250 --> 00:02:38,610 You could also use format date but day name. 43 00:02:38,610 --> 00:02:43,410 I happen to remember that one, although there are all those other methods I don't remember that are 44 00:02:43,410 --> 00:02:43,860 functions. 45 00:02:43,860 --> 00:02:48,090 I don't remember that I've talked about like the name of a month I always forget. 46 00:02:48,090 --> 00:02:52,260 So sometimes I do end up just pulling open the docs and trying to find the right function. 47 00:02:52,260 --> 00:02:55,710 But day name and then we just passed in created at. 48 00:02:57,160 --> 00:03:02,840 Okay, let's try that o semicolon before I forget that. 49 00:03:02,860 --> 00:03:04,700 So now we're seeing the day of the week. 50 00:03:04,720 --> 00:03:11,350 So now all we want to do is group them by those days of the week and then count how many people or how 51 00:03:11,350 --> 00:03:14,100 many instances we have for that day of the week. 52 00:03:14,110 --> 00:03:18,370 And then from there, we'll just order them to figure out what day is most popular. 53 00:03:19,630 --> 00:03:23,770 So rather than just doing this, we'll add our group by at the end. 54 00:03:25,150 --> 00:03:28,450 And we don't want to just do group by like group by created at. 55 00:03:28,450 --> 00:03:32,080 Like I said, we want to do a group by day name created at. 56 00:03:32,080 --> 00:03:34,840 And what we could do is just assign an alias here. 57 00:03:34,870 --> 00:03:39,250 So we'll just call this day and then we can just say group by day. 58 00:03:40,450 --> 00:03:41,710 Now, if I do this. 59 00:03:43,130 --> 00:03:44,670 I only get seven results. 60 00:03:44,690 --> 00:03:48,000 Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday. 61 00:03:48,020 --> 00:03:50,540 And of course, that's not all the data that's there. 62 00:03:50,540 --> 00:03:55,850 That's all that's printed out because these groups have been created and we can work with them behind 63 00:03:55,850 --> 00:03:57,530 the scenes or underneath the surface. 64 00:03:57,530 --> 00:04:00,140 So what we'll do, we can get rid of the username at this point. 65 00:04:00,140 --> 00:04:01,460 We don't care about that. 66 00:04:01,460 --> 00:04:09,950 We're going to just group them by day and then we're going to add our count star just like that. 67 00:04:10,430 --> 00:04:11,600 And let's see what we get. 68 00:04:14,380 --> 00:04:17,649 So you can see it's a pretty even distribution. 69 00:04:17,769 --> 00:04:19,120 Let's go ahead and sort. 70 00:04:19,120 --> 00:04:21,519 So we'll do an order by day. 71 00:04:25,290 --> 00:04:28,780 Oops, we don't mean day that's ordering alphabetically over here. 72 00:04:28,800 --> 00:04:31,190 What we want to do is order by Count Star. 73 00:04:31,200 --> 00:04:34,050 So I'll give that an alias as well. 74 00:04:34,050 --> 00:04:35,220 I'll call it total. 75 00:04:36,000 --> 00:04:37,650 We want to order by total. 76 00:04:40,410 --> 00:04:41,160 All right. 77 00:04:41,160 --> 00:04:44,580 And of course, if we wanted to, can change the order. 78 00:04:47,060 --> 00:04:51,140 Technically, the question we're asking is what's the most popular day? 79 00:04:51,140 --> 00:04:56,300 And well, we actually have two days that are tied for that Thursday and Sunday. 80 00:04:56,660 --> 00:05:00,110 Now, this data is not relevant to the real world. 81 00:05:00,110 --> 00:05:00,940 I have no idea. 82 00:05:00,950 --> 00:05:01,220 Actually. 83 00:05:01,220 --> 00:05:03,680 I'm pretty sure those days are not the most popular. 84 00:05:03,800 --> 00:05:09,920 Last time I looked into it for my own purposes, I believe Oh, boy, I believe it was the middle of 85 00:05:09,920 --> 00:05:18,450 the week, like Tuesday and Wednesday, roughly late afternoon, early evening Pacific time, like 5 86 00:05:18,450 --> 00:05:18,770 p.m.. 87 00:05:18,770 --> 00:05:20,570 Pacific time is the best time. 88 00:05:20,570 --> 00:05:24,920 I'm not sure why, but again, I didn't create this data to be realistic. 89 00:05:24,920 --> 00:05:31,100 I just tried to come up with something that would be interesting, whether or not it actually applies 90 00:05:31,100 --> 00:05:32,540 to the real world's rules. 91 00:05:32,830 --> 00:05:39,020 Okay, so what we could do is limit it at the end if we just limited it to one. 92 00:05:40,540 --> 00:05:42,790 It gives us Thursday because it comes first. 93 00:05:44,380 --> 00:05:46,000 We could do a limit too, as well. 94 00:05:48,150 --> 00:05:48,750 All right. 95 00:05:48,750 --> 00:05:55,740 And this is a good time to mention that for most of these things in this section, there isn't only 96 00:05:55,740 --> 00:05:56,640 one way of doing it. 97 00:05:56,640 --> 00:06:01,320 Sometimes there really is, but often there are a couple of different ways we're approaching things. 98 00:06:01,320 --> 00:06:05,910 And if you feel like you came up with something, whether it's just different and you're not sure if 99 00:06:05,910 --> 00:06:10,320 it's better or you feel like you did come up with something that's more efficient or cleaner or that 100 00:06:10,320 --> 00:06:11,910 you prefer, absolutely. 101 00:06:11,910 --> 00:06:17,940 Don't hesitate to post that in the discussion for the course and we'll do our best to get back to you 102 00:06:17,940 --> 00:06:18,960 with feedback on it. 103 00:06:18,960 --> 00:06:23,220 But at the very least, other students will be able to see it and maybe somebody else came up with the 104 00:06:23,220 --> 00:06:25,800 same thing and they won't feel so alone in the world. 105 00:06:26,100 --> 00:06:30,600 Or maybe you did come up with something better that is actually going to benefit other people to see. 106 00:06:31,020 --> 00:06:32,760 But for now, this is how we're doing. 107 00:06:32,760 --> 00:06:34,380 Most popular registration date. 108 00:06:34,380 --> 00:06:35,940 And that answers our question. 109 00:06:35,940 --> 00:06:41,430 Apparently we should be doing our registration campaign to get users to sign up on a Thursday or a Sunday.