1
00:01.000 --> 00:01.920
Hello everyone.

2
00:02.800 --> 00:03.720
Good morning all.

3
00:04.600 --> 00:06.440
So welcome back to my channel.

4
00:07.040 --> 00:13.120
Today we will solve another fun SQL question asked in product based company.

5
00:13.840 --> 00:17.400
So we have given a table of candidates and their skills.

6
00:18.240 --> 00:26.440
You are tasked with finding the candidates best suit for an open data science job, and you want to

7
00:26.440 --> 00:33.440
find candidates who are proficient in Python, Tableau, and PostgreSQL.

8
00:34.200 --> 00:41.200
So we have to identify the student who is proficient in at least minimum of these three skills.

9
00:41.640 --> 00:42.400
Compulsory.

10
00:42.400 --> 00:42.680
Right.

11
00:42.720 --> 00:44.120
All these three skills.

12
00:44.720 --> 00:50.560
Now write a query to list the candidates who possesses all the required skills for the job.

13
00:51.120 --> 00:54.560
Show the output by candidate ID in ascending order.

14
00:55.160 --> 01:00.600
So now we have given this table candidates table and in which only two columns are there.

15
01:00.700 --> 01:00.900
right?

16
01:00.940 --> 01:03.340
Candidate ID and skills columns are there.

17
01:03.980 --> 01:06.020
I have already created the table.

18
01:07.660 --> 01:11.540
Now let me take this data into the Excel file.

19
01:15.900 --> 01:18.460
Now let's see what should be our answer.

20
01:18.460 --> 01:18.940
Right.

21
01:21.060 --> 01:25.620
So our requirement is Python, Tableau and PostgreSQL minimum.

22
01:26.020 --> 01:27.980
These three skills should be there right.

23
01:28.540 --> 01:35.700
So one answer is this 110 C4 234 as R is there right.

24
01:36.140 --> 01:39.540
But not Python is not there.

25
01:39.540 --> 01:40.820
The tableau is not there.

26
01:40.820 --> 01:42.500
So that should be not our answer.

27
01:42.860 --> 01:45.500
Again, this one is only two skills are there.

28
01:45.540 --> 01:47.020
That should be not our answer.

29
01:47.420 --> 01:53.540
Now this one is for skills but all our required skills are there right.

30
01:53.620 --> 01:55.820
Python, Tableau and PostgreSQL.

31
01:56.340 --> 01:58.460
So it will be our answer again.

32
01:59.420 --> 02:00.060
And now.

33
02:01.410 --> 02:02.730
this one has only one skill.

34
02:02.730 --> 02:04.090
This one has only one skill.

35
02:04.290 --> 02:09.090
So our answer should be 123 and 147.

36
02:09.090 --> 02:09.530
Right.

37
02:10.530 --> 02:13.650
Now how can we find this in SQL.

38
02:14.370 --> 02:16.010
So let me.

39
02:18.770 --> 02:23.250
Show you the one way is we need to identify the.

40
02:25.370 --> 02:27.130
Candidate skill.

41
02:27.290 --> 02:32.490
One minute select star from candidates.

42
02:35.930 --> 02:43.690
Where candidate ID comma count of distinct skill.

43
02:48.730 --> 02:53.690
Should be where skill in.

44
02:54.250 --> 02:59.490
Now this distinct skill should be Python, Tableau and PostgreSQL.

45
02:59.490 --> 02:59.590
SQL.

46
02:59.590 --> 02:59.950
Right.

47
02:59.950 --> 03:01.710
So we need to write over here.

48
03:04.190 --> 03:04.950
Python.

49
03:06.270 --> 03:07.830
Then we need to write.

50
03:10.150 --> 03:10.830
Tableau.

51
03:13.310 --> 03:14.670
And PostgreSQL.

52
03:14.670 --> 03:15.150
Right.

53
03:15.990 --> 03:19.030
So minimum these three skills should be there.

54
03:19.590 --> 03:20.790
Now let me.

55
03:23.430 --> 03:24.870
Check okay.

56
03:24.910 --> 03:26.950
We need to mention the group by clause.

57
03:30.510 --> 03:34.550
Group by candidate ID because we have used the count function right.

58
03:35.430 --> 03:37.270
That is the aggregate function right.

59
03:38.030 --> 03:41.750
So now if you see over here.

