SQL Subqueries in FROM Clause for Data Analysis

Learning Data Analytics the Right Way Series - Ep. 49 SQL for Data Analysis | Subqueries in the SELECT Clause 🟢 What if your query could figure out a value and use it right away? That's exactly what subqueries in the SELECT clause do. Today was one of those times when SQL seemed like real magic. A subquery in the SELECT clause is inside the SELECT statement. You can use it to do math or get values for each row in your result set on the fly. 🟢 Below is the query I built to find the top 3 products by sales percentage: SELECT p.ProductName, s. Sales, ROUND(s.Sales/(SELECT SUM(Sales) FROM sales),4)*100 AS sales_percentage FROM products p JOIN sales s ON s.ProductID=p.ProductID ORDER BY sales_percentage DESC LIMIT 3 🟢 The inner query sums up all the sales values in the sales table. The outer query then uses that value to calculate what percentage of total sales each product makes up. ROUND keeps the result neat. ORDER BY puts them in order from highest to lowest. LIMIT only pulls the top 3. A question. Calculations that change. Real business knowledge. 🟢 In the next episode, I will look at subqueries in the FROM clause. It keeps getting better! What business question would you ask this kind of question to get an answer? Leave a comment and share. #LearningDataAnalyticsTheRightWaySeries #DataAnalytics #SQL #LearningDataAnalytics #DataAnalyst #WithYouWithMe

𝗚𝗥𝗢𝗨𝗣 𝗕𝗬 𝗖𝗵𝗮𝗻𝗴𝗲𝗱 𝗛𝗼𝘄 𝗜 𝗜𝗻𝘁𝗲𝗿𝗽𝗿𝗲𝘁 𝗗𝗮𝘁𝗮 Earlier, I used to group data without thinking much. Just adding columns to GROUP BY because SQL required it. The query worked. But the insight didn’t. That’s when it clicked. 𝗚𝗿𝗼𝘂𝗽𝗶𝗻𝗴 𝗶𝘀 𝗻𝗼𝘁 𝗷𝘂𝘀𝘁 𝗮 𝘁𝗲𝗰𝗵𝗻𝗶𝗰𝗮𝗹 𝘀𝘁𝗲𝗽. 𝗜𝘁 𝗱𝗲𝗳𝗶𝗻𝗲𝘀 𝗵𝗼𝘄 𝘁𝗵𝗲 𝗯𝘂𝘀𝗶𝗻𝗲𝘀 𝘀𝗲𝗲𝘀 𝘁𝗵𝗲 𝗱𝗮𝘁𝗮. Before writing GROUP BY, I now ask: 𝗔𝘁 𝘄𝗵𝗮𝘁 𝗹𝗲𝘃𝗲𝗹 𝘀𝗵𝗼𝘂𝗹𝗱 𝘁𝗵𝗶𝘀 𝗱𝗲𝗰𝗶𝘀𝗶𝗼𝗻 𝗯𝗲 𝗺𝗮𝗱𝗲? For example, if the goal is to understand region-level performance: SELECT region, SUM(revenue) AS total_revenue FROM sales_transactions GROUP BY region; Earlier, I used to group by too many columns. That broke the summary and made the output too detailed to interpret. 𝗦𝗮𝗺𝗲 𝗱𝗮𝘁𝗮. 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝘁 𝗴𝗿𝗼𝘂𝗽𝗶𝗻𝗴. 𝗗𝗶𝗳𝗳𝗲𝗿𝗲𝗻𝘁 𝗯𝘂𝘀𝗶𝗻𝗲𝘀𝘀 𝘀𝘁𝗼𝗿𝘆. What changed for me: GROUP BY defines the level of analysis Every non-aggregated column in SELECT affects that grouping level Wrong grouping leads to misleading insights More columns ≠ better analysis “I use GROUP BY to control aggregation level so the output aligns with the business question.” That’s when SQL stopped being syntax and started becoming analysis. #SQL #DataAnalytics #DataAnalyst #BusinessAnalysis #AnalyticsThinking #LearnSQL #DataCareer

