5 Essential SQL Window Functions for Data Analysts

Most people don’t struggle with SQL because it’s hard. They struggle because they only learn pieces of it. SQL is not a tool. It’s a system. And if you don’t know the full board, you can’t play the game well. Here are 64 SQL commands every data professional should know 👇 Core Queries SELECT – Retrieve data from a table DISTINCT – Remove duplicate rows WHERE – Filter rows based on conditions ORDER BY – Sort results GROUP BY – Aggregate data into groups HAVING – Filter aggregated results LIMIT – Restrict number of rows returned OFFSET – Skip a number of rows Joins INNER JOIN – Return matching records from both tables LEFT JOIN – All records from left + matching from right RIGHT JOIN – All records from right + matching from left FULL OUTER JOIN – All records from both tables CROSS JOIN – Cartesian product of both tables SELF JOIN – Join a table with itself Filtering & Conditions IN – Match values within a list BETWEEN – Filter within a range LIKE – Pattern matching IS NULL – Check for NULL values IS NOT NULL – Check for non-NULL values EXISTS – Check if subquery returns rows NOT EXISTS – Check if subquery returns no rows Aggregations COUNT – Count rows SUM – Total of values AVG – Average value MIN – Smallest value MAX – Largest value Table Operations CREATE TABLE – Create a new table ALTER TABLE – Modify table structure DROP TABLE – Delete table permanently TRUNCATE TABLE – Remove all rows quickly RENAME – Rename table Data Manipulation INSERT INTO – Add new records UPDATE – Modify existing records DELETE – Remove records MERGE – Upsert (insert/update based on match) Constraints PRIMARY KEY – Unique identifier for rows FOREIGN KEY – Link between tables UNIQUE – Ensure unique values NOT NULL – Prevent NULL values CHECK – Enforce condition on column DEFAULT – Set default value Index & Performance CREATE INDEX – Improve query performance DROP INDEX – Remove index Views & Advanced CREATE VIEW – Virtual table from query DROP VIEW – Remove a view MATERIALIZED VIEW – Precomputed stored view Window Functions ROW_NUMBER – Assign unique row numbers RANK – Rank with gaps DENSE_RANK – Rank without gaps LEAD – Access next row value LAG – Access previous row value Set Operations UNION – Combine results (remove duplicates) UNION ALL – Combine results (keep duplicates) INTERSECT – Common records EXCEPT – Records in first not in second Advanced SQL CASE – Conditional logic COALESCE – First non-null value NULLIF – Return NULL if values match WITH (CTE) – Temporary result set SUBQUERY – Query inside a query Transactions BEGIN – Start transaction COMMIT – Save changes ROLLBACK – Undo changes If SQL is your chessboard, these are your pieces. Master them, and you stop writing queries… you start thinking in SQL. 📌 𝗥𝗲𝗴𝗶𝘀𝘁𝗿𝗮𝘁𝗶𝗼𝗻𝘀 𝗮𝗿𝗲 𝗼𝗽𝗲𝗻 𝗳𝗼𝗿 𝗼𝘂𝗿 𝟮𝗻𝗱 𝗯𝗮𝘁𝗰𝗵 𝗼𝗳 𝗗𝗮𝘁𝗮 𝗘𝗻𝗴𝗶𝗻𝗲𝗲𝗿𝗶𝗻𝗴 𝗖𝗼𝗵𝗼𝗿𝘁 , 𝗘𝗻𝗿𝗼𝗹𝗹 𝗵𝗲𝗿𝗲- https://rzp.io/rzp/May2026

