SQL : Purchases Happened On Friday

Video 48: We've been tasked with extracting the total purchase amount specifically occurring on Fridays from the Purchases table, which contains user IDs and spending amounts. To identify purchases made on Fridays, we utilize the weekday() or dayofweek() function available in MySQL, where Friday corresponds to a value of 6, while Sunday holds a value of 0. Our challenge was not only to retrieve existing Friday sales but also to display dates even when there were no purchases on Fridays, showing a total purchase amount of zero for those days. Initially, we manually hardcoded dates representing all Fridays in November 2023 for reference, but considering scalability issues, we opted for a more dynamic approach. We leveraged a recursive Common Table Expression (CTE) to generate a complete range of dates encompassing the entire month of November 2023. This dynamic date range served as our primary reference table, enabling us to join and aggregate purchase amounts specifically for Fridays throughout the month. By utilizing this recursive CTE approach, we ensured inclusivity of all dates, even those without sales, allowing us to accurately capture and display the total purchase amount for each Friday in November 2023, addressing both existing sales and cases where no purchases occurred on Fridays. For a comprehensive understanding of this SQL logic, please refer to the explanatory video. 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/v0024_purchases_happened_on_Friday.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