SQL Data Analytics Roadmap 📊 Mastering SQL for data analytics is about building skills step by step: Start with the basics (SELECT, WHERE, GROUP BY), then move into filtering and aggregations. Learn how to combine data using JOINs, and deepen your analysis with subqueries and transformations. To stand out, focus on analytical functions (RANK, ROW_NUMBER), performance optimization, and understanding how databases work. Key insight: SQL is not just querying data, it’s transforming raw data into insights that drive decisions. Consistency is what builds mastery. #SQL #DataAnalytics #DataScience #BusinessIntelligence #Analytics #LearningSQL #DataSkills

-Cracking the Pareto Principle with SQL I recently tackled this using Window Functions in SQL. Here is a breakdown of how to find the specific products that contribute to that top 80% of sales. -The Logic To solve this, we can't just look at individual sales. We need to: Aggregate sales by product. Sort them from highest to lowest. Calculate a Running Total to see the cumulative impact. Compare that total against 80% of the company's grand total. -SQL Query WITH product_sales AS ( -- Step 1: Get total sales per product SELECT product_id, SUM(sales) AS product_sales FROM orders GROUP BY product_id ), product_wise_running_sales AS ( -- Step 2: Calculate running total and the 80% threshold SELECT *, SUM(product_sales) OVER( ORDER BY product_sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_sales, 0.8 * SUM(product_sales) OVER() AS total_sales_threshold FROM product_sales ) -- Step 3: Filter for the heavy hitters SELECT * FROM product_wise_running_sales WHERE running_sales <= total_sales_threshold; -Result In this dataset, out of thousands of entries, only 413 products were responsible for hitting that 80% mark ($1,837,760.77). #SQL #DataAnalytics #DataScience #ParetoPrinciple #BusinessIntelligence #Database#Ankit Bansal #NamasteSQLNamasteSQL

🚀 Excited to share my latest project: Data Warehouse Analytics using SQL Continue part from building warehouse project to advanced analytics I’ve built a comprehensive repository focused on data exploration, analytics, and reporting using SQL. This project covers end-to-end analytical workflows and is designed to strengthen real-world data analysis skills. 🔍 Key Highlights: • Database exploration (tables, columns, categories) • Exploratory Data Analysis (EDA) • Aggregate & magnitude analysis (SUM, COUNT, AVG) • Ranking analysis using window functions (RANK, DENSE_RANK, ROW_NUMBER) • Time-series analysis (MoM, YoY trends, DATE functions) • Cumulative analysis (running totals, moving averages) • Performance benchmarking • Data segmentation using CASE & GROUP BY • Part-to-whole comparisons • Customer & Product analytics reports 💡 This project demonstrates how SQL can be used not just for querying data, but for deriving meaningful business insights. 📂 Check out the repository here: https://lnkd.in/gUrXXSFF I’d love to hear your feedback and suggestions! #SQL #DataAnalytics #DataEngineering #DataWarehouse #BusinessIntelligence #EDA #Analytics #GitHubProjects

Month over month growth is one of the most frequently requested metrics in any data organization and SQL's LAG window function makes calculating it clean, efficient, and scalable. One LAG expression. PARTITION BY for segments. A CTE to calculate once and reference multiple times for multiple metrics. A month spine to handle gaps. NULLIF to prevent division by zero. These five patterns cover virtually every MoM growth calculation you will ever need from a single metric for a single region to a full multi-dimensional growth report across dozens of segments and metrics. Master these patterns and MoM reporting goes from a multi-hour manual exercise to a five-minute query. Read the full post here: https://lnkd.in/ed6Sc27X #SQL #DataEngineering #DataAnalysis #Analytics #BusinessIntelligence #DataScience

📊 SQL Essentials Every Data Analyst Should Know SQL is one of the most powerful tools for working with data. From selecting the right columns to joining multiple tables and performing aggregations, mastering these core SQL commands is essential for turning raw data into meaningful insights. This quick SQL reference highlights some of the most commonly used operations—filtering data, grouping results, performing calculations, and using joins to combine datasets. For anyone starting their journey in data analytics, building a strong foundation in SQL is a must. 📌 𝗦𝗮𝘃𝗲 this post ♻️ 𝗥𝗲𝗽𝗼𝘀𝘁 𝗶𝗳 𝘁𝗵𝗶𝘀 𝘄𝗮𝘀 𝗵𝗲𝗹𝗽𝗳𝘂𝗹! 🔔 𝗙𝗼𝗹𝗹𝗼𝘄 Mohammad Imran Hasmey 𝗳𝗼𝗿 𝗺𝗼𝗿𝗲 𝗶𝗻𝘀𝗶𝗴𝗵𝘁𝘀 𝗼𝗻 𝗗𝗮𝘁𝗮 Science and Analytics #SQL #DataAnalytics #DataAnalysis #DataScience #LearningJourney #Analytics

Ever feel like you're writing overly complex SQL queries with multiple self-joins just to calculate a simple running total or period-over-period growth? 🤯 Enter SQL Window Functions. They are an absolute game-changer for advanced data analysis, allowing you to perform calculations across a set of table rows related to the current row—all without collapsing your dataset like a standard GROUP BY does. I've put together this visual cheat sheet to break down the 6 key categories you need to know: 1️⃣ Core Concepts: Mastering the OVER() clause, partitioning, and ordering. 2️⃣ Simple Ranking: Unique numbering and distribution (ROW_NUMBER, NTILE). 3️⃣ Advanced Ranking: Handling ties like a pro (RANK, DENSE_RANK). 4️⃣ Relative Position: Looking forward and backward in time (LEAD, LAG). 5️⃣ Boundary Values: Extracting the first or last touchpoints (FIRST_VALUE, LAST_VALUE). 6️⃣ Aggregate-as-Window: Building running totals and moving averages. Bookmark this post for your next data modeling task! 📌 Which window function do you find yourself reaching for the most? Let me know in the comments! 👇 #SQL #DataAnalytics #DataEngineering #DataScience #BusinessIntelligence #TechTips #DataCommunity

DAY 18 Understanding Data Questions: The Real Skill Behind SQL Anyone can learn SQL syntax, but the real magic starts before you even touch the keyboard. Understanding what the data question is really asking is half the battle. Is it about trends, comparisons, or anomalies? Are we summarizing individual records or aggregated patterns? Do we need a single metric or a story from multiple joined tables? Once you truly understand the question, you can pick the right SQL tool for the job: GROUP BY + aggregates for summaries and KPIs JOINs to connect relationships across datasets CASE WHEN for conditional logic WHERE for filtering rows based on condition The stronger your grasp of data logic, the more powerful your SQL becomes. It’s not just about writing queries it’s about turning questions into insights. #DataAnalytics #SQL #DataAnalysis #BusinessIntelligence #DataThinking

𝗛𝗔𝗩𝗜𝗡𝗚 𝗜𝘀𝗻’𝘁 𝗝𝘂𝘀𝘁 𝗔 𝗙𝗶𝗹𝘁𝗲𝗿 𝗜𝘁 𝗗𝗲𝗰𝗶𝗱𝗲𝘀 𝗪𝗵𝗮𝘁 𝗦𝘁𝗮𝗻𝗱𝘀 𝗜𝗻𝘀𝗶𝗱𝗲 𝘁𝗵𝗲 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 When I started working with SQL, I kept mixing up WHERE and HAVING. And honestly, my queries worked… but my logic didn’t. I would try things like filtering aggregated results using WHERE. It failed. Not because SQL is strict. But because I didn’t understand the sequence. Then it became clear: 👉 WHERE filters raw data 👉 HAVING filters aggregated results Let’s take a simple business need: “Find regions that are actually performing well.” First, we prepare the data: SELECT region, SUM(revenue) AS total_revenue FROM sales_transactions WHERE status = 'Completed' GROUP BY region; Now we have grouped performance. But not every region is worth focusing on. So we apply the real filter: HAVING SUM(revenue) > 100000; Now the output changes completely. Instead of noise, we get: Only meaningful regions Only high-performing segments Only actionable insights 𝗪𝗵𝗮𝘁 𝗜 𝗹𝗲𝗮𝗿𝗻𝗲𝗱 𝘁𝗵𝗲 𝗵𝗮𝗿𝗱 𝘄𝗮𝘆: WHERE works on raw rows HAVING works on grouped results Filtering at the wrong stage breaks the logic Not everything in the dataset deserves to be analyzed “I use HAVING to filter aggregated results after grouping, ensuring the output only includes meaningful business segments.” This is where SQL stops being syntax… and starts becoming decision-making. #SQL #DataAnalytics #DataAnalyst #BusinessAnalysis #AnalyticsThinking #LearnSQL #DataCareer