1 00:00.320 --> 00:02.240 Let's see another SQL question. 2 00:02.720 --> 00:05.480 Calculate the basic customer lifetime value. 3 00:06.160 --> 00:08.160 So below is the problem statement. 4 00:08.880 --> 00:15.040 Given a customer table and a purchase table, write a query to calculate the lifetime value for each 5 00:15.040 --> 00:15.880 customer. 6 00:16.320 --> 00:22.800 For this basic version, lifetime value is defined as the total amount spent by the customer and also 7 00:22.800 --> 00:25.240 find their first purchase date. 8 00:25.280 --> 00:33.240 Okay, so here we are having the two different tables customer info table and purchase history table. 9 00:33.760 --> 00:34.720 Okay, perfect. 10 00:34.720 --> 00:39.960 So let me share this table structure and data with the ChatGPT. 11 00:40.520 --> 00:43.720 And let's see what happens. 12 00:47.680 --> 00:55.520 So I am having below two table structures and data okay. 13 00:58.120 --> 00:58.440 Okay. 14 00:58.440 --> 01:01.280 Please go ahead and share both the table structures and data. 15 01:01.280 --> 01:04.560 So this is my table structure and data right. 16 01:08.320 --> 01:10.910 Now let me ask the problem statement. 17 01:12.990 --> 01:14.830 So this is my problem statement. 18 01:14.870 --> 01:21.150 Let's see whether it is giving us the correct result or not. 19 01:23.550 --> 01:29.390 So they have sorted the both the tables into the proper tabular format. 20 01:29.390 --> 01:29.670 Right. 21 01:29.670 --> 01:34.150 So it is very easy for us to analyze the thing right now. 22 01:35.430 --> 01:41.470 Now let's uh, check the answer provided by the ChatGPT. 23 01:41.510 --> 01:42.030 Okay. 24 01:42.070 --> 01:42.790 Perfect. 25 01:43.390 --> 01:53.190 So this is the answer and I can mention over here solution provided by the. 26 01:55.430 --> 01:56.150 ChatGPT. 27 01:56.190 --> 01:56.510 Okay. 28 01:56.550 --> 02:02.550 What it is doing it is taking customer ID customer name now lifetime value. 29 02:02.590 --> 02:04.310 How we are checking the lifetime value. 30 02:04.350 --> 02:06.950 We have to do some of amount right. 31 02:07.390 --> 02:10.190 Some of amount and minimum of purchase date. 32 02:10.750 --> 02:15.670 Because first first purchase date we want right what it is saying. 33 02:15.910 --> 02:19.260 Find their first purchase date Perfect. 34 02:19.260 --> 02:21.900 And it has done the join between the two tables. 35 02:22.900 --> 02:27.820 Customer info table and purchase history table using the customer ID column. 36 02:28.300 --> 02:35.020 And it has also done the group by right because we have used the sum and minimum aggregate functions. 37 02:35.060 --> 02:35.500 Okay. 38 02:35.540 --> 02:36.180 Perfect. 39 02:36.620 --> 02:37.940 So it is giving the. 40 02:40.060 --> 02:40.700 Correct thing. 41 02:40.700 --> 02:40.980 Right. 42 02:40.980 --> 02:44.100 I think both the queries should give the same result. 43 02:45.340 --> 02:45.940 Okay. 44 02:46.060 --> 02:46.740 Let's see. 45 02:46.780 --> 02:51.820 There is one additional column is there in the query provided by the ChatGPT. 46 02:51.860 --> 02:52.300 Okay. 47 02:52.340 --> 03:01.620 But the thing is over here is the query provided by ChatGPT has something wrong over here if you see. 48 03:03.060 --> 03:04.740 So let me check that one. 49 03:04.740 --> 03:06.060 What is wrong over here? 50 03:07.660 --> 03:10.700 Why it is giving the null. 51 03:14.700 --> 03:16.780 So can you see why it is giving the null? 52 03:16.820 --> 03:19.900 Because this customer ID three. 53 03:22.460 --> 03:25.020 Is not available in the purchase history table. 54 03:25.020 --> 03:25.460 Right. 55 03:26.360 --> 03:29.560 And we have used the left join over here in the above query. 56 03:29.600 --> 03:31.200 We have used the inner join. 57 03:31.680 --> 03:34.120 So that is why the third record is not coming. 58 03:34.280 --> 03:36.800 But in the below query it has used the left join. 59 03:37.160 --> 03:39.840 That is why the third record is also coming, right? 60 03:42.120 --> 03:44.200 Which is having no purchase history. 61 03:44.200 --> 03:44.680 Right? 62 03:45.080 --> 03:47.440 Now let's see what else ChatGPT is. 63 03:50.400 --> 03:51.320 Providing. 64 03:51.480 --> 03:53.240 So notes for your Udemy course. 65 03:53.600 --> 03:55.160 So that is fine. 66 04:00.800 --> 04:01.480 So see. 67 04:03.720 --> 04:09.600 To calculate customer lifetime value we join the customer table with the purchase history table and 68 04:09.600 --> 04:15.520 sum all their spending and capture the first purchase date using the minimum function. 69 04:15.520 --> 04:16.000 Right. 70 04:16.480 --> 04:18.560 That only what I have told you right? 71 04:19.400 --> 04:23.920 This approach is widely used in marketing analytics and data reporting. 72 04:28.600 --> 04:33.760 Okay, so we will see the next question into the next lecture. 73 04:33.760 --> 04:34.440 Thank you.