🚀 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 13/15 — SQL TRAP: PARTITION BY vs GROUP BY GROUP BY collapses your data. PARTITION BY keeps everything. Most people don't know the difference. 👇 🎯 The Situation: You want to show each employee's name, their salary AND their department's total salary — all in the same row. You try GROUP BY. Result → only 2 rows. 😵 All employee names and individual salaries — GONE. Just department totals left. But you needed BOTH individual details AND group totals together! 🧠 Here's the simple difference: GROUP BY → collapses all rows into groups. You lose individual employee details forever. 5 employees become 2 department rows. PARTITION BY → calculates the group total BUT keeps every row intact. 5 employees stay as 5 rows. Each row also shows their department total. Same calculation. Completely different output. ✅ The Result Difference: GROUP BY result → Engineering = 24000 Marketing = 11000 (Only 2 rows. Names gone.) ❌ PARTITION BY result → Alice → 9000 → dept total 24000 ✅ Bob → 7000 → dept total 24000 ✅ Carol → 6000 → dept total 11000 ✅ Dave → 5000 → dept total 11000 ✅ Eve → 8000 → dept total 24000 ✅ All 5 rows. Individual salaries. Department totals. Everything together. 💡 Real Life Example: Imagine a school report. GROUP BY = show only class average. Individual student marks disappear. 😵 PARTITION BY = show every student's mark AND their class average side by side. ✅ One gives you a summary. Other gives you full detail with context. 📌 Save This Rule: → Need only group totals? → GROUP BY → Need individual rows AND group totals together? → PARTITION BY → PARTITION BY always uses OVER() → SUM(salary) OVER(PARTITION BY department) → GROUP BY collapses rows → you lose individual details → PARTITION BY never collapses → all rows always stay 🔑 One Line to Remember: GROUP BY = collapses rows = summary only PARTITION BY = keeps all rows = detail + summary together This is called a Window Function. One of the most powerful and most feared SQL concepts in interviews. 😎 💬 Real Talk: Window functions like PARTITION BY separate beginner SQL from advanced SQL. If you understand this — you're already ahead of most candidates in interviews. 🙋 Quick Quiz: If you use PARTITION BY department — how many rows will you get for a table with 10 employees across 3 departments? Drop your answer below 👇 Follow for Day 14 tomorrow — almost at the finish line! 🚀 #SQL #SQLForBeginners #WindowFunctions #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep

Ever tried writing a simple running total in SQL… and ended up with a monster query? 😅 That was me before discovering window functions. From messy self-joins to confusing subqueries, solving things like rankings, running totals, or row comparisons felt unnecessarily hard. Then window functions came in — powerful, but initially super confusing (PARTITION BY?! ORDER BY?! 🤯). If you’ve struggled with them, you’re not alone. Let’s break it down together 👇 Here are some real interview-style questions on SQL window functions that will take you from confusion to confidence 🚀 ✅ *SQL Window Functions Interview Questions with Answers* ✍️ *1. What are window functions in SQL?* - Window functions perform calculations across related rows - They do not reduce rows - Each row keeps its detail Key syntax: `OVER (PARTITION BY, ORDER BY)` *2. Difference between GROUP BY and window functions* - GROUP BY collapses rows - Window functions keep all rows - Window functions add calculated columns *3. What is ROW_NUMBER?* - Assigns unique sequential number - No ties allowed Example: Rank employees by salary SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; *4. Difference between ROW_NUMBER, RANK, and DENSE_RANK* - ROW_NUMBER gives unique numbers - RANK skips numbers on ties - DENSE_RANK does not skip Example salaries: 100, 100, 90 ROW_NUMBER → 1, 2, 3 RANK → 1, 1, 3 DENSE_RANK → 1, 1, 2 *5. What is PARTITION BY?* - PARTITION BY splits data into groups - Window function runs inside each group Example: Rank employees per department SELECT department, name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; *6. Find top 2 salaries per department* SELECT * FROM ( SELECT department, name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM employees ) t WHERE rnk <= 2; *7. What is LAG?* - Accesses previous row value - Used for comparisons Example: Day over day sales SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_day_sales FROM daily_sales; 8. What is LEAD? - Accesses next row value Example: Compare today with next day SELECT date, sales, LEAD(sales) OVER (ORDER BY date) AS next_day_sales FROM daily_sales; 9. Calculate day over day growth SELECT date, sales - LAG(sales) OVER (ORDER BY date) AS growth FROM daily_sales; 10. Common window function interview mistakes - Forgetting ORDER BY inside OVER - Using WHERE instead of subquery to filter ranks - Mixing GROUP BY with window logic incorrectly Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → WINDOW → ORDER BY #DataAnalytics #DataAnalyst #DataScience #BusinessIntelligence #PowerBI #SQL #Python #DataVisualization #Analytics #DataSkills #CareerGrowth #DataCommunity #LearningInPublic #LinkedInLearning #TechCareers #WomenInTech #DataJourney #AnalyticsThinking #DataDriven #Upskilling

