Mastering SQL Self Joins for Data Analysis

🚀 Mastering Window Functions in SQL (Quick Notes): If you're preparing for Data Engineering or Analytics roles, window functions are a must-know concept. Here’s a quick breakdown 👇 🔹 What are Window Functions? Window functions perform calculations across a set of rows related to the current row without collapsing the result (unlike GROUP BY). ----------------------------------------------------------------- 🔹 Key Components • PARTITION BY → divides data into groups • ORDER BY → defines row sequence within partition • OVER() → defines the window scope ----------------------------------------------------------------- 🔹 Most Common Window Functions 1️⃣ ROW_NUMBER() Assigns unique row numbers Example: Find top records per group 2️⃣ RANK() Same rank for duplicates, skips next rank Example: Ranking with gaps 3️⃣ DENSE_RANK() Same rank for duplicates, no gaps Example: Continuous ranking 4️⃣ LAG() / LEAD() Access previous/next row values Example: Compare current vs previous data 5️⃣ SUM() / AVG() OVER() Running totals and moving averages ----------------------------------------------------------------- 🔹 Example 👉 Same rank for duplicates, skips next rank: SELECT emp_name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank FROM employees; 👉 Get previous row value: SELECT emp_name, salary, LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_salary FROM employees; ----------------------------------------------------------------- 🔹 Why Window Functions? • Avoid complex subqueries • Perform row-level + aggregated analysis together • Essential for real-world ETL & analytics ----------------------------------------------------------------- 🔹 Real Use Cases ✔ Top N per group ✔ Running totals ✔ Duplicate detection ✔ Time-based comparisons ----------------------------------------------------------------- 💡 Pro Tip: Always clarify PARTITION and ORDER logic — that’s where most mistakes happen. #SQL #DataEngineering #Analytics #WindowFunctions #LearningJourney

💬 SQL Challenge of the Day Problem: You are given a table named "sales_data" with the following columns: - order_id (unique identifier for each order) - order_date (date of the order) - revenue (amount of revenue generated by the order) Write a SQL query to calculate the total revenue for each month, along with the running total revenue for each month in descending order of total revenue. Query: ```sql WITH monthly_revenue AS ( SELECT DATE_TRUNC('month', order_date) AS month_start, SUM(revenue) AS total_revenue, ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', order_date) ORDER BY order_date) AS rn FROM sales_data GROUP BY DATE_TRUNC('month', order_date) ) SELECT month_start, total_revenue, SUM(total_revenue) OVER (ORDER BY month_start ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM monthly_revenue ORDER BY total_revenue DESC; ``` Answer: The above query will calculate the total revenue for each month and provide the running total revenue for each month in descending order of total revenue. Explanation: - The query first calculates the total revenue for each month using the `DATE_TRUNC` function to group by month. - It then uses a window function `ROW_NUMBER` to assign a row number within each month based on the order date. - Finally, the query calculates the running total revenue using the `SUM` window function over the ordered months. Example: Consider the "sales_data" table: | order_id | order_date | revenue | |----------|------------|---------| | 1 | 2022-01-05 | 100 | | 2 | 2022-01-15 | 150 | | 3 | 2022-02-10 | 200 | | 4 | 2022-02-20 | 180 | The query will output: | month_start | total_revenue | running_total | |-------------|---------------|---------------| | 2022-01-01 | 250 | 250 | | 2022-02-01 | 380 | 630 | #Hashtags #PowerBIChallenge #PowerInterview #LearnPowerBi #LearnSQL #TechJobs #DataAnalytics #DataScience #BigData #DataAnalyst #MachineLearning #Python #SQL #Tableau #DataVisualization #DataEngineering #ArtificialIntelligence #CloudComputing #BusinessIntelligence #Data