60
03:43.870 --> 03:46.270
123147.

61
03:46.830 --> 03:50.230
These two are there which has all these three skills.

62
03:50.550 --> 03:57.670
So now over here we need to mention we can mention either city or.

63
03:59.690 --> 04:01.330
We can say that derived table.

64
04:01.370 --> 04:01.810
Right.

65
04:03.050 --> 04:05.850
So now let me take where.

66
04:08.170 --> 04:12.050
S k equal to three because all these three skills are required right.

67
04:12.970 --> 04:15.490
Now let me check okay.

68
04:15.530 --> 04:17.210
123 and 147 are there.

69
04:17.210 --> 04:17.650
Right.

70
04:18.090 --> 04:22.730
Now our requirement is only we need to find the candidate ID.

71
04:22.770 --> 04:25.810
So I have taken the candidate ID.

72
04:25.810 --> 04:28.650
And there should also be in ascending order.

73
04:29.090 --> 04:33.530
So 123 will come first and 147 will come later.

74
04:33.530 --> 04:34.050
Right.

75
04:34.090 --> 04:35.130
So let me check.

76
04:35.170 --> 04:35.690
Okay.

77
04:35.730 --> 04:36.530
Perfect.

78
04:37.250 --> 04:44.850
So this should this is how we can solve the any query ask in an interview.

79
04:44.850 --> 04:45.210
Right.

80
04:45.250 --> 04:50.170
Firstly we need to prepare our result set or data into the Excel file.

81
04:50.610 --> 04:55.610
And then from that Excel file we will try to generate our answer.

82
04:55.610 --> 04:56.010
Right.

83
04:57.330 --> 05:00.040
So Thank you everyone.

84
05:01.160 --> 05:03.800
Let me continue with the next question.

85
05:06.400 --> 05:13.720
Intuit provides a range of tax filing products, including Turbo Box and QuickBooks.

86
05:15.840 --> 05:17.640
Available in various versions.

87
05:18.120 --> 05:24.960
Write a query to determine the total number of tax filings made using Turbo Box and QuickBooks.

88
05:25.000 --> 05:31.280
Right now, each user can file taxes once a year using only one product.

89
05:31.840 --> 05:32.880
So now we have.

90
05:33.600 --> 05:39.920
This table is provided file taxes and I have already inserted the data into the same.

91
05:40.320 --> 05:48.000
Now some users are doing the tax paying the tax using turbo box and some are paying using QuickBooks.

92
05:48.440 --> 05:55.200
So we need to identify the users who are paying with Turbo Box and users who are paying with QuickBooks.

93
05:56.640 --> 05:58.680
So in this particular case.

94
05:59.780 --> 06:02.260
First of all, we need to find.

95
06:02.300 --> 06:02.460
Right?

96
06:02.500 --> 06:02.980
Right.

97
06:03.580 --> 06:05.220
How many users are there?

98
06:07.300 --> 06:09.700
So let me copy this one.

99
06:16.940 --> 06:20.140
And let me just write a right.

100
06:20.540 --> 06:21.700
Where clause over here.

101
06:23.340 --> 06:25.220
Product like.

102
06:30.140 --> 06:31.060
Turbo box.

103
06:31.060 --> 06:33.580
And let me take the count over here.

104
06:38.940 --> 06:40.340
So let me check.

105
06:41.260 --> 06:41.620
Okay.

106
06:41.660 --> 06:43.140
It is giving zero count.

107
06:43.180 --> 06:45.780
Let me do something over here.

108
06:46.340 --> 06:47.860
Lower of product.

109
06:54.540 --> 06:54.940
Sorry.

110
06:54.980 --> 06:56.500
Turbo tax is there, right?

111
07:00.720 --> 07:04.120
Now let me take Oct 17.

112
07:04.160 --> 07:08.000
Right now again we need to identify with quick box.

113
07:08.000 --> 07:09.120
Right quick box.

114
07:09.440 --> 07:11.280
So let me copy this one.

115
07:12.760 --> 07:26.080
Copy this one and let me take it should give for right now I need to both of these results said I need

116
07:26.080 --> 07:27.640
in two different columns.

117
07:28.080 --> 07:29.800
How can we do this.

118
07:30.360 --> 07:31.720
Because here we are.

119
07:32.120 --> 07:34.920
We have two different result sets into two different tables.

