Unlock SQL Productivity with Window Functions

JOINS in SQL combine rows from two or more tables based on a related column : Inner join, Left join, Right Join, Full Join, Self Join, Cross join. Inner Join: Return only rows where there's an match in both tables. SELECT users.name, orders.product FROM users INNER JOIN orders ON users.id = orders.user_id; Left Join: Return all rows from left table, plus matching rows from the right table. Non matching right rows shows NULL. SELECT users.name, orders.product FROM users LEFT JOIN orders ON users.id = orders.user_id; Right Join: Return all rows from right table, plus matching rows from the left table. Full Join: Return all rows from both tables, matching where possible and filling NULL where no match exists. Cross Join: Return Cartesian product every row from table A paired with every row from table B. SELECT colors.color, sizes.size FROM colors CROSS JOIN sizes; Self Join: Joining a table to itself useful for hierarchical data. SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Most analysts use SQL every day — but skip the one feature that makes complex analysis 10x faster. I'm talking about window functions. For the longest time, I relied on heavy GROUP BY queries and multiple subqueries to answer simple business questions. Window functions changed everything. Here's the difference in plain English: A regular aggregation collapses your rows. A window function looks across rows — without losing the row-level detail. Think of it like this: You want each employee's salary AND the average salary of their department — in the same row. GROUP BY can't do that. A window function does it in one line. 3 window functions worth mastering first: ▸ ROW_NUMBER() — rank records within a group (e.g., latest order per customer) ▸ LAG() / LEAD() — compare current vs. previous rows (e.g., month-over-month growth) ▸ SUM() OVER() — running totals without collapsing your data Once you understand PARTITION BY and ORDER BY inside the OVER() clause — you unlock a completely new level of SQL thinking. The syntax looks intimidating. The logic is actually intuitive. Which window function do you use the most — or wish you'd learned sooner? Drop it below 👇 #SQL #DataAnalytics #DataEngineering #CareerGrowth #TechSkills

🚀 Day 2 of #10DaysOfSQL Continuing my SQL revision journey, today I focused on Aggregation & Grouping—turning raw data into meaningful insights. 🔹 COUNT() → counts records 🔹 SUM() → calculates total values 🔹 AVG() → finds averages 🔹 MIN() / MAX() → identifies lowest & highest values 🔹 GROUP BY → groups data into categories 🔹 HAVING → filters grouped data 📌 Example 1 (Total records): SELECT COUNT(*) AS total_customers FROM customers; 📌 Example 2 (Average salary by department): SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; 📌 Example 3 (Total sales per region): SELECT region, SUM(sales) AS total_sales FROM orders GROUP BY region; 📌 Example 4 (Filtering grouped data using HAVING): SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5; 📌 Example 5 (Finding min & max values): SELECT MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary FROM employees; 💡 Realization: Aggregation functions helped me understand how data can be summarized to uncover patterns. Using GROUP BY and HAVING makes analysis much more powerful than just viewing individual rows. 💬 What’s your most frequently used aggregation function in SQL? #SQL #DataAnalytics #LearningJourney #TechSkills

Hot take: 90% of analysts overcomplicate their SQL. Here are the 8 functions that cover 80% of real analytics work: 1️⃣ ROW_NUMBER() : rank rows within a group → Use it: find the latest record per customer 2️⃣ LAG() / LEAD() : compare current row to previous or next → Use it: month-over-month change without a self-join 3️⃣ SUM() OVER() : running totals without collapsing rows → Use it: cumulative revenue that still shows each day 4️⃣ CASE WHEN : conditional logic inline → Use it: segment customers by behaviour in one query 5️⃣ DATE_TRUNC() : truncate timestamps to week, month, quarter → Use it: group daily data into monthly trends instantly 6️⃣ COALESCE() : replace NULLs with a fallback value → Use it: clean up messy source data before aggregating 7️⃣ COUNT(DISTINCT) : unique counts, not total rows → Use it: actual active users, not just session counts 8️⃣ WITH (CTE) : readable, reusable query logic → Use it: break a 200-line monster into human-readable steps Most dashboards I've built in 9 years? These 8 functions did the heavy lifting. Save this. Your future Monday-morning self will thank you. Which one do you use most and which one took you longest to actually get? #SQL #DataAnalytics #BI #DataAnalyst #Analytics