💬 SQL Challenge of the Day Problem: Given a table named "sales" with the following columns: - order_id (unique identifier for each order) - order_date (date of the order) - revenue (revenue generated by the order) Write a SQL query to calculate the 30-day running total revenue for each order, considering the current order and the revenue from the previous 29 days. Query: ```sql SELECT order_id, order_date, revenue, SUM(revenue) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS running_total FROM sales ``` Answer: The SQL query calculates the 30-day running total revenue for each order by using a window function to sum the revenue of the current order and the previous 29 days. Explanation: - The query uses the SUM() window function with the OVER clause to calculate the running total. - The PARTITION BY clause is not needed as we want the running total for all orders combined. - The ORDER BY clause orders the rows by the order_date. - The ROWS BETWEEN 29 PRECEDING AND CURRENT ROW specifies the window frame to consider for the running total calculation. Example: Consider the "sales" table: | order_id | order_date | revenue | |----------|------------|---------| | 1 | 2021-01-01 | 100 | | 2 | 2021-01-05 | 150 | | 3 | 2021-01-10 | 200 | | 4 | 2021-01-15 | 120 | The output of the query would be: | order_id | order_date | revenue | running_total | |----------|------------|---------|---------------| | 1 | 2021-01-01 | 100 | 100 | | 2 | 2021-01-05 | 150 | 250 | | 3 | 2021-01-10 | 200 | 450 | | 4 | 2021-01-15 | 120 | 570 | #Hashtags #PowerBIChallenge #PowerInterview #LearnPowerBi #LearnSQL #TechJobs #DataAnalytics #DataScience #BigData #DataAnalyst #MachineLearning #Python #SQL #Tableau #DataVisualization #DataEngineering #ArtificialIntelligence #CloudComputing #BusinessIntelligence #Data

💬 SQL Challenge of the Day Problem: You are given a table named "sales_data" with the following columns: - order_id: unique identifier for each order - order_date: date when the order was placed - product_id: unique identifier for each product - quantity: the quantity of the product ordered - revenue: the revenue generated from the order Write a SQL query to calculate the running total revenue for each day, considering all previous days. The result should include all columns from the original table plus an additional column for the running total revenue. Query: ```sql SELECT order_id, order_date, product_id, quantity, revenue, SUM(revenue) OVER (PARTITION BY order_date ORDER BY order_id) AS running_total_revenue FROM sales_data ORDER BY order_date, order_id; ``` Answer: The SQL query provided will calculate the running total revenue for each day, considering all previous days. Explanation: - The query uses a window function `SUM()` with `PARTITION BY order_date` to calculate the running total revenue for each day. - The `ORDER BY order_id` within the window function ensures that the running total is calculated based on the order of the orders. Example: Consider the "sales_data" table: | order_id | order_date | product_id | quantity | revenue | |----------|------------|------------|----------|---------| | 1 | 2022-01-01 | A | 2 | 100 | | 2 | 2022-01-01 | B | 1 | 50 | | 3 | 2022-01-02 | A | 1 | 60 | | 4 | 2022-01-03 | C | 3 | 120 | The output of the query will be: | order_id | order_date | product_id | quantity | revenue | running_total_revenue | |----------|------------|------------|----------|---------|-----------------------| | 1 | 2022-01-01 | A | 2 | 100 | 100 | | 2 | 2022-01-01 | B | 1 | 50 | 150 | | 3 | 2022-01-02 | A | 1 | 60 | 60 | | 4 | 2022-01-03 | C | 3 | 120 | 120 | #Hashtags #PowerBIChallenge #PowerInterview #LearnPowerBi #LearnSQL #TechJobs #DataAnalytics #DataScience #BigData #DataAnalyst #MachineLearning #Python #SQL #Tableau #DataVisualization #DataEngineering #ArtificialIntelligence #CloudComputing #BusinessIntelligence #Data

