Incremental Games Count | Advanced SQL Interview Questions | Data Engineer Interview Question | SQL
Video 353: This is the 33rd video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
05:10 - Approach 1: Using Self Join
12:55 - Approach 2: Using Windows Function
17:40 - Conclusion
We are given Activity table which shows the activity of players of some games.
(player_id, event_date) is the primary key (column with unique values) of this table.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.
We are asked to write a solution to report for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date.
In this video, I explore two different approaches to solving the problem of calculating the total number of games played by a player up to a given date. Each approach offers a unique method for tackling this problem using SQL.
** Approach 1: Using Self Join **
In this approach, we use a self-join on the Activity table. By joining the table with itself on matching player_id and ensuring that the event_date of the second table is less than or equal to that of the first, we can sum up the games played by the player up to each date.
** Approach 2: Using Window Function **
This approach leverages SQL window functions to achieve the same result more efficiently. By using the SUM() function as a window function and partitioning by player_id while ordering by event_date, we can calculate the cumulative sum of games played for each player over time.
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