1
00:01.760 --> 00:08.040
You are given a table of PayPal payments showing the payer, the recipient, and the amount paid.

2
00:08.960 --> 00:15.280
A two way unique relationship is established when two people send money back and forth.

3
00:16.080 --> 00:23.440
So one person is sending money to another person, and that same person is sending money to first person.

4
00:24.480 --> 00:28.960
Write a query to find the number of two way unique relationships in this data.

5
00:29.520 --> 00:33.840
A payer can send money to the same recipient multiple times.

6
00:34.480 --> 00:36.640
So here I have the payments table.

7
00:37.480 --> 00:42.600
So let me check whether it is created or not.

8
00:42.640 --> 00:42.920
Okay.

9
00:42.960 --> 00:44.520
It is already available.

10
00:45.040 --> 00:47.440
So payer id recipient id and amount.

11
00:47.840 --> 00:50.360
So now if we see the data over here.

12
00:52.000 --> 00:53.440
So one pair ID.

13
00:55.560 --> 00:57.840
Has another recipient ID right.

14
00:58.040 --> 01:01.880
And then that same recipient ID can become the payer ID.

15
01:01.880 --> 01:05.240
And the payer ID will become the recipient ID.

16
01:05.520 --> 01:07.560
But what is a unique relationship?

17
01:07.560 --> 01:10.600
So this is a only one unique relationship, right?

18
01:10.600 --> 01:12.440
This is only one unique relationship.

19
01:12.440 --> 01:20.160
We can say so now because back and forth transactions between two people should be counted as a single

20
01:20.160 --> 01:21.400
unique relationship.

21
01:22.080 --> 01:28.040
We can simply wrap this query in a subquery and divide the number of rows by a factor of two.

22
01:30.640 --> 01:35.760
Right now, the number of rows can be obtained with a count function as shown below.

23
01:36.280 --> 01:38.680
So now if you see over here.

24
01:41.920 --> 01:46.040
Select payer id id from payments table intersect.

25
01:46.520 --> 01:52.360
We need to do common common between the two query right.

26
01:52.360 --> 01:55.160
So that is why we are using the intersect over here.

27
01:55.400 --> 01:59.800
So if we do this one only it will give us eight rows right.

28
02:01.480 --> 02:06.200
Because first time the payer ID in the recipient ID will be different.

29
02:06.200 --> 02:13.120
But now in the second time, that recipient ID will become the payer ID and payer ID will become the

30
02:13.120 --> 02:17.480
recipient ID, so it should be count as a one row only, right?

31
02:17.520 --> 02:18.880
Not two different rows.

32
02:19.400 --> 02:21.800
So that is why row is reducing over here.

33
02:22.280 --> 02:28.400
And now simply we need to count of either payer ID or recipient ID and then divide it by two.

34
02:30.960 --> 02:36.760
So if we do so we will get eight divided by two means four right.

35
02:37.640 --> 02:40.520
So four is our result set.

36
02:42.840 --> 02:44.320
Four is our result set.

37
02:44.320 --> 02:52.760
Over here we can simply wrap this query in a subquery and divide the number of rows by a factor of two.

38
02:53.160 --> 02:56.400
So that is the only thing we have done over here right.

39
03:02.840 --> 03:07.120
To find the number of two way unique relationships in this data.

40
03:13.080 --> 03:15.040
So this should be our output.

41
03:16.040 --> 03:16.800
Thank you.