Window functions are powerful, but a lot of the “magic” in SQL comes from even simpler, everyday tools that most people under‑use. Today’s small topic: HAVING vs WHERE and when to push filters after aggregation. I used to think “if it’s a filter, it goes in WHERE.” Then I realized that WHERE filters before grouping, while HAVING filters after it. That one realization changed how I write aggregations and clean up dashboards. -- WHERE filters BEFORE grouping SELECT country, COUNT(*) AS total_sales FROM orders WHERE order_date > '2025-01-01' -- limited to recent orders GROUP BY country; -- HAVING filters AFTER grouping SELECT country, COUNT(*) AS total_sales FROM orders GROUP BY country HAVING COUNT(*) > 100; -- only countries with >100 orders WHERE is your pre‑qualifier: it reduces the raw data before the database does the heavy work. HAVING is your post‑processor: it strips out groups that don’t meet your business rules - like only showing regions with meaningful volume or filtering out low‑activity segments. Pairing smart WHERE filters with selective HAVING conditions also makes queries faster and more readable. You prune the data early, then enrich with aggregations, then clean up the result - not the other way around. In one line: WHERE refines the rows. HAVING refines the groups. That’s when SQL stopped being “count and filter later” and started feeling like a structured pipeline. #SQL #DataEngineering #LearningInPublic

For weeks, SQL JOINs felt like a personal enemy 🥹 I stared at those Venn diagrams until my eyes blurred. Inner? Left? Full? It all felt very overwhelming. I’d write a query, get 0 rows and have no idea why. I almost convinced myself I wasn't "technical enough." Then, I stopped looking at diagrams and started looking at the data. I realized a JOIN isn't a math problem. It’s just a conversation between two tables. "Hey Table A, do you have any info on customer 001?" "Table B says yes, here is their order." Once I started "talking" to my data, it clicked. However, this didn't happen overnight. I practiced oftenly. If you're struggling with JOINs today, don't quit. I see you. I was you. Let me break it down for you. Think of SQL JOINs as a way of combining data from two or more tables based on a related column (like a common ID). Types of JOINS  INNER JOIN What it does: Returns only matching records from both tables.  LEFT JOIN (LEFT OUTER JOIN) What it does: Returns all records from the left table + matching ones from the right. You might be wondering which is the left table (just like I did for the longest). It's the table that appears immediately after the FROM statement. RIGHT JOIN (RIGHT OUTER JOIN) What it does: Returns all records from the right table + matching from the left. The right table appears after the JOIN statement. FULL JOIN (FULL OUTER JOIN) What it does: Returns all records from both tables  SELF JOIN What it does: Joins a table to itself Today, I joined orders table and customers table using an INNER JOIN. The orders table contains customer id numbers, but not the actual names of the customers. The INNER JOIN finds matches between the customer id in the orders table and the customer id in the customers table. #DataAnalytics #Datascience #SQL #JOINS #Techcommunity #BuildinginPublic

🚀 Day 34/100 — CASE Statements: Adding Logic to SQL ⚡ Today I learned how to use CASE statements in SQL, which help apply conditional logic directly inside queries. 📊 What is a CASE statement? 👉 Similar to IF-ELSE logic in programming 👉 Used to create new columns or categorize data 📌 What I explored today: 🔹 Conditional logic in SQL 🔹 Creating new categories 🔹 Using CASE with SELECT 🔹 Combining with GROUP BY 💻 Example Scenario: 👉 Categorize customers based on spending 📌 Example Query: SELECT customer_id, order_amount, CASE WHEN order_amount > 1000 THEN 'High Value' WHEN order_amount BETWEEN 500 AND 1000 THEN 'Medium Value' ELSE 'Low Value' END AS customer_category FROM orders; 🔥 Key Learnings: 💡 CASE adds decision-making power to SQL 💡 Helps in data categorization & reporting 💡 Very useful for dashboards & business insights 🚀 Real-world use cases: ✔ Customer segmentation ✔ Sales performance classification ✔ Risk analysis 🔥 Pro Tip: 👉 Use CASE with GROUP BY to create powerful summaries 📊 Tools Used: SQL | MySQL ✅ Day 34 complete. 👉 Quick question: Where would you use CASE — data cleaning or reporting? 🤔 #Day34 #100DaysOfData #SQL #CaseStatement #DataAnalytics #LearningInPublic #CareerGrowth #JobReady #InterviewPrep

