Coding Series - SQL : Restaurant Growth Tracker | SQL Interview | Data Engineer Interview Questions
Video 163: This is the 40th video in our series "Crack SQL Interview in 50 Questions," and in this exercise, we explore the topic of "Sub Queries"
We are given Customer table and asked us to find how much the customer paid in a seven days window
In this table, (customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer has visited the restaurant.
amount is the total paid by a customer.
The restaurant owner want to analyze a possible expansion
Compute the moving average of how much the customer paid in a seven days window
Few assumptions:
1. There will be at least one customer every day
2. Moving 7 days = current day + 6 days before
3. Average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.
In this video, we explore three distinct approaches for computing the moving average of customer transactions in a restaurant over a seven-day window.
** Approach 1: Self Join Technique **
This approach utilizes a self-join technique to calculate the sum of amounts for each visit date and then joins the table with itself to find the sum of amounts for the previous seven days.
** Approach 2: Row-Level Window Functions **
Here, row-level window functions are employed to compute the cumulative sum and average of amounts over the preceding seven days.
** Approach 3: Range-Level Window Functions **
Utilizing range-level window functions, this approach achieves the same result as Approach 2, albeit with a slight variation in syntax.
By exploring the implementation details and discussing the advantages and limitations of each approach, you'll gain a comprehensive understanding of different strategies for solving this analytical problem in SQL.
For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.
code: https://github.com/jeganpillai/crack_...
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...
#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
On this page of the site you can watch the video online Coding Series - SQL : Restaurant Growth Tracker | SQL Interview | Data Engineer Interview Questions with a duration of hours minute second in good quality, which was uploaded by the user Grow with Data 04 April 2024, share the link with friends and acquaintances, this video has already been watched 104 times on youtube and it was liked by 10 viewers. Enjoy your viewing!
