1 00:01.080 --> 00:01.920 Hello everyone! 2 00:02.880 --> 00:09.800 So assume you are given the table below containing information on user purchases. 3 00:10.640 --> 00:17.200 Write a query to obtain the number of users who purchased the same product on two or more consecutive, 4 00:17.760 --> 00:19.400 two or more different days. 5 00:21.280 --> 00:28.400 So here they have given us the table purchases table and in which user ID, product ID quantity and 6 00:29.200 --> 00:31.200 purchase date column is given. 7 00:31.600 --> 00:35.160 So now let me select the data from this purchases table. 8 00:36.200 --> 00:39.640 So user id product id quantity and purchase date. 9 00:40.280 --> 00:46.680 Now our requirement is we want to identify users who purchase the same product. 10 00:47.240 --> 00:50.320 Same product means purchase product ID should be same right. 11 00:51.000 --> 00:56.320 Product ID should be same on two or more different days. 12 00:56.800 --> 01:01.480 So over here if you see this is the purchase date is same right. 13 01:01.480 --> 01:03.200 So this we cannot consider. 14 01:03.760 --> 01:10.980 But now for if you see four, five, three, six, then he has purchased the same product on two or 15 01:10.980 --> 01:11.980 more different days, right? 16 01:11.980 --> 01:13.780 Because purchase date is different. 17 01:14.380 --> 01:20.620 Again, 8 to 7 same product and purchase date is different right? 18 01:21.900 --> 01:26.180 So our results should be write a query to obtain the number of users. 19 01:26.620 --> 01:31.340 So number of users will be one and two. 20 01:31.700 --> 01:35.940 So two different users are there right now how we can. 21 01:40.460 --> 01:42.860 How we can approach toward this query. 22 01:43.180 --> 01:49.260 So first of all we have to identify user ID product ID and. 23 01:52.260 --> 01:53.220 Purchase date. 24 01:53.340 --> 02:00.020 So if you see over here we need same user ID and same product ID. 25 02:00.300 --> 02:02.740 But purchase date should be different. 26 02:03.300 --> 02:12.720 So now in the outside of the city what I have done I have joined this particular city with my original 27 02:12.760 --> 02:18.360 table purchase table with user ID and product ID will be same, right? 28 02:19.200 --> 02:28.720 So if you see user ID and product ID will be same, but but purchase date of this particular result 29 02:28.720 --> 02:32.440 set and purchase date of this particular result set should be different. 30 02:33.040 --> 02:40.960 So that is why city dot purchase date not equal to p dot purchase date and year of purchase date is 31 02:40.960 --> 02:41.800 2022. 32 02:41.840 --> 02:46.680 Only because all the records has year of purchase date will be 2022 only. 33 02:46.680 --> 02:47.080 Right? 34 02:48.640 --> 02:53.080 And having count of distinct purchase date should be greater than one. 35 02:54.640 --> 02:56.880 So that should be our main requirement, right? 36 02:57.400 --> 03:05.760 Because same user have purchased the same product but on multiple different days multiple different 37 03:05.760 --> 03:06.160 days. 38 03:06.760 --> 03:09.960 So purchase date should be definitely distinct. 39 03:09.960 --> 03:16.740 Purchase date for this particular user ID and End product ID should be multiple, not a single one, 40 03:16.740 --> 03:17.180 right? 41 03:19.020 --> 03:20.980 Will be multiple, not a single one. 42 03:21.300 --> 03:24.220 So that is why I have used this particular condition. 43 03:25.060 --> 03:32.220 And over here we have used the count of distinct p dot user id because we want to identify how many 44 03:32.220 --> 03:42.460 total distinct users are there who has purchased the same product on multiple different days. 45 03:42.460 --> 03:46.700 So that is why I want count of distinct p dot user ID right? 46 03:48.580 --> 03:52.420 So which are which users will be qualified. 47 03:52.780 --> 03:54.300 So this user will not qualify. 48 03:54.340 --> 03:54.540 Right. 49 03:54.580 --> 03:57.820 As I told it is a same purchase date. 50 03:57.820 --> 04:00.420 But these two user ID will be qualified right. 51 04:01.260 --> 04:03.860 Because it has multiple purchase date right. 52 04:05.860 --> 04:08.020 So our result set should be two. 53 04:08.060 --> 04:08.500 Right. 54 04:10.060 --> 04:11.500 So let me execute the query. 55 04:11.500 --> 04:14.140 It should be showing the two right. 56 04:14.140 --> 04:15.740 So this will be our answer. 57 04:16.980 --> 04:17.700 Thank you.