1 00:00.960 --> 00:07.880 Assume you are given a table on Walmart user transactions based on their most recent transaction date. 2 00:08.440 --> 00:14.360 Write a query that retrieves the user along with number of products they bought. 3 00:15.280 --> 00:23.920 Output the user's most recent transaction date, user ID, and the number of products sorted in chronological 4 00:23.920 --> 00:25.560 order by transaction date. 5 00:26.040 --> 00:33.120 So here we have table which is user transactions and they have given us product ID user ID spend and 6 00:33.120 --> 00:34.120 transaction date. 7 00:34.800 --> 00:37.200 Now if you see this particular table. 8 00:39.840 --> 00:48.960 So based on their most recent transaction date how many number of products a particular user has purchased. 9 00:49.280 --> 00:59.720 So if you see for user ID 159, he has purchased two products on this particular maximum date, right? 10 00:59.760 --> 01:03.960 Max of transaction date In this user ID bucket. 11 01:04.400 --> 01:06.720 Now for user ID 115. 12 01:07.200 --> 01:08.880 If you see 4115. 13 01:09.720 --> 01:13.160 The maximum date is this one and one product. 14 01:13.160 --> 01:17.400 And for user id 123 the maximum date is this one. 15 01:17.400 --> 01:20.360 And it is also purchase one product. 16 01:20.640 --> 01:30.800 So we can say that for user ID 1592 product and rest of the two user ID one one product purchase on 17 01:30.800 --> 01:32.240 the maximum date, right? 18 01:32.680 --> 01:37.840 So now how can we get this, uh, this output. 19 01:37.840 --> 01:45.680 So first of all, we need to identify the maximum of transaction date for each user. 20 01:45.680 --> 01:46.040 Right. 21 01:46.080 --> 01:50.200 So suppose you dot user id and max of. 22 01:55.800 --> 01:57.520 Max of transaction date. 23 01:57.880 --> 02:00.600 So and let me do the group by. 24 02:07.390 --> 02:09.830 Group by user ID right now. 25 02:10.710 --> 02:12.670 Okay, this is my maximum date. 26 02:13.070 --> 02:15.110 Now I want to identify. 27 02:18.310 --> 02:23.670 How many number of products he has purchased on this particular date. 28 02:24.070 --> 02:33.430 So what I can do, I can encapsulate this, uh, detail in the city, and then I can again inner join. 29 02:34.190 --> 02:39.390 Inner join with the original table user underscore transactions table. 30 02:40.230 --> 02:42.190 And based upon the user ID. 31 02:45.790 --> 02:54.870 And and also I have to take transaction date equal to maximum transaction date. 32 02:55.070 --> 02:58.550 So as max date. 33 02:58.990 --> 03:02.270 So this is my city dot max date right. 34 03:02.750 --> 03:07.270 So on this particular date only I want not for all the date. 35 03:07.270 --> 03:07.710 Right. 36 03:08.390 --> 03:12.750 So on this particular date, how many number of products the user has purchased? 37 03:13.110 --> 03:17.310 Now I can take city dot user id. 38 03:19.830 --> 03:20.630 Comma. 39 03:28.990 --> 03:34.390 U one dot product id and count of one. 40 03:35.030 --> 03:37.830 Or you can say that let me take. 41 03:40.310 --> 03:41.790 A transaction date. 42 03:42.270 --> 03:46.630 So city dot max date right. 43 03:48.990 --> 03:52.550 And group by group by these two particular column. 44 03:53.110 --> 03:55.870 So let me take. 45 03:57.710 --> 03:59.750 And now let me run this query. 46 04:04.430 --> 04:05.020 So right. 47 04:05.020 --> 04:07.340 This is giving me the perfect output. 48 04:10.620 --> 04:16.380 That 159 user has purchased two product on this particular date, right. 49 04:16.380 --> 04:17.980 That we have seen already. 50 04:17.980 --> 04:18.380 So. 51 04:24.180 --> 04:26.980 So let me show you. 52 04:36.380 --> 04:37.020 The query. 53 04:37.220 --> 04:39.420 So this is the query actually. 54 04:40.140 --> 04:42.060 And let me remove this one. 55 04:44.700 --> 04:48.100 And put a star over here and let me run both of these query. 56 04:49.900 --> 04:52.500 No need this one user order by. 57 04:55.060 --> 04:56.180 User id. 58 04:59.460 --> 05:00.780 Product id and. 59 05:05.100 --> 05:06.220 Transaction date. 60 05:06.260 --> 05:08.860 And now, let me put Elias over here. 61 05:10.380 --> 05:12.660 Let me try to run both of these query. 62 05:14.460 --> 05:15.140 So see. 63 05:21.260 --> 05:26.060 Let me do order by user ID and transaction date only. 64 05:26.900 --> 05:31.580 So now if you see 115 in this transaction date. 65 05:34.300 --> 05:37.060 I can take descending transaction date. 66 05:37.060 --> 05:37.260 Right. 67 05:37.260 --> 05:38.940 Because I want highest. 68 05:41.740 --> 05:47.100 So if you see over here for on this particular date 115 has purchased one product. 69 05:47.100 --> 05:51.020 On this particular date 123 is purchased also one product. 70 05:51.020 --> 05:54.220 And on this particular date both of the date are similar. 71 05:54.620 --> 06:00.300 He has purchased two product, so it is giving me max date 12th of July and two products. 72 06:00.300 --> 06:00.700 Right? 73 06:01.500 --> 06:03.740 So this is how we can solve the query. 74 06:05.140 --> 06:05.900 Thank you.