*✅ Complete Roadmap to Learn SQL (Structured Query Language) 🧠💻* *Week 1: SQL Basics* - What is SQL and how databases work - Install MySQL Workbench or PostgreSQL - Learn SELECT, FROM, WHERE - Filtering data with conditions - Practice basic queries Example: Fetch all employees, filter salary > 50k *Week 2: Sorting and Aggregation* - ORDER BY (sorting data) - Aggregate functions: COUNT, SUM, AVG, MIN, MAX - GROUP BY concept - HAVING clause Example: Department-wise average salary *Week 3: Joins (Most Important 🔥)* - INNER JOIN - LEFT JOIN, RIGHT JOIN - FULL JOIN - Self Join Example: Combine employees and departments tables *Week 4: Advanced Filtering* - IN, BETWEEN, LIKE - Wildcards (% , _) - NULL handling (IS NULL, IS NOT NULL) - CASE statements Example: Categorize customers based on spending *Week 5: Subqueries* - Nested queries - Correlated subqueries - Using subqueries in SELECT, WHERE Example: Find employees earning above average salary *Week 6: Window Functions (High Value 💰)* - OVER() clause - ROW_NUMBER(), RANK(), DENSE_RANK() - PARTITION BY Example: Rank employees by salary within each department *Week 7: CTE & Views* - Common Table Expressions (WITH) - Temporary vs permanent views - Simplify complex queries Example: Multi-step data transformation *Week 8: Data Modification* - INSERT, UPDATE, DELETE - TRUNCATE vs DELETE - Constraints (PRIMARY KEY, FOREIGN KEY) Example: Update employee salary *Week 9: Indexing & Performance* - What are indexes - Query optimization basics - EXPLAIN keyword Example: Speed up large table queries *Week 10: Working with Real Data* - Import CSV data - Data cleaning in SQL - Handling duplicates - Basic transformations Example: Clean messy sales dataset *Week 11: Mini Projects* - Write complex queries - Solve real-world case studies - Focus on business logic Examples: Sales dashboard queries, Customer segmentation *Week 12: Final Preparation* - Revise all concepts - Practice interview questions - Solve SQL challenges on LeetCode / HackerRank - Mock interviews *Daily Rule for You* - Practice SQL 60 minutes daily - Solve 5 queries daily - Revise previous queries weekly *🔥 Pro Tip* - Focus more on JOINS + WINDOW FUNCTIONS - Practice real datasets, not just theory - Think in terms of “business questions” #learningjourny #dataanalysis

To handle 80% of real-world SQL querying, you don’t need everything—you need to master a strong core. Here’s the high-impact SQL skill stack that will cover most use cases: -------------------------------------------------------------------------------- 🔑 1. SELECT + FROM (FOUNDATION) * Pull data from tables * Choose specific columns SELECT name, age FROM users; -------------------------------------------------------------------------------- 🔍 2. WHERE (FILTERING DATA) * Filter rows using conditions * Use operators: =, !=, >, <, LIKE, IN, BETWEEN SELECT * FROM orders WHERE amount > 100; -------------------------------------------------------------------------------- 🔗 3. JOINS (MOST IMPORTANT 🔥) * Combine data from multiple tables Must know: * INNER JOIN * LEFT JOIN SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id; -------------------------------------------------------------------------------- 📊 4. GROUP BY + AGGREGATIONS * Summarize data Functions to know: * COUNT() * SUM() * AVG() * MIN() * MAX() SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id; -------------------------------------------------------------------------------- 🚦 5. HAVING (FILTER AGGREGATES) * Filter grouped results SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id HAVING total > 500; -------------------------------------------------------------------------------- 🔃 6. ORDER BY (SORTING) * Sort results SELECT * FROM orders ORDER BY amount DESC; -------------------------------------------------------------------------------- 🔢 7. LIMIT (TOP RESULTS) * Get top N rows SELECT * FROM orders ORDER BY amount DESC LIMIT 10; -------------------------------------------------------------------------------- 🧠 8. CASE (CONDITIONAL LOGIC) * Add logic inside queries SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS status FROM users; -------------------------------------------------------------------------------- 🔁 9. SUBQUERIES (BASIC) * Query inside a query SELECT name FROM users WHERE id IN ( SELECT user_id FROM orders ); -------------------------------------------------------------------------------- ⚡ 10. WINDOW FUNCTIONS (HIGH VALUE) * Advanced but extremely useful Must know: * ROW_NUMBER() * RANK() * PARTITION BY SELECT name, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank FROM orders; -------------------------------------------------------------------------------- 🎯 IF YOU LEARN ONLY THIS… You’ll handle: * Dashboards * Data analysis * ETL pipelines * Interview questions * 80% of business queries -------------------------------------------------------------------------------- 🧠 SIMPLE RULE TO REMEMBER * SELECT + WHERE → filter * JOIN → combine * GROUP BY → summarize * WINDOW → analyze deeper

