Daily Maximum Transactions | Advanced SQL Interview Questions | Data Engineer Interview Question

Video 369: This is the 41st video of the SQL Interview Question series.

00:00 - Introduction to dataset and Question
03:15 - Approach 1: CTE (Common Table Expression) Approach
07:55 - Approach 2: Subquery Approach
09:00 - Approach 3: Correlated Subquery Approach
11:00 - Approach 4: Concatenated Values Approach
16:00 - Approach 5: Window Function Approach
18:35 - Conclusion

We are given Transactions table which have the transaction id, the timestamp when the transaction happened and the transaction amount.

we are asked to write a solution to report the transaction IDs with the maximum amount on their respective day. If in one day there are multiple such transactions, return all of them.

In this video, we explore five distinct SQL approaches to solve this challenge, each with its own strengths and variations in query structure. Below are the details of the approaches covered:

** Approach 1: CTE (Common Table Expression) Approach **
We utilize a CTE to first compute the maximum transaction amount for each day. This temporary result is then joined back to the original table to filter out the transactions matching these maximum amounts.

** Approach 2: Subquery Approach **
A similar logic to the CTE approach is implemented here, but instead of using a CTE, a subquery is used to calculate the maximum amount for each day, which is then joined with the transactions table to fetch the required results.

** Approach 3: Correlated Subquery Approach **
This approach leverages a correlated subquery to find the maximum transaction amount directly within the WHERE clause, effectively filtering the transactions without needing a join.

** Approach 4: String Concatenation Approach **
Here, we use a string concatenation technique to match the date and maximum amount of transactions in a single comparison, ensuring that only the transactions with the maximum amounts are returned.

** Approach 5: Window Function Approach **
We use the DENSE_RANK() window function to rank transactions based on their amount for each day. This approach is especially useful for scenarios where multiple transactions might share the same maximum amount.

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