Frequently Ordered Products | Advanced SQL Interview Questions | Data Engineer Interview Question
Video 379: This is the 45th video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
05:00 - Approach 1: CTE logic
12:30 - Approach 2: Concat Subquery
15:30 - Approach 3: Correlated Subquery
18:45 - Approach 4: Window Functions
29:10 - Conclusion
We are given three tables, Customers, Orders and Products. We are asked to find the Frequently Ordered Products for each customer.
In this video, we are discussing using three distinct approaches. Each method demonstrates a different way of thinking about and solving this problem:
** Approach 1: CTE logic **
This approach uses a combination of subqueries to calculate the count of each product ordered by the customer and then for each customer, get the max order, finally we find the products with the maximum count for each customer.
** Approach 2: Concat Subquery **
This approach uses a combination of subqueries to calculate the count of each product ordered by the customer and then finds the products with the maximum count for each customer.
** Approach 3: Correlated Subquery **
Here, we dive into correlated subqueries, where we find the most frequently ordered product by comparing each product’s count to the maximum count for each customer. This method simplifies the selection process by using subqueries directly in the main query.
** Approach 4: Window Functions **
The third approach leverages window functions to rank the products ordered by each customer based on order frequency. Using count() and dense_rank(), we efficiently identify the most ordered products while maintaining flexibility in the query.
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