Start and End Number of Continuous Range | Advanced SQL Interview Question | Data Engineer Interview
Video 377: This is the 44th video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
02:10 - Approach 1: Using Window Functions in a CTE Format
06:45 - Approach 2: Using Window Functions in a Subquery
08:45 - Approach 3: Simplified Version
10:30 - Conclusion
We are given Logs table which got the log_id in a discontinued sequence. We are asked to find the start and end number of continuous ranges in the table
In this video, we discuss three distinct approaches to solve this challenge. Each approach is explained in detail to help you understand the logic behind the solution, including when and why each method works efficiently.
** Approach 1: Using Window Functions in a CTE Format **
We'll begin by leveraging row_number() in a Common Table Expression (CTE) to assign row numbers and group them into "buckets." This approach helps us identify continuous ranges of log IDs seamlessly by analyzing the difference between log ID and row number.
** Approach 2: Using Window Functions in a Subquery **
Next, we'll simplify the CTE logic by embedding it directly in a subquery. This method efficiently computes the start and end of each continuous log range by performing the calculations inline with the query.
** Approach 3: Simplified Version **
In the final approach, we further streamline the solution by eliminating unnecessary complexity. Here, we use window functions to group log IDs into ranges in the simplest and most direct way possible.
For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.
code: https://github.com/jeganpillai/adv_sq...
Follow me on,
Website : https://growwithdata.co/
YouTube : / @growwithdata
TikTok : / growwithdata
LinkedIn : / growwithdata
Facebook : / growwithdata.co
FB Group : facebook.com/groups/datainterviewpreparation
twitter : / growwithdata_co
Instagram : / growwithdata.co
WhatsApp : https://whatsapp.com/channel/0029VaF8...
TheWide : https://thewide.com/profile/891
#sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil