Video 23: Given a set of user id, the date and number of steps for each day, find the average steps of each user for their three consecutive days. If they miss any days inbetween, then skip those days bucket.
In this video, We have addressed this question in two ways
1. Aggregation of Steps using Self Join: In this method, we self join the Steps table using the User ID and with the date difference between the two rows, we aggregate only those date interval which has three rows in it.
2. Windows function with LAG() option, where we pulled the previous two rows along with the current rows, check if the difference between the start and end date is Three. If so, consider only those intervals and get the average steps
code: github.com/jeg...
Follow me on,
Website : growwithdata.co/
RU-vid : / @growwithdata
TikTok : / growwithdata
LinkedIn : / growwithdata
Facebook : / growwithdata.co
twitter : / growwithdata_co
Instagram : / growwithdata.co
WhatsApp : whatsapp.com/c...
#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
21 авг 2024