🚀 I used to write complex SQL queries… until Window Functions changed everything. There was a time I built a sales dashboard where comparing daily performance meant multiple joins, subqueries, and unnecessary complexity. It worked—but it wasn’t elegant. Then I discovered SQL Window Functions—and everything became simpler, faster, and more intuitive. 💡 What are SQL Window Functions? Window Functions allow you to perform calculations across a set of rows related to the current row—without collapsing the data like GROUP BY. 👉 In simple terms: You get aggregated insights + row-level detail in the same result. That’s what makes them incredibly powerful for analytics. 🔧 Practical Tips & Use Cases Here are some window functions I use almost daily: 🔹 ROW_NUMBER() Assigns a unique number to each row 👉 Use case: Deduplicate records or pick latest transactions 🔹 RANK() vs DENSE_RANK() RANK() skips numbers on ties DENSE_RANK() doesn’t 👉 Use case: Ranking top customers or products 🔹 LAG() & LEAD() Access previous or next row values 👉 Use case: Compare today’s sales with yesterday’s 🔹 PARTITION BY Breaks data into groups (like GROUP BY, but without aggregation) 👉 Use case: Analyze performance per region, customer, or product 🔹 Running Totals with SUM() OVER() 👉 Use case: Track cumulative revenue over time 📊 Real-World Example Let’s say you want to calculate daily sales + cumulative revenue + previous day comparison: SELECT order_date, daily_sales, SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales, LAG(daily_sales) OVER (ORDER BY order_date) AS prev_day_sales FROM sales; 👉 In one query, you get: Daily performance Running total Day-over-day comparison No joins. No subqueries. Just clean logic. 🧠 Personal Insight One of my biggest “aha” moments as an analyst was realizing: 👉 Good SQL isn’t just about getting results—it’s about writing queries that are easy to understand and scale. Window functions helped me: Reduce query complexity Improve performance Make dashboards more dynamic 🎯 Final Thought If you’re still overusing subqueries or complex joins for analytical problems… it’s time to start thinking in windows, not blocks. 💬 Which SQL window function do you use the most in your daily work? #SQL #DataAnalytics #BusinessIntelligence #PowerBI #DataTips

Day 7/30 — 5 SQL queries I use every single day as a Data Analyst. Not textbook queries. The ones I actually run. On real data. Every week. Save this. You'll need it. 🔖 1️⃣ Finding duplicates instantly sql SELECT customer_id, COUNT(*) as count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 ORDER BY count DESC; Before building ANY dashboard — this is query #1. Duplicates in source data = wrong KPIs. Always check. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2️⃣ Running totals with window functions sql SELECT order_date, revenue, SUM(revenue) OVER (ORDER BY order_date) AS running_total FROM sales; No subquery. No JOIN. One clean line. Stakeholders love a running total visual. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 3️⃣ Month-over-month comparison sql SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS MoM_growth FROM monthly_sales; This one query powers half my Power BI KPI cards. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 4️⃣ Replacing NULLs without breaking aggregations sql SELECT COALESCE(region, 'Unknown') AS region, COALESCE(revenue, 0) AS revenue FROM sales_data; NULL values silently break SUM and AVG. COALESCE is your safety net. Use it everywhere. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 5️⃣ Top N per category sql SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank FROM sales ) ranked WHERE rank <= 3; Top 3 products per region. Top 5 customers per segment. This pattern works for everything. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ These 5 queries cover 80% of real analyst work. Master these before memorising anything else. 💪 Day 7 of my #7DaysOfData series. One practical insight every day — follow along 🔔 💬 Which of these do you use most? Or is there a query you'd add to this list? Drop it below 👇 #30DaysOfData #SQL #DataAnalytics #DataAnalyst #SQLtips #DataEngineering #WindowFunctions #BIwithPankhuri

