Recent Three Orders | Advanced SQL Interview Questions | Data Engineer Interview Question | FAANG
Video 367: This is the 40th video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
04:30 - Approach 1: Using Correlated Subquery
10:30 - Approach 2: Using Windows Function
13:45 - Conclusion
We are given two tables Customers and Orders.
Customers table stores information about customers
Orders table contains information about the orders made by customer_id. Each customer has one order per day.
We are asked to write a solution to find the most recent three orders of each user. If a user ordered less than three orders, return all of their orders.
Return the result table ordered by customer_name in ascending order and in case of a tie by the customer_id in ascending order. If there is still a tie, order them by order_date in descending order.
In this video, we tackle an SQL problem where we need to find the most recent three orders of each customer. We explore two distinct approaches to solve this problem effectively:
** Approach 1: Using Correlated Subquery **
In this approach, we leverage a correlated subquery to identify the most recent three orders for each customer. The logic involves comparing the current order date with all other order dates for the same customer and counting how many are more recent. This approach is straightforward but can be computationally intensive for large datasets.
** Approach 2: Using Window Function **
Here, we use the RANK() window function to assign a rank to each order based on the order date within each customer group. This method is more efficient, especially when dealing with large datasets, as it avoids the need for repeated subqueries.
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
Auf dieser Seite können Sie das Online-Video Recent Three Orders | Advanced SQL Interview Questions | Data Engineer Interview Question | FAANG mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Grow with Data 21 August 2024 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 96 Mal angesehen und es wurde von 6 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
