GOOGLE SQL Interview Question- FAANG SQL Interview Questions and Answers

本站添加: 2026-05-06

[00:00:00]Hello everyone, welcome back to another video on my channel. Today we'll be solving another company based SQL interview question. Today we'll be solving a question that was asked in Google and the difficulty level is medium. Let's begin with the question.

[00:00:15]Also, I have created a playlist where I solve company based SQL interview questions, so check it out in case you are preparing for some SQL interview.

[00:00:25]So, let's begin. Assume you're given a table with measurement values obtained from a Google sensor over multiple days with measurements taken multiple days within each day.

[00:00:37]Write a query to calculate the sum of odd numbered and even numbered measurements separately for a particular day and display the results in two different columns.

[00:00:48]Refer to example output below for the desired output.

[00:00:52]So, this is our table measurements. It has measurement ID, measurement value, and measurement time. Within a day measurements taken at first, third, and fifth are considered odd numbered measurements and measurements taken at second, fourth, and sixth times are considered even numbered measurements.

[00:01:09]So, this is our table and these are the measurement times. For a particular day, let's say 7 10 2022.

[00:01:17]There are three records.

[00:01:20]9 11 13. So, the first, third, and fifth times are considered odd numbered measurements and the measurements that were taken at second or fourth or sixth time or eighth and so on are considered even numbered measurements and we have to sum it up. So, if you see this output that they have created for 7 10 2022, the odd sum is 2355.75, which means let's say 7 10 2022, this one.

[00:01:51]For odd, which means one and three. So, 1109.51 + 1246.24.

[00:01:59]The value the first and the third measurement. That will go in odd sum, which is over here, 2355.75.

[00:02:08]And even would be 1662.74, which is the case over here. Now, for 7 11, we only have two records. So, the first one will be odd and the second one will be even. 1124.50 is odd and 123.

[00:02:23]1234.14 is even sum.

[00:02:28]So, let's write the logic. Let's write the query for this one. So, I would request you all to pause the video for some time and then try to write the query on your own and then get back to the video.

[00:02:44]So, what we need to do is for all the odd times, we have to sum it together and for all the even times, we have to sum it together.

[00:02:52]So, we need to use row number for each particular measurement time, measurement day actually. For each particular day, let's say 7 10 2022, we have to group it together and then we have to number it 1 2 3 and then we have to sum [snorts] it based on whether it is an even number or an odd number. Let's write the solution and then it will be much clearer. So, select star from measurements.

[00:03:27]And let's create row number. [snorts] Row number over partition by and we have to partition it by measurement time. Actually, not measurement time, but rather the date.

[00:03:49]So, I will use the date function and I will write measurement time.

[00:03:58]as RN1. I will give an alias. Let's run this.

[00:04:06]So, what this is doing is for for each date let's say 2022 7 10 it is grouping it together and then it is giving row number.

[00:04:19]1 2 3. Then for 7 11, we have 1 2 3 4.

[00:04:24]For 12, we have 1 2 3 and so on.

[00:04:28]So, now we have even numbered it. Now, we need to just sum all the odd records and then sum all the even records. So, we can use a CTE.

[00:04:39]We can put this in a CTE.

[00:04:46]With CTE _measurements as. So, this is our CTE where we have the row numbers.

[00:04:59]Now, we need to just group all the odd records and the even records together. So, select measurement. So, in our output, the first column is measurement day.

[00:05:11]So, I will do date of measurement time because we do not need the time, we just need the day.

[00:05:23]And I will give an alias measurement day.

[00:05:30]Comma, now we need to sum all the odd records and sum all the even records.

[00:05:35]So, sum and then I will use a case statement.

[00:05:39]Case when RN1 mod two is not equal to zero.

[00:05:49]So, whenever the mod two is not equal to zero, it means it is odd.

[00:05:55]Then I will take measurement value.

[00:06:04]Else zero.

[00:06:08]Sum of case when RN1 mod two is not equal to zero, then measurement value else zero.

[00:06:16]This will be odd_sum. This will be odd sum.

[00:06:23]And let's write similarly for even sum.

[00:06:31]When RN1 mod two is equal to zero, then it it means it is an even number. That particular RN is even number.

[00:06:42]So, this will be even sum from CT_measurements.

[00:06:54]Also also we need to order the row number by measurement time.

[00:07:09]So, row number over partition by that particular day and then we have to order it by measurement time in ascending order only.

[00:07:20]So, I will not write anything after that because by default it is ascending.

[00:07:24]Now, our CTE.

[00:07:26]This is where the CTE ends.

[00:07:30]This is our CTE.

[00:07:42]So, this is our CTE. Our CTE is pulling data from the measurements table and it is also pulling row number. What is row number doing? It is partitioning by that particular day and then it is ordering in measurement time and then it is giving a row number.

[00:08:00]Because in our question, within a day measurements taken at first, third, and fifth times are considered odd numbered measurements and measurements taken at second, fourth, and sixth times are considered even number. So, we also need to order it.

[00:08:13]Now, in our CTE, we are pulling the measurement day, then the odd sum, even sum from CTE measurements and then we will group it by measurement day.

[00:08:27]And then we also will be ordering it by measurement day.

[00:08:34]Let's run this.

[00:08:45]Also, we need to end the case statement.

[00:08:51]End. Let's run this again.

[00:08:54]And this is our output.

[00:08:57]Let's submit this.

[00:08:59]And it has been accepted. So, it's a very simple question. We will be using CTE. You can also use subquery. We will be using row number and then we will order it by measurement time and we will give an alias RN1 and then we will use the row number. We will use the row number to find whether it was uh it was whether the measurement was taken at an odd time or an even time. So, we will use the mod function to find out even or odd number.

[00:09:30]And then we will just use case statement along with sum to get that output. And then we'll group it by measurement day and order it by measurement day. And that is our output.

[00:09:42]I hope this video was helpful. Thank you so much for watching.