𝗠𝗮𝗻𝘆 𝗦𝗤𝗟 𝗯𝗲𝗴𝗶𝗻𝗻𝗲𝗿𝘀 𝗰𝗼𝗻𝗳𝘂𝘀𝗲 𝘁𝗵𝗲𝘀𝗲 𝘁𝘄𝗼 𝗰𝗹𝗮𝘂𝘀𝗲𝘀. 𝗕𝘂𝘁 𝘂𝘀𝗶𝗻𝗴 𝘁𝗵𝗲 𝘄𝗿𝗼𝗻𝗴 𝗼𝗻𝗲 𝗰𝗮𝗻 𝗰𝗵𝗮𝗻𝗴𝗲 𝘆𝗼𝘂𝗿 𝗿𝗲𝘀𝘂𝗹𝘁𝘀. One of the most common SQL questions is: When should you use WHERE and when should you use HAVING? At first glance, both look similar because they filter data. But the key difference is when the filtering happens. WHERE Filters rows before aggregation. Example: SELECT product, SUM(revenue) AS total_revenue FROM sales WHERE revenue > 500 GROUP BY product; Here, SQL first removes rows where revenue ≤ 500, then performs the aggregation. HAVING Filters groups after aggregation. Example: SELECT product, SUM(revenue) AS total_revenue FROM sales GROUP BY product HAVING SUM(revenue) > 500; Here, SQL first calculates total revenue per product, then removes groups that don’t meet the condition. 💡 Simple way to remember WHERE → filters rows HAVING → filters aggregated groups Understanding this difference is essential when working with GROUP BY and aggregate functions. Small SQL concepts like this make a big difference in real data analysis. Curious to know 👇 Which clause confused you more when you first learned SQL — WHERE or HAVING? #SQL #DataAnalytics #LearningInPublic #SQLTips #DataAnalyticsJourney

🚀 SQL Practice — Finding First Order Per Customer Today I worked on a very practical SQL problem: 👉 How to find the first order for each customer Here are two different approaches I explored: 🔹 Approach 1: Using ROW_NUMBER() (Window Function) WITH RankedOrders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY dateTime ASC) as rn FROM samples.bakehouse.sales_transactions ) SELECT * FROM RankedOrders WHERE rn = 1; 🔹 Approach 2: Using Aggregation SELECT customerID, MIN(dateTime) AS first_order_date FROM samples.bakehouse.sales_transactions GROUP BY customerID; 💡 Key Learnings: ROW_NUMBER() gives full row details (more flexible) MIN() is simpler but only returns the date Window functions are powerful when you need complete records, not just aggregates 📌 Real-world use cases: Customer onboarding analysis First purchase tracking Retention & cohort analysis Small steps every day toward mastering SQL for Data Engineering 💪 #SQL #DataEngineering #WindowFunctions #LearningInPublic #Analytics #DataSkills

STOP writing 3 SQL queries for 1 result. There's a single clause that does it all. 𝗚𝗥𝗢𝗨𝗣 𝗕𝗬 𝗥𝗢𝗟𝗟𝗨𝗣 Ever needed totals at multiple levels of aggregation? And a grand total? All in one query? That's exactly what 𝗚𝗥𝗢𝗨𝗣 𝗕𝗬 𝗥𝗢𝗟𝗟𝗨𝗣 does. Take a SALES table with products and regions. With GROUP BY ROLLUP(region, product) you get: ⇒ Total sales by region + product ⇒ Total sales by region ⇒ The Grand Total No messy UNIONs. No multiple queries. Multi-level aggregation in one shot. GROUP BY ROLLUP + the columns. That's it. It feels like cheating🫡 --------------------------------------------------------------- 𝟭𝟬𝟬 𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 + 𝟯𝟬𝟬 𝗣𝗿𝗮𝗰𝘁𝗶𝗰𝗲 𝗘𝘅𝗮𝗺𝗽𝗹𝗲𝘀 + 𝗡𝗼𝘁𝗲𝘀 𝟭𝟬𝟬 𝗘𝘅𝗰𝗲𝗹 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 + 𝗡𝗼𝘁𝗲𝘀 + 𝗙𝗼𝗿𝗺𝘂𝗹𝗮 𝗦𝗵𝗲𝗲𝘁 𝟭𝟱𝟬 𝗣𝘆𝘁𝗵𝗼𝗻 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 (𝗡𝘂𝗺𝗣𝘆 + 𝗣𝗮𝗻𝗱𝗮𝘀 + 𝗠𝗮𝘁𝗽𝗹𝗼𝘁𝗹𝗶𝗯) 𝟭𝟬𝟬 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 + 𝗗𝗔𝗫 𝗖𝗵𝗲𝗮𝘁 𝗦𝗵𝗲𝗲𝘁 + 𝗡𝗼𝘁𝗲𝘀 𝟭𝟬𝟬 𝗧𝗼𝗽 𝗛𝗥 𝗥𝗼𝘂𝗻𝗱 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 𝟭𝟬𝟬 𝗦𝘁𝗮𝘁𝗶𝘀𝘁𝗶𝗰𝘀 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗤&𝗔 + 𝗡𝗼𝘁𝗲𝘀 𝗥𝗲𝘀𝘂𝗺𝗲 𝗚𝘂𝗶𝗱𝗲 + 𝟳𝟬𝟬 𝗖𝗼𝗺𝗽𝗮𝗻𝘆 𝗦𝗶𝘁𝗲𝘀 𝗚𝗲𝘁 𝗔𝗰𝗰𝗲𝘀𝘀 𝗛𝗲𝗿𝗲: https://lnkd.in/dyBfCTjK #dataanalytics #data #powerbi #sql #mysql #excel