120
07:34.920 --> 07:35.360
Right.

121
07:35.720 --> 07:37.400
But I need in two different columns.

122
07:37.400 --> 07:41.400
So for that I can write multiple CTE over here.

123
07:42.160 --> 07:46.360
So this will be our city and this will be our city two s.

124
07:47.560 --> 07:56.280
And then we can do simply this and then select star from city to city two.

125
07:56.600 --> 08:01.190
Right Now let me try to execute this one.

126
08:08.990 --> 08:09.550
Okay.

127
08:09.710 --> 08:14.190
So over here we need to give count, uh, as a alias.

128
08:14.190 --> 08:14.430
Right.

129
08:14.470 --> 08:15.230
Column name.

130
08:15.670 --> 08:20.270
So as CT turbo, we can give.

131
08:23.030 --> 08:28.390
And over here we can give as CT quick.

132
08:29.430 --> 08:31.950
Now let me execute both of this query.

133
08:34.110 --> 08:34.630
Sorry.

134
08:34.870 --> 08:37.230
So this should be our answer, right?

135
08:37.270 --> 08:38.630
17 and four.

136
08:41.750 --> 08:48.310
So this is how you can use the multiplicity in this particular kind of scenario right.

137
08:49.390 --> 08:50.550
So thank you again.

138
08:50.670 --> 08:54.550
Uh we will meet again in the next question.

139
08:57.850 --> 08:58.290
So.

140
08:58.330 --> 08:58.730
Yeah.

141
08:59.290 --> 09:03.970
So assume you are given a table containing its user transactions.

142
09:04.690 --> 09:11.770
Write a query that retrieves the customers whose first transaction was valued at $50 or more.

143
09:12.730 --> 09:16.450
Output the total number of users who meet this criteria.

144
09:17.730 --> 09:24.130
Now, to determine the first transactions for each user, use the transaction date field and take into

145
09:24.130 --> 09:29.290
account scenario where a user had multiple transactions on the same day.

146
09:30.010 --> 09:35.610
Use a specific function to handle these cases and correctly identify the first transaction.

147
09:36.090 --> 09:39.410
So now here they have given us this table right.

148
09:41.450 --> 09:43.610
It is a user underscore transactions table.

149
09:43.610 --> 09:48.130
Let me try whether this table is there or not.

150
09:48.330 --> 09:50.090
Select star from.

151
09:53.050 --> 09:55.010
User underscore transactions.

152
09:56.210 --> 10:00.950
Okay so data is there right now.

153
10:00.950 --> 10:03.670
We need to identify from this.

154
10:04.270 --> 10:07.030
We need to identify the users.

155
10:07.310 --> 10:08.990
Let me do order by.

156
10:11.110 --> 10:13.470
Order by user ID and.

157
10:15.830 --> 10:17.030
Transaction date.

158
10:19.430 --> 10:20.070
Okay.

159
10:20.110 --> 10:27.230
So here we need to identify those users whose first transaction should be greater than $50.

160
10:27.230 --> 10:27.630
Right.

161
10:28.150 --> 10:34.910
So let me take which should be there 156 should be there.

162
10:37.950 --> 10:43.070
Right then one 7180 should be there.

163
10:44.830 --> 10:48.550
And 286 268 will should be there.

164
10:48.550 --> 10:48.990
Right.

165
10:49.750 --> 10:51.230
So we can say that.

166
10:53.630 --> 10:55.150
Three different users are there.

167
10:55.150 --> 10:55.590
Right.

168
10:56.690 --> 10:57.570
Perfect.

169
10:57.930 --> 10:59.770
Three different users are there.

170
11:00.730 --> 11:02.330
Let me show you how.

171
11:03.970 --> 11:07.650
So let me again run this query.

172
11:09.450 --> 11:11.850
Let me take this data into the Excel file.

173
11:12.530 --> 11:15.770
So this is the best way to understand any problem.

174
11:16.970 --> 11:19.730
Now let me show you.

175
11:23.530 --> 11:26.930
So this is the 111 first transaction right.

176
11:27.850 --> 11:32.010
But it is not more than $50 right.

177
11:32.410 --> 11:38.250
So this one will not come then this one again.

178
11:38.250 --> 11:39.890
This one will not come.

179
11:41.290 --> 11:43.170
Now let's check for this one.

