3 SQL queries to Master Data analysis

Key Takeaways

What if I told you there are only three SQL queries you need to master to analyze any data set like a pro. Let's start with the first one. Window functions. Think of them as a secret weapon for analyst who want insights within groups. For example, highest paid employee in each department. Here's how it looks. For every department, we are ranking employees based on salary. But let me break that down a bit more so it's crystal clear. When you write partition by department, you are basically telling SQL to treat each department like a separate mini table. HR becomes its own group. Finance becomes its own group and so on. Inside each group, SQL applies order by salary descending. That means it sorts the employees by salary from highest to lowest within that department. Now rank kicks in. It assigns rank one to the highest salary inside that group, rank two to the next and so on. If two people have the same salary, they both get the same rank. So if HR has 10 people, it gives rank one to the highest paid person in HR, not in the whole company. This one line is doing all this grouping, sorting and ranking at the same time. And if you want to go deeper into window functions and actually master them with real world examples, I have written a book called Mastering SQL Window Functions. I kept it practical, beginner friendly and focused on the exact problem analysts face every day. If you're serious about leveling up your analysis skills, this book will save you months of trial and error. Now, the second query every analyst should know, the CTE or common table expression. It helps you break complex analysis into simple layers. Imagine you want to compare each employees salary with their department's average. Here is how we do it. In the first step, we find the average salary per department. But here is what that really means. The CTE DP average runs first. It groups the data by department ID and calculates one number per group, the department's average salary. So if you have five departments, you end up with five rows. Then in the main query, we join the result back to the employees table. Now every employee gets matched with their department's average salary. Once that link is made, the comparison becomes simple. Check if the employee salary is above or below the average for their own department. This is how real analyst think. Break the logic, reuse the result and keep the query readable. And if you want a deeper breakdown of how CDs work, I have already made a full video on it where I explain it with simple visuals. I will leave the link on the screen and in the description. Watch it whenever you want to master CDEs properly. Finally, the third query advanced joins with filters. Because in real world projects, your data is rarely in one table. Let's say you want to find customers who spent more than 50,000 in the last 3 months. We are joining two tables filtering orders from the last 3 months right inside the on clause and then using having to keep only high value customers. But here is what actually happening behind the scenes. The join connects each customer with their orders and this condition makes sure we only pick recent orders. Everything older than 3 month is ignored before the aggregation even happens. Then SQL groups the data by customer and calculates the total sales for each person. Finally, the having clause filters out anyone whose total doesn't cross 50,000. So you are cleaning the data, matching it, and summarizing it in one clean query. So if you master these three window functions, CDs and smart joys, you can handle almost any SQL analytics problem thrown at you. If you want me to make a part two where I show you how to combine all three in one powerful query, comment part two below. And don't forget to follow for more SQL insights like this. And if you want to learn SQL with me directly, my live training batch is starting soon. Seats are limited. So I'm keeping the enrollment link in the description. Fill it out and if you get selected, my team will reach out to you. Thanks for watching. This is Manish from rebellionrider.com.