Crack Google SQL: Detect Increasing Trend (Most Candidates Fail!)

Today we are going to solve a Google SQL interview question which is to detect users with increasing value trend. We are given a table called user matrix with three columns user ID, activity date and value. And this is your problem statement. Google wants to identify users whose activity value keeps strictly increasing over time. So for example in our sample data set for user ID 1 the value keeps on increasing for each subsequent record sorted by activity date. But if we move on to user ID 2 we see that his value has decreased on the second record. So he should be excluded from our output. Let's start framing our solution. So we have selected the three columns from the table and now we are going to use the lac function over the value column to find out the previous activity value for each user. First we're going to partition by the user ID and order by the activity date. This is going to be our previous value. Execute. And now you have the current and the previous values for each record. Putting this within a CTE. And now writing another query on top of our CTE including a case condition to identify and set the flag as zero when the current value is greater than the previous value and one otherwise. The case when previous value is less than or equal to the current value then zero else one and as flag. If you execute this, you will see that the flag is set to zero for scenarios where the current value is greater than the previous value and set to one when the current value is lesser than the previous value. But we also observe that for this first record in each group, since the previous value was null, the flag was set as one. But we need to make sure that we account for this null and the first record for each user ID. and we're going to set this flag to zero because this is the first record and we're going to include it in a streak of increasing values. So here in a case statement we're going to make a change and include one more condition when previous value is null then zero. execute this again and you'll have a value of one for the flag only in conditions where the current value is lesser than the previous value. So it is breaking your continuously increasing value streak. Now the last part is going to identify those users who have continuously increasing values which means all flag values of zeros and no flag value of one. For that we're going to create and put our output from this query into another CTE. Just call it as CTE flag and write a select statement selecting a from a CTE flag group by user ID and including a filter using the having keyword and we're using having because we are performing a filter on aggregated data. we have used group by. So with the group by clause if you want to filter after the aggregated after the aggregation has occurred we need to use having and we're going to sum the flag that we just created in the flex cde. We're going to say sum of fle is equal to zero. So this is going to make sure that there were no ones for that user ID. And we need to include one more condition to make sure that there were greater than one record for each user ID. So if it's a new user and you have just one record for that user, he cannot be correctly identified as the user who has an increasing value trend because he has just a single record. So we're going to exclude those single records and say count star is greater than one. Now if you execute the entire query you're going to end up with the correct user ids one and three who satisfy this condition. The data set and SQL code used in this video is linked in the description of this YouTube video. You can try to practice it yourself and don't forget to check out the nightclub query in our next