180
11:43.650 --> 11:45.450
So this will definitely come, right.

181
11:45.450 --> 11:48.810
Because it is more than $50.

182
11:49.370 --> 11:51.090
Then check for this one.

183
11:51.090 --> 11:52.490
This one will not come.

184
11:57.520 --> 12:01.360
Then this one will definitely come.

185
12:06.280 --> 12:09.040
And this one will also come, right?

186
12:09.400 --> 12:12.000
So how many total distinct user IDs are there?

187
12:12.600 --> 12:13.880
156.

188
12:16.600 --> 12:17.520
Should be green.

189
12:18.600 --> 12:21.440
156 180.

190
12:22.400 --> 12:24.280
And then 268.

191
12:24.320 --> 12:26.240
It means three, right?

192
12:26.280 --> 12:26.800
Total.

193
12:28.280 --> 12:28.800
Perfect.

194
12:28.840 --> 12:31.040
Now, how can we solve this in SQL?

195
12:31.960 --> 12:33.320
So over here.

196
12:36.320 --> 12:38.640
Let me write this query again.

197
12:41.840 --> 12:42.360
Okay.

198
12:42.720 --> 12:44.960
Now what are the different columns are there.

199
12:45.440 --> 12:46.640
User ID right.

200
12:46.640 --> 12:48.600
So let me write user ID.

201
12:51.960 --> 12:55.720
Then we need to check row number.

202
12:58.300 --> 13:06.100
Over partition by user ID order by transaction date.

203
13:06.100 --> 13:12.060
Let me do the transaction date as arc now.

204
13:14.460 --> 13:20.740
Where spend we need to check spend right spend should be greater than $50 right.

205
13:21.580 --> 13:25.620
So let me write 50 over here.

206
13:28.980 --> 13:31.540
So let me execute this query okay.

207
13:36.460 --> 13:37.060
Okay.

208
13:37.100 --> 13:44.700
So it should be greater than 50 right now we need to check over here arc equal to one only.

209
13:44.700 --> 13:46.220
We need to check right one.

210
13:46.860 --> 13:48.380
So where it is one.

211
13:48.380 --> 13:50.140
Now let me check that one.

212
13:51.220 --> 13:57.040
So and also let me take other fields also over here user ID.

213
13:59.120 --> 14:01.200
Then transaction date.

214
14:01.600 --> 14:05.000
Transaction ID transaction date.

215
14:06.640 --> 14:08.360
What are the other fields are there.

216
14:11.840 --> 14:12.560
And spend.

217
14:12.560 --> 14:14.080
Also let me take spend.

218
14:16.440 --> 14:22.360
Now let me encapsulate this block into the city with city's.

219
14:26.000 --> 14:29.000
Select star from city.

220
14:29.960 --> 14:33.360
Now let me check in.

221
14:33.360 --> 14:36.280
Where one where r k equal to one.

222
14:36.280 --> 14:36.720
Right.

223
14:38.880 --> 14:40.280
Now let me take.

224
14:45.360 --> 14:47.520
So now let us see the result set.

225
14:49.480 --> 14:50.920
These are the result set right.

226
14:50.960 --> 14:55.790
Now our requirement is we need to find more than $50 transaction.

227
14:55.790 --> 14:56.190
Right.

228
14:56.630 --> 15:04.310
So and we can say that end spend greater than $50.

229
15:04.350 --> 15:04.790
Right.

230
15:07.310 --> 15:09.230
Let me take the result.

231
15:09.270 --> 15:09.710
Okay.

232
15:10.030 --> 15:19.070
So total three users are there right now over here we need to do simply count of user.

233
15:21.750 --> 15:25.990
ID as users.

234
15:26.830 --> 15:29.150
Now let me check the result set.

235
15:31.390 --> 15:31.750
Three.

236
15:31.750 --> 15:31.990
Right.

237
15:32.030 --> 15:32.750
Perfect.

238
15:33.150 --> 15:37.110
So this should be our result total 333 different users.

239
15:38.430 --> 15:44.190
Who has done the first transaction greater than $50?

240
15:44.190 --> 15:45.630
We can say that right.

241
15:46.870 --> 15:49.230
So thank you so much everyone.

242
15:50.790 --> 15:54.390
I will come up again with a few more questions in my next video.

243
15:54.390 --> 15:55.150
Thank you.