⚠️ DAY 14/15 — SQL TRAP: No Index vs Index Same query. Same table. Same result. One takes 120 seconds. Other takes 0.1 seconds. The difference? One line of code. 👇 🎯 The Situation: Your table has 1 million employees. You search for one employee by name. No error. Query runs. Result comes. But it takes 2 whole minutes. 😵 Your dashboard is freezing. Users are complaining. Your manager is asking questions. All because of one missing setup. 🧠 Here's what's happening: Without an index — SQL has no idea where your data lives. So it checks every single row. One by one. All 1 million of them. This is called a FULL TABLE SCAN. Every. Single. Row. Checked. Even when only 1 row matches. With an index — SQL knows exactly where Alice is. Jumps directly to her row. 1 row scanned. Done. ✅ The Numbers: No Index → Rows scanned = 1,000,000 Time = ~120 seconds ❌ With Index → Rows scanned = 1 Time = ~0.1 seconds ✅ 1200x faster. Just from one line — CREATE INDEX idx_name ON employees(name) 💡 Real Life Example: Without index = finding a word in a book by reading every single page from start to finish. 😵 With index = opening the book's index at the back, finding the word, jumping straight to that page. ✅ Same book. Same word. One way is just exhausting. 📌 Save This Rule: → No index = full table scan = checks every row = very slow → Index = direct lookup = jumps to result = very fast → Always index columns used in WHERE, JOIN, ORDER BY → Too many indexes slow down INSERT and UPDATE — balance is key → Use EXPLAIN command to check if your query is using an index 🔑 One Line to Remember: No Index = reads entire table Index = jumps directly to answer On small tables you won't notice. On production data with millions of rows — this is the difference between a fast app and a broken one. 💬 Real Talk: Most slow dashboards and freezing reports in real companies are not caused by bad queries. They are caused by missing indexes. This is one of the first things senior engineers check when a query is running slow. 😎 🙋 Have you ever had a query that worked fine in testing but became super slow in production? Missing index was probably the reason! Comment below 👇 Follow for Day 15 tomorrow — the FINAL day of the series! 🚀 #SQL #SQLForBeginners #DataAnalytics #LearnSQL #SQLTips #DataEngineering #InterviewPrep

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

