1
00:01.400 --> 00:02.280
Hello everyone.

2
00:02.280 --> 00:04.280
So let's solve another SQL problem.

3
00:04.640 --> 00:11.600
Google marketing managers are analyzing the performance of various advertising accounts over the last

4
00:11.600 --> 00:12.120
month.

5
00:12.760 --> 00:15.360
They need your help to gather the relevant data.

6
00:16.120 --> 00:24.520
Write a query to calculate the return on ad spend for each advertiser across all ad campaigns.

7
00:25.160 --> 00:30.520
Round your answer to two decimal places and order your output by advertiser ID.

8
00:31.760 --> 00:36.360
So here they have given us the campaign ad campaigns table.

9
00:36.400 --> 00:36.840
Right?

10
00:37.840 --> 00:41.200
So I think I have already created this table.

11
00:41.400 --> 00:51.480
So in that campaign ID revenue advertiser ID and spend is given and I have already inserted the data

12
00:51.480 --> 00:52.360
into the same.

13
00:52.360 --> 00:54.120
So let me see the table.

14
01:00.280 --> 01:02.400
So this is my ad campaigns table.

15
01:03.080 --> 01:04.720
Now our requirement is.

16
01:08.360 --> 01:14.560
Write a query to calculate the return on ad spend for each advertiser across all ad campaigns.

17
01:15.120 --> 01:22.040
So over here so there were advertiser ID is uh repeating multiple times, right?

18
01:22.200 --> 01:25.720
So we have to calculate for each advertiser ID right.

19
01:27.160 --> 01:31.880
For each advertiser ID we need to do some of revenue and then some of spend.

20
01:32.680 --> 01:38.280
So this is my sum of revenue and sum of spend for each advertiser ID.

21
01:38.840 --> 01:44.880
Now we have to divide this sum of revenue and sum of spend.

22
01:45.560 --> 01:48.800
And we need to round and cast up to two decimal places.

23
01:48.800 --> 01:49.240
Right.

24
01:50.080 --> 01:58.000
So for that purpose, we have defined the city over here, because in the same query we cannot divide

25
01:58.000 --> 01:58.680
properly.

26
01:58.680 --> 02:04.000
So that to simplify the query I have defined the city over here.

27
02:04.320 --> 02:04.760
Right.

28
02:06.240 --> 02:08.040
And revenue divided by spend.

29
02:08.040 --> 02:13.680
We have to define right so that this will be my result.

30
02:18.120 --> 02:23.040
We can give the alias over here like Roas right.

31
02:23.680 --> 02:25.040
That is our alias.

32
02:30.080 --> 02:36.560
So this is the use of city as well as the aggregate functions.

33
02:39.160 --> 02:39.880
Thank you.