🚀 Advanced SQL Concepts That Elevate Your Data Analysis Game Here are some advanced concepts that have significantly improved how I analyze data: 🔹 HAVING Clause (Filtering Aggregated Data) Unlike WHERE, which filters rows, HAVING filters grouped results. SELECT region, SUM(revenue) AS total_revenue FROM sales GROUP BY region HAVING SUM(revenue) > 5000; 🔹 IF Statements (Conditional Logic in SQL) Useful for creating conditional outputs (varies by SQL dialect). SELECT IF(profit > 0, 'Profit', 'Loss') AS status FROM sales; 🔹 CASE Statements (More Flexible Conditional Logic) A powerful way to categorize data based on conditions. SELECT CASE WHEN discount < 0.05 THEN 'Low' WHEN discount BETWEEN 0.05 AND 0.1 THEN 'Medium' ELSE 'High' END AS discount_category FROM sales; 🔹 NULL Handling (Dealing with Missing Data) Handling NULLs correctly is critical in real-world datasets. SELECT COALESCE(profit, 0) AS profit FROM sales; 🔹 Nested Queries / Subqueries (Query Inside a Query) Used when you need results from another query to filter or transform data. SELECT * FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); 🔹 Subquery Using IN Clause Helpful when filtering based on a list generated dynamically. SELECT * FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE region = 'East' ); 🔹 Subquery as a Table (Derived Table) Treat subqueries like temporary tables for further analysis. SELECT region, avg_revenue FROM ( SELECT region, AVG(revenue) AS avg_revenue FROM sales GROUP BY region ) t WHERE avg_revenue > 1000; 💡 Key Insight: SQL isn’t just about querying data—it’s about thinking logically. The better you structure conditions and subqueries, the more powerful and efficient your analysis becomes. From my experience, mastering these concepts is what transitions you from writing queries → to solving business problems. Which of these SQL concepts do you find most powerful in your day-to-day work? 👇 #DataAnalytics #SQL #AdvancedSQL #DataAnalyst #LearningSQL #DataSkills #AnalyticsJourney #SQLTips #CareerGrowth #DataCommunity #frontlinesedutech #flm #frontlinesmedia Upendra Gulipilli Krishna Mantravadi Ranjith Kalivarapu Rakesh Viswanath Frontlines EduTech (FLM)

SQL> create or replace package demo_student is  2 procedure add_stu(sid number, name varchar);  3 procedure edit_stu(sid number, name varchar);  4 end demo_student;  5 / Package created. SQL> create or replace package body demo_student is  2 procedure add_stu(sid number, name varchar) is  3 begin  4 dbms_output.put_line('Inserted Success');  5 end add_stu;  6 procedure edit_stu(sid number, name varchar) is  7 begin  8 search_stud(sid);  9 dbms_output.put_line('Record Updated Success');  10 end edit_stu;  11 procedure search_stud(sid number) is  12 begin  13 dbms_output.put_line('Record Find Success');  14 end search_stud;  15 end demo_student;  16 / Warning: Package Body created with compilation errors. SQL> show error; Errors for PACKAGE BODY DEMO_STUDENT: LINE/COL ERROR -------- ----------------------------------------------------------------- 8/1   PL/SQL: Statement ignored 8/1   PLS-00313: 'SEARCH_STUD' not declared in this scope SQL> create or replace package body demo_student is  2 procedure search_stud(sid number);  3 procedure add_stu(sid number, name varchar) is  4 begin  5 dbms_output.put_line('Inserted Success');  6 end add_stu;  7 procedure edit_stu(sid number, name varchar) is  8 begin  9 search_stud(sid);  10 dbms_output.put_line('Record Updated Success');  11 end edit_stu;  12 procedure search_stud(sid number) is  13 begin  14 dbms_output.put_line('Record Find Success');  15 end search_stud;  16 end demo_student;  17 / Package body created. SQL> exec demo_student.add_stu(101,'Mahadev'); Inserted Success PL/SQL procedure successfully completed. SQL> exec demo_student.edit_stu(101,'Mahakal'); Record Find Success Record Updated Success PL/SQL procedure successfully completed.