🔥 𝗗𝗮𝘆 34 – 𝗦𝗤𝗟 𝗦𝗲𝗿𝘃𝗲𝗿 𝗝𝗼𝗶𝗻𝘀 (𝗣𝗮𝗿𝘁 2) SQL Joins are one of the most important concepts in SQL Server. They are used to combine data from multiple tables using related columns. In real-world projects, data is usually stored in separate tables such as Customers, Products, Transactions, Accounts, Employees, and Sales. ━━━━━━━━━━━━━━━━━━ 📌 𝗧𝘆𝗽𝗲𝘀 𝗼𝗳 𝗦𝗤𝗟 𝗝𝗼𝗶𝗻𝘀 1️⃣ 𝗜𝗡𝗡𝗘𝗥 𝗝𝗢𝗜𝗡 Returns only matching records from both tables. SELECT * FROM AccountMaster A INNER JOIN TransactionMaster T ON A.AccountID = T.AccountID; 2️⃣ 𝗟𝗘𝗙𝗧 𝗝𝗢𝗜𝗡 Returns all rows from the left table and matching rows from the right table. SELECT * FROM AccountMaster A LEFT JOIN TransactionMaster T ON A.AccountID = T.AccountID; 3️⃣ 𝗥𝗜𝗚𝗛𝗧 𝗝𝗢𝗜𝗡 Returns all rows from the right table and matching rows from the left table. SELECT * FROM AccountMaster A RIGHT JOIN TransactionMaster T ON A.AccountID = T.AccountID; 4️⃣ 𝗙𝗨𝗟𝗟 𝗝𝗢𝗜𝗡 Returns matching and unmatched rows from both tables. SELECT * FROM AccountMaster A FULL JOIN TransactionMaster T ON A.AccountID = T.AccountID; 5️⃣ 𝗖𝗥𝗢𝗦𝗦 𝗝𝗢𝗜𝗡 Returns every possible combination of rows. Formula: Rows in Table A × Rows in Table B Example: 6 rows × 6 rows = 36 rows SELECT * FROM AccountMaster A CROSS JOIN TransactionMaster T; ━━━━━━━━━━━━━━━━━━ 💡 𝗜𝗺𝗽𝗼𝗿𝘁𝗮𝗻𝘁 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗣𝗼𝗶𝗻𝘁𝘀 ✔ NULL does not match NULL ✔ JOIN means INNER JOIN by default ✔ CROSS JOIN does not need a common column ✔ If joining N tables, we need N - 1 join conditions ✔ Use aliases for clean queries ✔ Use table names when same column exists in multiple tables ━━━━━━━━━━━━━━━━━━ ⚠️ 𝗔𝗺𝗯𝗶𝗴𝘂𝗼𝘂𝘀 𝗖𝗼𝗹𝘂𝗺𝗻 𝗘𝗿𝗿𝗼𝗿 ❌ Wrong: SELECT AccountID FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID; ✅ Correct: SELECT A.AccountID FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID; ━━━━━━━━━━━━━━━━━━ 🚀 𝗥𝗲𝗮𝗹-𝗧𝗶𝗺𝗲 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 Customer-wise Transaction Summary SELECT A.Name, T.TransactionType, COUNT(*) AS NumberOfTransactions, SUM(T.TransactionAmount) AS TotalAmount FROM AccountMaster A JOIN TransactionMaster T ON A.AccountID = T.AccountID GROUP BY A.Name, T.TransactionType; ━━━━━━━━━━━━━━━━━━ 🎯 𝗙𝗶𝗻𝗮𝗹 𝗧𝗵𝗼𝘂𝗴𝗵𝘁 SQL Joins are the backbone of: ✔ Reporting ✔ Analytics ✔ ETL ✔ Dashboards ✔ Data Engineering 👉 Master joins and complex SQL becomes much easier. #SQL #SQLServer #SQLQueries #SQLDeveloper #SQLLearning #LearnSQL #SQLInterview #SQLTips #SQLPractice #TSQL #MicrosoftSQLServer #Database #DatabaseDeveloper #DatabaseManagement #DataAnalytics #DataAnalysis #DataAnalyst #BusinessIntelligence #BI #PowerBI #PowerBIDeveloper #Dashboard #Reporting #ETL #DataEngineering #DataEngineer #DataWarehouse #DataModeling #Joins #InnerJoin #LeftJoin #RightJoin #FullJoin #CrossJoin #TechJobs #Analytics #Coding #Programming #InterviewPreparation #CareerGrowth #LinkedInLearning Bhaskar Jogi Go Online Trainings

