1 00:00:00,090 --> 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,540 --> 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:34,440 Yeah, it's kind of hypothetical, but still, let's say that's what we're working with. 9 00:00:34,440 --> 00:00:38,460 And you could 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:50,550 This is something that is definitely relevant, even though it's simplistic here. 13 00:00:50,550 --> 00:00:53,760 We're figuring out days of the week or time of the day. 14 00:00:54,120 --> 00:00:55,350 Just as a photographer. 15 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 16 00:01:00,390 --> 00:01:01,620 to post something on Instagram? 17 00:01:01,620 --> 00:01:02,490 What's the worst time? 18 00:01:02,490 --> 00:01:03,990 What day of the week is the best? 19 00:01:04,019 --> 00:01:07,680 Do you wait, 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:25,020 So we're just doing this simple thing to figure out when users register. 25 00:01:25,020 --> 00:01:30,720 But there's all sorts of insights to be garnered about when people are posting photos and how many likes 26 00:01:30,720 --> 00:01:34,230 photos get, depending on what time of day they're posted and all that kind of stuff. 27 00:01:34,500 --> 00:01:37,680 But we're working with this, so what day of the week do most users register on? 28 00:01:37,830 --> 00:01:40,230 So we only need to work with users still. 29 00:01:40,230 --> 00:01:42,030 So I'm going to just add a comment. 30 00:01:42,660 --> 00:01:54,030 Most popular registration date so we can recycle part of this query select star from users and we don't 31 00:01:54,030 --> 00:01:57,570 really need to order buy anything at this point so I can get rid of that. 32 00:01:57,570 --> 00:02:02,820 So what we want to do is use a group by to collapse these into similar categories. 33 00:02:02,820 --> 00:02:10,350 But we can't do a group by creating that because unless somebody registered at exactly the same time 34 00:02:10,350 --> 00:02:17,820 down to the second on the same day, Group Buy won't work and we'll just have 100 unique users. 35 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. 36 00:02:23,160 --> 00:02:24,360 So let's start with that. 37 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 38 00:02:32,280 --> 00:02:33,660 we want de name. 39 00:02:34,080 --> 00:02:35,250 This is one way of doing it. 40 00:02:35,250 --> 00:02:38,610 You could also use format date, but day name. 41 00:02:38,610 --> 00:02:40,200 I happen to remember that one. 42 00:02:40,200 --> 00:02:43,860 Although there are all those other methods, I don't remember that are functions. 43 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. 44 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. 45 00:02:52,260 --> 00:02:53,310 But day name. 46 00:02:53,310 --> 00:02:54,660 And then we just passed in. 47 00:02:54,660 --> 00:02:55,710 Created at. 48 00:02:57,160 --> 00:02:57,760 Okay. 49 00:02:58,720 --> 00:03:02,850 Let's try that o semicolon before I forget that. 50 00:03:02,860 --> 00:03:04,700 So now we're seeing the day of the week. 51 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 52 00:03:11,350 --> 00:03:14,110 many instances we have for that day of the week. 53 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. 54 00:03:18,970 --> 00:03:19,330 Okay. 55 00:03:19,630 --> 00:03:23,770 So rather than just doing this, we'll add our group by at the end. 56 00:03:25,150 --> 00:03:30,220 And we don't want to just do group by like group I created at like I said, we want to do a group by 57 00:03:30,220 --> 00:03:32,080 day name created at. 58 00:03:32,080 --> 00:03:34,840 And what we could do is just assign an alias here. 59 00:03:34,840 --> 00:03:39,250 So we'll just call this day and then we can just say group by day. 60 00:03:40,450 --> 00:03:41,710 Now if I do this. 61 00:03:43,130 --> 00:03:48,000 I only get seven results Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday. 62 00:03:48,020 --> 00:03:50,540 And of course, that's not all the data that's there. 63 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 64 00:03:55,850 --> 00:03:57,530 the scenes or underneath the surface. 65 00:03:57,530 --> 00:04:00,140 So what we'll do, we can get rid of username at this point. 66 00:04:00,140 --> 00:04:01,460 We don't care about that. 67 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. 68 00:04:10,430 --> 00:04:11,600 And let's see what we get. 69 00:04:13,720 --> 00:04:14,190 Och. 70 00:04:14,380 --> 00:04:17,649 So you can see it's a pretty even distribution. 71 00:04:17,769 --> 00:04:21,519 Let's go ahead and sort so we'll do an order by day. 72 00:04:25,290 --> 00:04:25,660 Oops. 73 00:04:25,710 --> 00:04:28,780 We don't mean day that's ordering alphabetically over here. 74 00:04:28,800 --> 00:04:31,190 What we want to do is order by Count Star. 75 00:04:31,200 --> 00:04:34,050 So I'll give that an alias as well. 76 00:04:34,050 --> 00:04:35,220 I'll call it total. 77 00:04:36,000 --> 00:04:37,650 We want to order by total. 78 00:04:40,410 --> 00:04:41,160 All right. 79 00:04:41,160 --> 00:04:44,580 And of course, if we wanted to, can change the order. 80 00:04:47,030 --> 00:04:51,140 Technically the question we're asking is what's the most popular day? 81 00:04:51,140 --> 00:04:56,300 And well, we actually have two days that are tied for that Thursday and Sunday. 82 00:04:56,660 --> 00:05:00,110 Now, this data is not relevant to the real world. 83 00:05:00,110 --> 00:05:00,940 I have no idea. 84 00:05:00,950 --> 00:05:03,680 Actually, I'm pretty sure those days are not the most popular. 85 00:05:03,770 --> 00:05:08,360 Last time I looked into it for my own purposes, I believe oh boy. 86 00:05:08,360 --> 00:05:15,770 I believe it was the middle of the week, like Tuesday and Wednesday, roughly late afternoon, early 87 00:05:15,770 --> 00:05:17,900 evening, Pacific time. 88 00:05:17,900 --> 00:05:20,570 Like 5 p.m. Pacific time is the best time. 89 00:05:20,570 --> 00:05:24,920 I'm not sure why, but again, I didn't create this data to be realistic. 90 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 91 00:05:31,100 --> 00:05:32,570 to the real world's rules. 92 00:05:32,720 --> 00:05:39,020 Okay, so what we could do is limit it at the end if we just limited it to one. 93 00:05:40,540 --> 00:05:42,790 It gives us Thursday because it comes first. 94 00:05:44,410 --> 00:05:46,000 We could do a limit too as well. 95 00:05:48,120 --> 00:05:48,750 All right. 96 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 97 00:05:55,740 --> 00:05:56,640 one way of doing it. 98 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. 99 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 100 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 101 00:06:10,320 --> 00:06:11,070 you prefer. 102 00:06:11,100 --> 00:06:11,910 Absolutely. 103 00:06:11,910 --> 00:06:17,640 Don't hesitate to post that in the discussion for the course, and we'll do our best to get back to 104 00:06:17,640 --> 00:06:18,960 you with feedback on it. 105 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 106 00:06:23,220 --> 00:06:25,800 same thing and they won't feel so alone in the world. 107 00:06:26,100 --> 00:06:30,630 Or maybe you did come up with something better that is actually going to benefit other people to see. 108 00:06:31,020 --> 00:06:35,940 But for now, this is how we're doing most popular registration date and that answers our question. 109 00:06:35,940 --> 00:06:41,490 Apparently we should be doing our registration campaign to get users to sign up on a Thursday or a Sunday.