WEBVTT 00:00.280 --> 00:00.850 Already. 00:00.930 --> 00:05.040 So we inserted 500 users it was kind of a behemoth of a section to get there. 00:05.100 --> 00:06.270 Lots of ground to cover. 00:06.420 --> 00:11.430 But now that we're there are back in our comfort zone hopefully we're going to have a couple of exercises. 00:11.640 --> 00:14.370 I know we have a lot of them but this is cool. 00:14.370 --> 00:20.490 We have a lot of data now where it was you know a single you hit the return key once and you got 500 00:20.490 --> 00:21.360 users. 00:21.360 --> 00:24.520 Much easier than having to copy and paste like 20 books. 00:24.720 --> 00:26.610 So we're going to play around with them. 00:26.700 --> 00:29.020 And again your data is randomized just like mine. 00:29.070 --> 00:33.670 So your tables and your results will look different but the queries will work the same. 00:34.140 --> 00:35.350 So here's the first thing. 00:35.520 --> 00:40.830 Find the earliest date that a user joined and all we need to see is the date. 00:41.280 --> 00:43.690 And also notice how I formatted it. 00:43.950 --> 00:44.940 Pay attention to that. 00:45.820 --> 00:53.840 It's just the earliest date and then the next problem find the email of the earliest user. 00:53.840 --> 00:55.090 Now this one's tricky. 00:55.100 --> 01:00.950 Remember how I talked about this with min and max way back a couple I don't know five six hours ago 01:01.370 --> 01:06.650 about how when you use min and max you do just get the min you don't get the row itself. 01:06.650 --> 01:08.180 So we had to use a set query. 01:08.180 --> 01:14.840 So the hint is use this query to find the email that corresponds to the earliest user. 01:14.990 --> 01:21.620 Then take all the users and I want to see a table where we have a month name and then the number of 01:21.620 --> 01:23.970 users joined who joined in that month. 01:24.020 --> 01:29.110 So it doesn't matter what year we have some 20 16 some 20 17 most likely. 01:29.450 --> 01:32.220 So we're not grouping by that just by the month. 01:32.600 --> 01:36.710 So you have to figure out how you extract the month name from the date and then go from there. 01:37.220 --> 01:45.160 Also notice the order count the number of e-mails or the number of users with Yahoo emails and the way 01:45.160 --> 01:50.260 that you can determine that it's just if their email ends with AT yahoo DOT com. 01:50.420 --> 01:51.270 So how do you do that. 01:52.310 --> 01:54.170 Count them all. 01:54.380 --> 01:59.900 And then finally calculate the total number of users for each email host and this one's a little bit 01:59.900 --> 02:06.800 complicated because you're basically going to be doing four different checks or to say all right does 02:07.640 --> 02:09.860 that email end with Gmail. 02:10.070 --> 02:10.850 Does it end with Yahoo. 02:10.850 --> 02:16.830 Does it end with hotmail and then you need to calculate how many each end with these. 02:16.880 --> 02:18.740 So it's a little bit complicated. 02:18.800 --> 02:20.340 That's why I left it for last. 02:20.360 --> 02:21.420 Hopefully you're able to get it. 02:21.440 --> 02:24.120 If not solutions coming up next video. 02:24.200 --> 02:26.300 Just remember your data is going to look different. 02:26.300 --> 02:28.510 Don't panic if your numbers are different. 02:28.520 --> 02:28.830 All right. 02:28.880 --> 02:29.850 I'll see you with a solution.