Are you ready to go beyond basic day 1 retention? In this tutorial, you’ll learn how to calculate a critical yet overlooked measure of engagement: consecutive login streaks. Perfect for mobile games analysts, this tutorial will teach you how to calculate the percentage of your audience with consecutive login activity. Specifically, you'll know the percentage of your DAU that has started a consecutive login streak, has finished one, has one in-progress, or is not in one at all. With clear instruction in MySQL, you’ll unlock the power to understand your audience’s retention on a whole new level. Take the next step in your analytics journey and transform your approach to retention today!
Watch on YouTubeIn this tutorial, I'll show you how to detect late data in your analytics. With the right SQL techniques, I'll show you how to stay ahead of latent data and protect against disruptions to timely decision-making. Join me as I guide you through MySQL techniques for tracking data latency, including how to use insertion date-time columns to identify late data entries. We'll then isolate the latent records and export them to Excel, so you can easily share the results with your data engineering team. If you're passionate about data accuracy, this tutorial provides hands-on strategies for spotting and addressing data latency!
Watch on YouTubeIn this tutorial, you'll learn how to measure the performance of your user acquisition by calculating the percent of your install cohort that has churned by days since install. This method will calculate how many users leave after a certain period of time after acquisition. We will be stepping through a real-world example where we will track an install cohort from September 2023 to October 2024 measuring percent churned by days since install. We'll define churn and explore SQL techniques like selecting cohorts, calculating churn flags, and grouping results to reveal key trends. A highlight of this analysis will be refining your insights by filtering out customers who never engage beyond their install date. By the end of this session, you'll have the technical guidance to add real value to your user acquisition strategy by calculating churn on a timeline for customers you acquire.
Watch on YouTubeIn this tutorial, you'll learn how to analyze the retention of your installs by comparing how many days they've logged in to how many total days they are from their install date. Through this, you'll be able to calculate retention in a unique and creative way. The SQL query begins by selecting customer installs then joins this data with login records to determine the percentage of total days since install that a customer has logged in. The result is a cumulative retention percentage for installs. In the end, the data is presented in a pivot table where retention rates for different install cohorts can be compared, creating a useful tool for improving app retention.
Watch on YouTubeIn this tutorial, you'll learn how to assess the "stickiness" of your digital audience by analyzing the percent of daily active users (DAU) who log in on consecutive days over the last week. Instead of just counting DAU, the analysis goes deeper by identifying users who logged in on back-to-back days, like Monday and Tuesday or Thursday and Friday. This measure helps to determine how engaged your audience is and how frequently they return to your product. The SQL code uses a customer logins table, joined with itself to track login activity over a trailing seven-day window. Then, it calculates whether a customer had consecutive login days within that window. The result is a more nuanced view of your DAU, showing both total daily users and the percentage that exhibit this consecutive login behavior. This is a powerful tool for gauging audience loyalty and user engagement over time.
Watch on YouTubeWant to know who your die-hard customers are? The ones that evangelize your products and brand the most? In this tutorial I'll show you how to write a dynamic SQL query to return these exact customers. Do you want to accomplish this by returning customers that have logged in each day over the past three weeks? Month? Or some other timeframe? Through this video you'll learn all this and be well on your way to deepening these critical relationships. None of the data shown is real PII (personally identifiable information), it's fictional data generated from the following Python modules: names, random_address, and phone_gen.
Watch on YouTubeWant a data-driven approach to reactivating lapsed spenders? Using SQL, you'll be able to assess the likelihood of customers purchasing again as time passes. You'll be able to us this information to inform when to offer incentives like virtual currency to re-engage spenders before their odds of purchasing drops too low. In this tutorial, I'll show you how to calculate the percent of monetizers that purchase by days since their last purchase. By analyzing the results, you'll be able to target the right customers at the right time. Perfect for data analysts and online marketers, this approach can help reactivate revenue without cannibalizing future earnings.
Watch on YouTubeIn this tutorial, you'll learn how to calculate Weekly Active Users (WAU) using a rolling 7-day window. With this method, you'll avoid calculating your WAU from week start (Sunday or Monday), helping to escape the confusion of having your query return an incomplete week. Instead, you'll be doing a count of unique logins over the last seven days to always have up-to-date, reliable data. This tutorial demonstrates how using this method delivers a continuous - unbroken - view of user engagement.
Watch on YouTubeIn this tutorial, you'll learn how to measure customer lifecycle by analyzing cumulative logins per install over time. The tutorial walks you through using a customer's install date to count logins cumulatively by days since install. The resulting curve shows the average lifecycle where the chart flatlines - marking the point where logins have stopped but days since install continue. This method shows you how long customers engage with your online business allowing you to strategize around your customer's average lifecycle.
Watch on YouTubeIn this tutorial, you'll learn exactly when to offer video ads without cannibalizing in-app purchases. Specifically, the video demonstrates how to find the cumulative percentage of first-time monetizers by days since install. At the point where the curve flatlines, you can safely introduce video ads to non-monetizers and open a new revenue stream without cannibalizing in-app purchases. This tutorial provides a detailed SQL query that extracts this information and shows how to visualize it in Excel for better business decision-making.
Watch on YouTubeIn this tutorial, you'll learn how to sort your customers into an 80/20 A/B test using SQL. You'll be using a MOD function to split your customers into control and treatment groups. By dividing customer IDs by 5 and analyzing the remainder, you'll assign 20% of customers to the control group (remainder 0) and the remaining 80% to the treatment group (remainders 1, 2, 3, or 4). The tutorial demonstrates how to apply the logic, ensuring that customers are correctly and randomly sorted. It then validates the distribution to confirm that the correct percentage of customers is assigned to each group, providing a simple yet effective method for setting up an unequal A/B test. This is a valuable guide for anyone looking to optimize testing strategies within their business analytics.
Watch on YouTubeUsing SQL, you'll learn how to sort customers into groups for multivariate testing. Through the SQL MOD function you'll learn how to divide your customers into evenly distributed groups. The example sets up one control group and three treatment groups, sorting customers into four total groups based on their numeric customer IDs. SQL's MOD function will divide customers evenly, generating remainder values used to assign customers to different groups. This tutorial will also show how to aggregate and summarize the customer data, ensuring that each group is balanced. With this method, you'll be able to configure experiments with accurate, random, and properly-weighted groups.
Watch on YouTubeIn this tutorial, you'll learn how to assign customers into 50/50 A/B groups using SQL. By using the MOD function - which divides numeric values and returns a remainder - you'll be able to sort customers by applying this operation on their customer IDs. This approach ensures that customers are evenly split into one of two groups - either test or control - based on the remainder of the MOD function. This video walks you through each step of building the query, running the MOD function, assigning customers to groups based on the remainder value, and running the final validation to ensure both groups are equally sized. At the end, you'll be able to take these methods and apply it to other A/B testing scenarios.
Watch on YouTubeIn this tutorial, you'll learn how to keep your financial data up-to-date using SQL. Rather than reporting on a historical snapshot of your company's finances, I'll show you how to build a query that refreshes financial data each time it executes. This walkthrough will also show how to calculate revenue metrics on a weekly, monthly, quarterly, and yearly rolling basis, so you can track trends in real-time. By aggregating revenue data over different periods and ensuring you have enough historical data for each calculation, you'll be able to get live feedback on how your business is performing without relying on outdated reports. This tutorial is perfect for anyone looking to integrate real-time financial tracking into their SQL workflows.
Watch on YouTubeIn this tutorial, I'll show you how to recapture revenue for your online business all while avoiding the high costs of acquiring new customers. In this powerful, low-cost strategy, I'll show the exact SQL to identify churned monetizers and return their lifetime revenue so you can properly asses the opportunity - and benefit - of reactivating them. By the end of this walkthrough you'll be convinced that focusing on existing monetizers and leveraging your churned database is the best go-to option for growing revenue without making product changes.
Watch on YouTubeIn this tutorial, you'll learn how to perform SQL joins using multiple keys. We'll end by joining two tables together - a logins table and a revenue table by customer ID and date. Step-by-step we'll verify each table's granularity to ensure compatibility before the join. As part of checking granularity, you'll see how uniqueness is defined in each table using SQL aggregate functions. With this knowledge, you'll confidently join tables with more than one key, preserving all records from one table while pulling in matching records from the other table. This tutorial also highlights how to convert null values to zeros for cleaner output, collectively giving you the skills to create custom datasets.
Watch on YouTubeIn this tutorial, you'll learn how to perform your very first SQL join. The video walks you through combining two tables - one containing customer data and the other containing suppression codes - using a left join. You'll see how to ensure the tables are compatible by checking their granularity and identifying a common join field. The process of bringing in relevant fields from both tables is explained step-by-step, along with a practical demonstration of handling null values by converting them into a meaningful string. This tutorial will help you confidently create custom datasets while ensuring data integrity when performing SQL joins.
Watch on YouTubeIn this video, you'll dive into a powerful SQL analysis that extends beyond just tracking Monthly Active Users (MAU). The tutorial calculates the percentage of your monthly active users that login daily (DAU) and weekly (WAU), providing actionable insights into customer engagement and retention. By using advanced SQL queries, you'll learn how to track the percentage of your rolling MAU that login daily and weekly, giving a complete view of how frequently users interact with your platform. This approach allows you to identify key engagement gaps, such as discovering that a significant portion of monthly users may not be logging in weekly, highlighting a potential growth opportunity for improving user retention. This analysis helps you not only understand the size of your audience but also the cadence of their interactions, highlighting where you can improve engagement.
Watch on YouTubeIn this video, you'll supercharge your Daily Active Users analysis by cohorting DAU by days since last login. Using a clever SQL trick, you'll aggregate users based on their last login date, categorizing players into brand new installs, highly active users, or infrequent/inactive customers. This approach adds rich insights to your DAU reports by revealing the rhythm and cadence of your user logins. With clear step-by-step instructions, you'll not only track overall DAU but also understand user login behaviors, improving the quality of your DAU retention strategies. This categorization of users by days since last login brings valuable clarity to how recently and frequently customers engage with your product.
Watch on YouTubeIn this tutorial, you'll discover how to cohort daily active users (DAU) by their install month to better understand your app's player life cycle. You'll be writing a query that retrieves a count of DAU segmented by install month for your app. This method will reveal critical insights, such as the average age and tenure of your DAU. By viewing your DAU through the month of their install, you can infer the typical life cycle of users within your business. The tutorial will focus on a demo business, and show how more than half of the DAU comes from users who installed within the last three months. This type of analysis is essential for refining your marketing, engagement, and customer retention strategies.
Watch on YouTubeIn this video, you'll learn how to elevate a basic daily revenue chart into a more insightful analysis. Together, we'll create a daily revenue chart with trendlines for average trailing revenue over the last 7, 14, and 30 days. With this method you'll be able to see if daily revenue spikes or drops are significant as compared to the trailing performance. By following along with a practical MySQL example, you'll see how to aggregate daily revenue and create comparisons to trailing averages. Whether you're trying to identify high-performing days or spot early warning signs of revenue slumps, this tutorial is a valuable addition to your SQL toolkit.
Watch on YouTubeBefore you spend valuable resources acquiring new customers, consider leveraging your database of churned players. In most cases, it takes fewer resources to reactivate a churned player than acquiring a new customer. In this tutorial, I'll show you how to calculate a daily and cumulative count of churned players by day since product launch. In doing so, you'll be able to not only see how many customers go inactive per day, but also return the cumulative count of churned players overall - a measure for the total audience available for marketing reactivation. Seize this knowledge now to grow your audience without the costs of acquiring brand new users!
Watch on YouTubeIn this tutorial, I will show you how to measure the performance of your reactivation efforts - or those marketing campaigns targeted to inactive or dormant players. You will learn how to return a count of customers that have returned to your online business after being lapsed for thirty or more days, and additionally, will see what percentage of them return the day after their reactivation date (day-1 retention). Together, you will not only see the total volume of returning players, but also the percentage of which return the day after they reengage with your business. This will give you tremendous insight into your ability to not only resurrect a dormant user but to also keep that user after their reactivation date. As a business stakeholder, it's important to compare day-1 retention on reactivated users to new installs to see which of the two channels is superior in growing DAU.
Watch on YouTubeIn this tutorial, I will show you a more holistic way to measure the size of your monetizing audience. For many online businesses, it's common to only calculate the number of unique monetizers per day. However, in this tutorial we will broaden this calculation to also include a count of unique monetizers by week and month. As a business owner, seeing the number of unique monetizers per day, week, and month will reveal how many casual or infrequent purchasers you have, along with the size of the opportunity to change your product offerings to create more frequent purchasers.
Watch on YouTubeIn this tutorial, you'll learn how to calculate Daily Active Users (DAU), Weekly Active Users (WAU), and Monthly Active Users (MAU) using SQL. To create these metrics you will be doing a distinct count of users across the following periods: daily, weekly, and monthly. First, DAU is calculated by counting distinct customer logins on each specific day. Second, WAU is calculated by counting distinct customer logins over a trailing seven-day period (including the current date and trailing six days). Lastly, MAU is calculated by counting distinct customer logins over a trailing 30-day period. By comparing these three metrics, you'll be able to see the size of your highly engaged audience logging in daily as compared to your more infrequent audience logging in weekly or monthly. This comparison will show you how much opportunity you have to convert infrequent users into engaged customers.
Watch on YouTubeIn this tutorial, you'll learn how to leverage ChatGPT to predict future Monthly Active Users (MAU) using SQL and an ML algorithm. The video walks through the process of calculating historical MAU for each month based on customer login data. Once the data is prepared, it's uploaded to ChatGPT to predict the incomplete month using either a nonlinear regression or time series algorithm. The tutorial concludes by visualizing the MAU trend, showing both historical data and the predicted value for the current month, demonstrating how to enhance basic analysis with ChatGPT.
Watch on YouTubeYou know the size of your digital audience (DAU), but you want to know how often your audience logs in weekly. Are your customers logging in every other day on a weekly basis? In this tutorial, we will answer exactly this by adding a trendline to your DAU chart showing the average number of logins for your DAU over a trailing week period. The final query computes DAU and average logins over the last seven days to show how often users return to your platform, providing your team clearer product direction through user behavior.
Watch on YouTubeIn this tutorial, we will create a waterfall chart of all purchasers according to the total number of transactions they've made. Starting with 100% of your paying customers having made at least one purchase, the query walks you through how to calculate the percentage of those who make two or more purchases, three or more, and so on. The value in this analysis is understanding how good your online business is at generating repeat purchases. This analysis is particularly useful for businesses that struggle to get customers to convert a second time after their first purchase. In the demo business showcased in the tutorial, only 45% of customers make it to a second purchase, implying a 55% drop off from first purchase to second purchase. With this analysis, issues like this can be quickly identified, empowering you to make proactive changes to your purchaser economy.
Watch on YouTubeWant to know the monetizing potential of your online business? Instead of looking at just a count of unique monetizers per day, also calculate the number of unique monetizers over the trailing month. Doing so will show how wide your monetizing audience is, along with the size of that audience that doesn't purchase frequently. By comparing the daily count of purchasers to the monthly count of purchasers, you'll gain insight into the breadth of your monetizing audience and with it the knowledge to begin optimizing your online business.
Watch on YouTubeIn this tutorial, you'll learn how to calculate rolling Monthly Active Users (MAU) using SQL. Traditionally, business owners wait until the month ends before calculating their MAU. However, in this new method, you'll be able compute MAU anytime you want as a trailing 30-day lookback, allowing you to monitor this metric continuously without having to wait for a month to close. This technique is highly useful to keep your reporting up-to-date and meaningful without having to wait for a period to end.
Watch on YouTubeIn this video, you'll learn how to automate detecting late data in your database using Python. The tutorial walks you through identifying tables with outdated data to help you avoid downstream surprises like broken BI dashboards. Our solution will be to loop through each table in your database, returning date columns, then calculating the maximum date and comparing it to the date our data should be through. If the maximum date value is less than the expected date then we can assume the data is not updated. The automation stores these results in a pandas DataFrame, which is then saved both locally as a CSV file and back to your database as a SQL table, making the information easily accessible for your team. This method offers a proactive approach to dealing with late data, allowing your team to catch data issues early and often.
Watch on YouTubeIn this tutorial, you'll learn how to automate the task of creating a data dictionary using Python. Instead of manually writing down table names, column names, and data types, this video will show you how to automate the entire process into a clickable workflow. You'll discover how to connect Python to MySQL, how to loop through tables and columns, and how to generate a data dictionary using pandas. This video will also cover how to save the data dictionary as a CSV onto your local machine and even how to upload it back to your MySQL database as a SQL table. This approach ensures that your data dictionary is always up-to-date and accessible within the database itself, providing a valuable resource for analysts working with the data. This tutorial is perfect for data professionals looking to streamline their documentation process and reduce manual effort.
Watch on YouTubeIn this video, you'll learn how to calculate the percentage of your Daily Active Users (DAU) who become 30-day churned. You will see exactly how to determine who will stop interacting with your business over a forward-looking 30-day period. On the technical side, you'll accomplish this by learning how to implement a self-join, leveraging window functions, and building a case statement to flag churned users. By the end, you'll know how to calculate a comprehensive churn rate for your digital audience to better setup your business for audience growth.
Watch on YouTubeIn this video, you'll learn how to calculate Monthly Active Users (MAU) and an average of Daily Active Users (DAU) by month using SQL. Together, these two metrics are crucial for understanding the engagement of your online business. The tutorial gives you a technical walkthrough on how to calculate a distinct count of users per month (MAU) and count of users per day (DAU) averaged for the month. Following this, we will be evaluating an example business by calculating their DAU/MAU ratio - a metric used to evaluate the retention strength of an online business. If the DAU/MAU ratio is 100%, then the online business has a perfect retention rate where all monthly users also login daily. While this is rarely the case, we'll be comparing the example business to a typical DAU/MAU ratio of 20%, where about a fifth of a company's monthly users also login daily. After this case study you will feel empowered to measure audience retention for online businesses.
Watch on YouTubeHave you ever wondered if your digital audience is growing or shrinking? In this tutorial, I'll show you how to calculate the net increase or decrease of customers to your digital audience so that you can understand if your audience size is trending up or down. In MySQL, we'll calculate the number of installs and churners to create a final value representing net audience growth: Installs Net Churners (Installs - Churners). If the resulting value is positive we'll know audience size is growing, if negative we'll know audience size is shrinking. By the end of this video, you'll be equipped with a SQL-based approach to understanding whether your digital audience is growing, shrinking, or staying the same, offering valuable insight into how many users you need to maintain a certain audience size.
Watch on YouTubeIn this tutorial, you will learn how to calculate customer churn using SQL, a critical metric for businesses looking to monitor their audience size. The video walks you through a practical example where churn is defined as fourteen consecutive days of customer inactivity. On a technical level, if fourteen or more days have elapsed from a customer's last login date, we'll know they have churned and left the product. By the end of this tutorial, you'll know how to generate a churn report that provides insight into how many customers your business loses daily, allowing you to plan the number of new users needed to offset these losses and grow your audience. This tutorial is essential for analysts focused on customer retention and churn dynamics.
Watch on YouTubeHave you ever wondered if the first purchase a customer makes determines their lifetime value? In this tutorial, we will explore this exact topic by cohorting all monetizers by their first purchase price and then computing their CLV (customer lifetime value) from their conversion date. The video walks you through identifying the first revenue transaction per customer using window functions, aggregating revenue cumulatively from the customer's first conversion date, then totaling revenue aggregated to the first purchase price. By applying this technique, you'll be able to compare the lifetime value of customers who convert at different price points (e.g., $0.99 vs. $7.99), showing how initial pricing affects long-term customer profitability.
Watch on YouTubeIn this tutorial, you'll learn how to calculate your customer's average lifecycle using SQL. Specifically, I will show you how to pinpoint the exact place - analytically - where this occurs. The process involves aggregating logins cumulatively from a customer's first purchase date. At the point where the chart flatlines is where your customer's average lifecycle is. This insightful analysis provides valuable information into how long a business can expect a customer to remain engaged.
Watch on YouTubeIn this tutorial, you'll learn how to add trailing averages to your data using SQL - a useful technique to maintain focus on broader trends. By incorporating rolling averages, you'll be able to confidently deflect questions about one-off data points and redirect attention back to the overall picture. In our example we'll be calculating a 14-day trailing average for daily active users (DAUs) to smooth out daily fluctuations and enhance our analytics. To ensure accuracy of the rolling average we'll be excluding any days with fewer than 14 preceding - or historical - records. Finally, the results will be exported to Excel where a dual axis chart will be created, featuring both the daily active users and the 14-day trailing average to provide a clearer view of trends over time. With this method you'll be able to address outliers while maintaining focus on the overall narrative.
Watch on YouTubeIn this tutorial, you'll learn how to track retention each month beginning from a customer's install month. The analysis groups customers by the month they installed and then measures their retention across forward-looking months. By the end of this technical walkthrough, you'll have a chart visualizing retention performance on a monthly basis for each install cohort starting from the month of install. This analysis will serve as an excellent tool for businesses looking to understand the long-term retention on their installs.
Watch on YouTubeIn this tutorial, you'll learn how to transform a basic revenue chart by adding a unique trendline: the average days between customer purchases. In this video, we'll segment revenue between new and existing monetizers, and if a customer has made a previous purchase, calculate the days between purchases. We will then take the revenue segmented by new or existing, stack it, then add the trendline representing the average days between purchases for existing monetizers. This enhanced visualization will help businesses understand the purchasing rhythm of their customers, offering analysis that is both more actionable and compelling.
Watch on YouTubeIn this tutorial, you'll learn how to add a thought-provoking metric to your analytics: day 1 retention for purchasers. Rather than counting the number of purchasers per day, you'll track how many purchasers return the day after monetizing. In the end, you'll be able to visualize the results in a chart including both the count of purchasers and the percentage that return the following day. This unique approach is perfect for analysts looking to elevate their understanding of purchaser retention, while also giving your organization the ability to pinpoint customer service issues if a monetizer does not return the day after making a purchase.
Watch on YouTubeIn this tutorial, you'll learn how to enhance your revenue reporting by adding a refreshing segmentation technique. To complement our revenue bar chart, we'll add a count of new spenders (customers making their first purchase) and reactivated spenders (customers purchasing again after 30 or more days) as trendlines. By comparing purchase dates with previous transactions, you'll be able to classify each customer and aggregate the results. This technique transforms a simple "revenue by day" chart into a more insightful report, showing not only daily revenue but also the trend of new and reactivated purchasers over time.
Watch on YouTubeIn this tutorial, you'll learn how to calculate return on ad spend (ROAS) by tracking cumulative revenue per install over time, allowing you to determine when breakeven happens on your marketing investments. Starting from a customer's install month, you will learn how to calculate cumulative revenue per install for all leading months. The result will be a chart showing how much cumulative revenue an install generates over time starting from their install month. In the video's example, we will see an initial cost to acquire a customer at $2.70 getting recouped after a year (12 months) post installation. Such an analysis is invaluable for businesses aiming to be cost conscious with their ad spend.
Watch on YouTubeIn this tutorial, you'll learn how to calculate day 1, day 3, and day 7 retention rates for installs. The video walks you through determining if customers return after 1, 3, or 7 days post install. In addition, I'll explain the guardrails needed to ensure retention metrics are only calculated for installs who have enough leading data to assess their return behavior. After calculating the retention rates, the results will be exported to Excel, where the retention percentages are visualized in a chart. This project is perfect for data analysts looking to understand retention-based metrics for apps or online services.
Watch on YouTubeIn this tutorial, you'll learn how to use SQL to determine if your business is "whale-driven," where a small percentage of customers generate a significant portion of revenue. You'll start by aggregating revenue by customer and using a window function to rank customers based on their total revenue. Next, you'll calculate the percentage of total revenue compared to the percentage of total players to detect whale activity. If there's a huge imbalance, then your business is whale driven. The final data is then exported to Excel for further analysis to prove whale activity through revenue distribution.
Watch on YouTubeEver wanted to know how this Monday's revenue compares to last week's? In this tutorial, I'll show you how to compare revenue for the same weekday between current and prior week. I'll guide you through creating day-of-week labels, aggregating revenue for each day, and differentiating between the current and prior weeks using SQL window functions. After structuring the data, we'll export it to Excel, where a pivot table will generate a histogram comparing revenue for each day of the week. This method is ideal for quickly identifying week-over-week performance, ensuring you can make meaningful comparisons to stakeholders.
Watch on YouTubeIn this tutorial, you'll learn how to use SQL to rank and return your top 10 best customers by revenue. We'll walk through aggregating revenue by customer then creating a window function to rank customers based on their total revenue. Upon ordering the results descending, the highest spenders will rise to the top. We will then create a variable allowing us to return the top 10, top 100, or even the lowest-spending customers dynamically. Finally, the results will be exported to Excel for further analysis. If you're looking to master SQL ranking functions start your journey here!
Watch on YouTubeIn this tutorial, you'll learn how to create a powerful year-over-year revenue comparison tool. The video walks you through returning current and the prior year revenue, then - using self joins - aggregating that revenue cumulatively by days into the year. After preparing the data, we'll export it to Excel to build the final year-over-year chart. This chart allows for easy comparison between current and prior year, making it an excellent tool for revenue pacing.
Watch on YouTubeIn this tutorial, you'll tackle cleaning up messy, duplicate data in SQL. Using a real-world example, you'll learn three powerful techniques to identify and remove duplicates without losing valuable records. The tutorial covers three hassle-free methods for deduplication: using DISTINCT to collapse duplicates, grouping records with GROUP BY to achieve the same result, and applying a window function to index and filter out duplicate entries. These techniques will equip you to effectively clean your data and ensure it's ready for analysis. Perfect for any data professional dealing with large, messy datasets.
Watch on YouTubeIn this detailed tutorial, you'll learn how to create a SQL month-over-month revenue chart that allows you to compare performance across different months in the current year. The walkthrough starts by filtering data to include only 2024 records, then building out columns to display month names and days into each month. YouÆll also calculate daily differences and sum revenues for each day. By using self-joins, youÆll add a cumulative revenue column, enabling you to track revenue trends within each month. The tutorial culminates with exporting the SQL results to Excel, where youÆll create a pivot table to visualize and compare month-to-month revenue pacing. This is a perfect project for data analysts aiming to refine their SQL skills and enhance their data visualization capabilities.
Watch on YouTubeIn this tutorial, you'll learn how to use a SQL GROUP BY to transform your raw data into different aggregations. Through three examples, you'll learn how to aggregate revenue by day, by state, and by month. I'll show you how to quickly accomplish this by modifying your base query, and even impart a clever SQL server technique to help you complete the final aggregation by month. With the data properly aggregated, you'll then build charts in Excel, giving you practical experience in presenting data visually for business reporting.
Watch on YouTubeIn this tutorial, you'll master the SQL self join, a powerful technique used to retrieve data from within the same table. Through a practical example, we'll use a self-join to calculate a trailing 7-day average - a common business practice to help smooth out reporting in revenue charts. The video walks you through the logic of joining a table back to itself, summing up the current and previous six days' revenue, and creating a new column with the 7-day average. Perfect for those looking to enhance their SQL reporting and charting skills, this tutorial demonstrates how self joins can help you present more insightful, polished data to stakeholders, ensuring that your visualizations meet the demands of executives and analytics teams alike.
Watch on YouTubeIn this tutorial, you'll learn the fundamentals of SQL views and discover how they can dramatically improve your database efficiency. SQL views allow you to store query results in a virtual table that can be reused by multiple analysts, freeing up system resources. The video walks you through a real-world example by consolidating an often-run query into a view, resulting in the elimination of redundant executions, and the creation of a more streamlined workflow. In addition, you'll see how views allow for underlying table modifications without incurring disruptions. By the end of this tutorial, you'll discover that SQL views are a powerful tool for any engineer looking to optimize database performance and downstream reporting.
Watch on YouTubeIn this beginner-friendly tutorial, you'll learn how to use SQL CROSS JOINS to manage many-to-many relationships. CROSS JOINS multiply records from two tables, creating all possible combinations between them. Using a practical example, the video walks you through managing tourist data and equipment needs for a Mount Kilimanjaro expedition. By cross joining two tables, one with five tourists and the other with five pieces of equipment, you'll generate a 25-record result set with each tourist having every piece of equipment. The tutorial clearly demonstrates how this powerful SQL join can be applied in real-world scenarios, simplifying complex relationships and streamlining your workflow. Ideal for anyone looking to enhance their SQL expertise with a hands-on example.
Watch on YouTubeIn this tutorial, you'll dive into the differences between SQL UNION and UNION ALL and learn how to use them effectively in SQL Server. Both commands allow you to combine records from two tables into a single dataset, however the distinction lies in how duplicates are handled. With an example merging top 10 spenders from 2022 and 2023, you'll see how UNION ALL keeps all records (inclusive of duplicates) whereas UNION drops duplicate entries. By following this step-by-step walkthrough, you'll gain a clear understanding of when to use each method to optimize your data.
Watch on YouTubeIn this tutorial, you'll discover how to audit SQL tables using the power of full outer joins. Gone are the days of manual, time-consuming record-by-record validation. Instead, this video walks you through validating revenue tables side-by-side using full outer joins to return all records, making it easy to identify discrepancies in seconds. The tutorial also takes it a step further, showing you how to add a WHERE clause to filter and return only mismatched records. This method transforms complex auditing tasks into a quick, error-free process, perfect for any data professional.
Watch on YouTubeIn this concise video, you'll break down the intimidating SQL right join and discover how simple it can be with the right guidance. By using MySQL, you'll experience an example focusing on two tables: one with customer emails and the other with player revenue. You'll find that the key to a right join is that the second table remains your base, where its records remain preserved even when there are no matches from the first table. With clear explanations and practical steps, you'll see how easy it is to use right joins in practical settings.
Watch on YouTubeIn this engaging tutorial, you'll master the power of SQL inner joins while working with real data in MySQL. An inner join only retains records present in both tables, behaving like a logical AND statement. To further your knowledge, you'll explore how this join can help filter data effectively without using a WHERE clause. You'll also experience a practical business scenario: filtering a customer list down to Colorado residents who are also high spenders. In service of this example, you'll see how the inner join naturally excludes non-spenders and out-of-state customers, leaving you with the precise data you need. By the end, you'll be equipped to use inner joins confidently to tackle real-world SQL challenges.
Watch on YouTubeIn this quick tutorial, you'll master the concept of SQL LEFT JOINS in just a few minutes. Diving into this hands-on walkthrough, you'll work with real data to understand how to merge two tables, one containing player names and the other their locations, into a comprehensive final table. You'll see how SQL LEFT JOINs preserve records from the first table, even when there's no matching data in the second. With practical examples, this tutorial simplifies LEFT JOINS for anyone looking to sharpen their SQL skills quickly and efficiently.
Watch on YouTube