SQL doesn’t have a fixed number of “queries” or “joins”—it’s a language, not a limited set of commands. But you can group things in a way that’s easy to understand for interviews and real work. 🔹 1. Types of SQL Queries 🟢 Main Categories (5 types) 1. DQL (Data Query Language)** * Used to fetch data * Example: ```sql SELECT * FROM employees; ``` 2. DML (Data Manipulation Language)** * Used to modify data * Commands: ```sql INSERT INTO employees VALUES (1, 'John'); UPDATE employees SET name='Sam' WHERE id=1; DELETE FROM employees WHERE id=1; ```3. DDL (Data Definition Language)** * Used to define structure ```sql CREATE TABLE employees (id INT, name VARCHAR(50)); ALTER TABLE employees ADD salary INT; DROP TABLE employees; ```4. DCL (Data Control Language)** * Permissions ```sql GRANT SELECT ON employees TO user1; REVOKE SELECT ON employees FROM user1; ``` **5. TCL (Transaction Control Language)** * Manage transactions ```sql COMMIT; ROLLBACK; SAVEPOINT sp1; 👉 So broadly, **5 types of SQL queries** 🔹 2. Types of SQL Joins 🟢 Main Joins (5 types) 1. INNER JOIN 👉 Returns only matching records from both tables ```sql SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; ``` 2. LEFT JOIN (LEFT OUTER JOIN) 👉 Returns all records from left table + matched from right ```sql SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; ``` 3. RIGHT JOIN (RIGHT OUTER JOIN) 👉 Returns all records from right table + matched from left ```sql SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id; ``` 4. FULL JOIN (FULL OUTER JOIN) 👉 Returns all records from both tables ```sql SELECT e.name, d.dept_name FROM employees e FULL JOIN departments d ON e.dept_id = d.id; ``` 5. CROSS JOIN 👉 Returns all combinations (Cartesian product) ```sql SELECT e.name, d.dept_name FROM employees e CROSS JOIN departments d; `` 🔹 3. Visual Understanding | Join Type | Result | | ---------- | ------------------------- | | INNER JOIN | Matching data only | | LEFT JOIN | All left + matching right | | RIGHT JOIN | All right + matching left | | FULL JOIN | Everything | | CROSS JOIN | All combinations | 🔹 4. Real-Time Example (Interview Style) Tables: Employees | id | name | dept_id | | -- | ---- | ------- | **Departments** | id | dept_name | Scenario: 👉 “Show all employees even if department is missing” ✔ Use: ```sql LEFT JOIN 👉 “Show only employees with valid departments” ✔ Use: ```sql INNER JOIN ``` 🔹 5. Key Tips (Very Important) * Always use **JOIN with ON condition** * Use aliases (`e`, `d`) for readability * Avoid **CROSS JOIN** in real projects (huge data) * LEFT JOIN is most used in real-time projects #SQL #StructuredQueryLanguage #DataAnalytics #DataScience #Database #TechLearning #Programming #Coding #LearnSQL #ITJobs

SQL Window Functions SQL Window Functions: Functions that perform calculations across a subset (window) of rows related to the current row, without losing the row-level detail. Unlike GROUP BY, window functions do not reduce the number of rows in the output. GROUP BY: Aggregates data by specified columns, reducing the output rows to one per group, losing detailed row-level information. Granularity: GROUP BY changes granularity (level of detail), while window functions maintain original granularity. Window Components: Window function: e.g., SUM, COUNT, AVG, RANK, LEAD, LAG. OVER clause: Defines the window or partition. PARTITION BY: Divides data into groups (windows). ORDER BY: Sorts data inside each partition. FRAME clause: Specifies subset of rows within the partition relevant for calculations. Aggregate Window Functions Count(): Counts rows or non-null values if a column is specified. Sum(): Sums numerical values, ignoring NULLs. Average(): Calculates mean; NULLs ignored unless handled (e.g., COALESCE). Min() / Max(): Finds minimum or maximum values; NULLs ignored unless replaced. Running Total / Rolling Total: Running total: Cumulative sum from start to current row. Rolling total: Sum over a fixed window size (e.g., last 3 months), shifting as new rows come in. General window function syntax: FUNCTION(expression)OVER(PARTITION BY …ORDER BY …ROWS BETWEEN…) PARTITION BY: Optional, defines groups. ORDER BY: Optional for aggregates but required for ranking and value functions. ROWS BETWEEN: Defines frame boundaries, critical for running/rolling totals and last_value function. Use Cases: Top/bottom N analysis (ROW_NUMBER, RANK, DENSE_RANK). Data segmentation (NTILE). Distribution and percentile calculations (CUME_DIST, PERCENT_RANK). Frame Clause Details Defines the subset of rows used for calculation within each partition. Frame boundaries examples: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: From start of partition to current row (default for running totals). ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING: Current row plus next 2 rows (rolling window). Frame clause is mandatory for LAST_VALUE() to get correct results. Rules and Limitations for Window Functions Window functions can only be used in SELECT or ORDER BY clauses. Cannot be used in WHERE or GROUP BY clauses. Cannot nest window functions inside each other. Window functions execute after filtering (WHERE clause). Can be mixed with GROUP BY only if window function uses aggregated columns present in GROUP BY. Key Insights Window functions enable complex analytics without losing row-level detail, unlike GROUP BY. ORDER BY inside OVER clause is mandatory for ranking and value functions. Frame clause provides powerful control over subsets of data within partitions, critical for running/rolling totals and LAST_VALUE. Lead and Lag functions simplify time-based comparisons and customer behavior analysis. Ranking functions are essential for top/bottom N and distribution analyses.

SQL Cheat Sheet for Data Analysts 🗄️📊 1. SELECT What it is: Used to choose columns from a table What it does: Returns specific columns of data Query: Fetch name and salary SELECT name, salary FROM employees;   2. FRO What it is: Specifies the table What it does: Tells SQL where to get data from Query: Fetch all data from employees SELECT * FROM employees;   3. WHER What it is: Filters rows based on condition What it does: Returns only matching rows Query: Employees with salary > 30000 SELECT * FROM employees WHERE salary > 30000; 4. ORDER B What it is: Sorts the data What it does: Arranges rows in order Query: Sort by salary (highest first) SELECT * FROM employees ORDER BY salary DESC; 5. COUNT() What it is: Counts rows What it does: Returns total records Query: Count employees SELECT COUNT(*) FROM employees; 6. AVG() What it is: Calculates average What it does: Returns mean value Query: Average salary SELECT AVG(salary) FROM employees; 7. GROUP BY What it is: Groups rows by column What it does: Applies aggregation per group Query: Avg salary per department SELECT department, AVG(salary) FROM employees GROUP BY department;   8. HAVING What it is: Filters grouped data What it does: Returns filtered groups Query: Departments with avg salary > 40000 SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 40000; 9. INNER JOIN What it is: Combines matching rows from tables What it does: Returns common data Query: Employees with department names SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; 10. LEFT JOIN What it is: Combines all left + matching right What it does: Returns all left table data Query: All employees with departments SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; 11. CASE WHEN What it is: Conditional logic What it does: Creates values based on condition Query: Categorize salary SELECT name, CASE WHEN salary > 40000 THEN 'High' ELSE 'Low' END AS category FROM employees; 12. SUBQUERY What it is: Query inside another query What it does: Uses result of inner query Query: Salary above average SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ); 13. RANK() What it is: Window function What it does: Assigns rank without grouping Query: Rank employees by salary SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; 14. DISTINC What it is: Removes duplicates What it does: Returns unique values Query: Unique departments SELECT DISTINCT department FROM employees; 15. LIKE What it is: Pattern matching What it does: Filters text patterns Query: Names starting with A SELECT * FROM employees WHERE name LIKE 'A%'; #SQL #LinkedIn #Learning #Skill