WEBVTT 00:00.090 --> 00:00.750 All right. 00:00.750 --> 00:07.500 So the next problem here the next question is that we're trying to schedule an ad campaign our own internal 00:07.500 --> 00:08.540 campaign that we're going to. 00:08.610 --> 00:13.860 Well not internal but our own campaign where we're trying to get people to sign up new users on our 00:13.860 --> 00:14.650 application. 00:14.760 --> 00:18.480 Let's say we're advertising on Facebook. 00:18.690 --> 00:25.290 We're also considering maybe for a single day we're going to go and put an ad out like on a billboard. 00:25.290 --> 00:27.620 We can't afford a billboard for an entire week. 00:27.630 --> 00:29.510 So we're just going to do one day. 00:29.760 --> 00:30.030 Yeah. 00:30.060 --> 00:31.570 It's kind of hypothetical. 00:31.590 --> 00:34.470 But still let's say that's what we're working with. 00:34.530 --> 00:38.450 And you could expand that obviously to wherever the budget is and whatever your constraints are. 00:38.460 --> 00:42.210 But for us low budget and we're just going to run an ad for a day. 00:42.510 --> 00:46.560 We want to figure out what day we should do that on what day the week is going to work the best. 00:46.590 --> 00:51.840 This is something that is definitely relevant even though it's simplistic here figuring out you know 00:51.840 --> 00:57.690 days of the week or time of the day just as a photographer that's something that is useful that people 00:57.690 --> 01:01.590 are always trying to figure out is what's the best time to post something on Instagram. 01:01.590 --> 01:04.040 What's the worst time what day of the week is the best. 01:04.050 --> 01:09.330 Do you wait you for what time zone do you try and post according to do you post something so that people 01:09.330 --> 01:13.770 in Europe will be able to see it when they're waking up there's this whole science and that's just for 01:13.770 --> 01:17.690 photographers and Instagram who are not even really making any money from it. 01:17.700 --> 01:21.840 So if you started thinking about advertisers then that's where it really matters. 01:21.840 --> 01:25.010 So we're just doing a simple thing to figure out when users register. 01:25.170 --> 01:30.690 But there's all sorts of insights to be garnered about when people are posting photos and how many likes 01:30.690 --> 01:34.340 photos get depending on what time of day they're posted and all that kind of stuff. 01:34.680 --> 01:37.880 But we're working with it so what day of the week do most users register on. 01:38.040 --> 01:40.160 So we only need to work with the users still. 01:40.380 --> 01:42.710 So I'm going to just add a comment. 01:42.720 --> 01:52.520 Most popular registration date so we can recycle part of this query Nexstar start from users. 01:53.280 --> 01:57.720 And we don't really need to order by anything at this point so I can get rid of that. 01:57.720 --> 02:04.170 So what I want to do is use a Group By to collapse these into similar categories but we can't do a group 02:04.170 --> 02:11.190 by created that because unless somebody you know registered at exactly the same time down to the second 02:11.250 --> 02:17.760 on the same day GROUP BY won't work and it will just have you know 100 unique users. 02:18.000 --> 02:23.340 So what we can do is basically extract the day of the week which we've seen how to do that. 02:23.340 --> 02:29.970 So let's start with that let's just use user name comma and do this on separate lines. 02:30.200 --> 02:34.140 Username comma and then we want de name. 02:34.140 --> 02:35.220 This is one way of doing it. 02:35.220 --> 02:38.610 You could also use Format date but Day name. 02:38.700 --> 02:43.820 I happen to remember that one although there are all those other methods I don't remember her function. 02:43.830 --> 02:46.940 I don't remember that I've talked about like the name of a month. 02:46.950 --> 02:51.540 I always forget so sometimes I do end up just you know pulling open the docs and trying to find the 02:51.540 --> 02:52.270 right function. 02:52.380 --> 02:56.350 But de name and then we just pass passen created it. 02:57.200 --> 03:02.940 OK let's try that semi-colon for I forget that. 03:02.960 --> 03:04.680 So now we're seeing the day of the week. 03:04.860 --> 03:11.310 So now all we want to do is group them by those days that week and then count how many people or how 03:11.310 --> 03:14.210 many instances we have for that day of the week. 03:14.340 --> 03:18.570 And then from there we'll just order them to figure out what is most popular. 03:18.990 --> 03:19.570 OK. 03:19.800 --> 03:26.910 So rather than just doing this well at our group by at the end and we don't want to just do group by 03:26.910 --> 03:32.670 like a group I created it like I said we want to do a group by day name created at and what we could 03:32.670 --> 03:34.800 do is just assign an alias here. 03:34.830 --> 03:40.480 So we'll just call this day and then we can just say group by day. 03:40.530 --> 03:44.870 Now if I do this I only get seven results. 03:44.870 --> 03:50.330 Friday Monday Saturday Sunday Thursday Tuesday Wednesday and of course that's not all the data that's 03:50.330 --> 03:55.010 there that's all that's printed out because these groups have been created and we can work with them 03:55.550 --> 03:57.610 behind the scenes underneath the surface. 03:57.650 --> 04:00.260 So what we'll do we can get rid of a username at this point. 04:00.260 --> 04:01.570 We don't care about that. 04:01.610 --> 04:10.250 We're going to just group them by day and then we're going to add our count star just like that. 04:10.460 --> 04:11.590 And let's see what we get. 04:13.700 --> 04:14.460 OK. 04:14.560 --> 04:17.830 So you can see it's a pretty even distribution. 04:17.830 --> 04:21.250 Let's go ahead and sort of do an order by day 04:25.270 --> 04:28.860 but we don't mean day that's ordering alphabetically over here. 04:28.980 --> 04:31.310 We're going to do is order by count start. 04:31.310 --> 04:35.840 So I'll give that an alias as well I'll call it total. 04:36.080 --> 04:37.490 We want to order by total 04:40.410 --> 04:44.340 All right and of course if we wanted to you can change the order 04:47.130 --> 04:51.150 technically the question we're asking is What's the most popular day. 04:51.270 --> 04:56.810 And well we actually have two days that are tied for that Thursday and Sunday. 04:56.820 --> 05:00.090 Now this data is not relevant to the real world. 05:00.150 --> 05:00.960 I have no idea. 05:00.950 --> 05:03.870 Actually I'm pretty sure those days are not the most popular. 05:03.870 --> 05:07.410 Last time I looked into it for my own purposes I believe. 05:07.680 --> 05:15.720 Oh boy I believe it was the middle of the week like Tuesday and Wednesday roughly late afternoon early 05:15.720 --> 05:20.700 evening Pacific time like 5:00 p.m. Pacific Time missed it best time. 05:20.700 --> 05:21.650 I'm not sure why. 05:22.080 --> 05:24.930 But again I didn't create this data to be realistic. 05:24.930 --> 05:31.080 I just tried to come up with something that would be interesting whether or not it actually applies 05:31.080 --> 05:32.550 to the real world's rules. 05:32.780 --> 05:33.030 OK. 05:33.030 --> 05:36.710 So what we could do is you know limit it at the end. 05:37.290 --> 05:48.160 If we just limited to one it gives us Thursday because it comes first we could do limit two as well. 05:48.160 --> 05:48.610 All right. 05:48.870 --> 05:55.900 And this is a good time to mention that for most of these things in this section there isn't only one 05:55.900 --> 05:56.620 way of doing it. 05:56.620 --> 06:01.380 Sometimes there really is but often there are a couple of different ways of approaching things. 06:01.420 --> 06:06.010 And if you feel like you came up with something whether it's just different and you're not sure if it's 06:06.010 --> 06:10.420 better or you feel like you did come up with something that's more efficient or cleaner or that you 06:10.420 --> 06:11.050 prefer. 06:11.140 --> 06:11.900 Absolutely. 06:11.930 --> 06:17.890 Don't hesitate to post that in the discussion for the course and we'll do our best to get back to you 06:17.950 --> 06:19.060 with feedback on it. 06:19.090 --> 06:23.200 But at the very least other students will be able to see it and maybe somebody else came up with the 06:23.200 --> 06:26.140 same thing and they won't feel so alone in the world. 06:26.140 --> 06:30.790 Or maybe you did come up with something better that is actually going to benefit other people to see. 06:31.180 --> 06:32.870 But for now this is how we're doing. 06:32.920 --> 06:34.420 Most popular registration date. 06:34.450 --> 06:35.950 And that answers our question. 06:35.950 --> 06:41.230 Apparently we should be doing our registration campaign to get users to sign up on a Thursday or Sunday.