SQL : Users who Started as Viewers then turned as Streamers

Video 46: In this tutorial, we explore identifying users who transitioned from being viewers to becoming streamers. It's akin to someone initially consuming content, gaining confidence, and eventually becoming a content creator themselves. Similarly, users who start as viewers and then switch to being streamers must exhibit a sequence where their initial session is as a viewer followed by at least one session as a streamer. We tackle this challenge through two distinct approaches: General Subquery: This approach involves first isolating the initial session for each user, focusing on those whose first session is marked as a viewer. Then, for these specific users, we count the number of subsequent sessions where their role transitions to being a streamer. Utilizing Windows Function: Here, we leverage the RANK() function within a window to determine the first session for each user. Filtering users whose rank is 1 and session type is a viewer, we subsequently track their sessions, specifically looking for the switch to a streamer role. Both approaches dissect the transition from viewer to streamer, offering insights into the user journey within the platform. For detailed SQL code and supplementary sample data, please refer to the GitHub file provided below. code: https://github.com/jeganpillai/sql_reference/blob/main/v0023_viewers_turned_streamers.sql Follow me on, Website : https://growwithdata.co/ YouTube : https://www.youtube.com/@growwithdata TikTok : https://www.tiktok.com/@growwithdata LinkedIn : https://www.linkedin.com/company/growwithdata/ Facebook : https://www.facebook.com/growwithdata.co/ twitter : https://twitter.com/growwithdata_co Instagram : https://www.instagram.com/growwithdata.co/ WhatsApp : https://whatsapp.com/channel/0029VaF8pkb77qVNfbp5pA0S #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