How to Use SQL Window Functions

𝗬𝗼𝘂 𝗹𝗲𝗮𝗿𝗻 𝗦𝗤𝗟. Then you hit your first analytics problem. Suddenly, window functions are the only thing that make sense. Because real analytics isn’t about rows, it’s about context. And window functions give you exactly that. Here’s how they show up in real business problems: 𝗥𝘂𝗻𝗻𝗶𝗻𝗴 𝗧𝗼𝘁𝗮𝗹𝘀 Track cumulative revenue, orders, or growth without losing row detail. Functions: SUM() OVER 𝗠𝗼𝗻𝘁𝗵-𝗼𝘃𝗲𝗿-𝗠𝗼𝗻𝘁𝗵 𝗚𝗿𝗼𝘄𝘁𝗵 Compare current values with previous periods directly in SQL. Functions: LAG() 𝗣𝗲𝗿𝗶𝗼𝗱-𝗼𝘃𝗲𝗿-𝗣𝗲𝗿𝗶𝗼𝗱 𝗖𝗼𝗺𝗽𝗮𝗿𝗶𝘀𝗼𝗻 See weekly, monthly, or quarterly changes without collapsing rows. Functions: LAG(), LEAD() 𝗥𝗮𝗻𝗸𝗶𝗻𝗴 & 𝗟𝗲𝗮𝗱𝗲𝗿𝗯𝗼𝗮𝗿𝗱𝘀 Find top customers, products, or regions with proper ranking logic. Functions: RANK(), DENSE_RANK(), ROW_NUMBER() 𝗖𝘂𝘀𝘁𝗼𝗺𝗲𝗿 / 𝗘𝗻𝘁𝗶𝘁𝘆 𝗦𝗲𝗴𝗺𝗲𝗻𝘁𝗮𝘁𝗶𝗼𝗻 Run separate calculations for regions, categories, or user groups. Functions: PARTITION BY 𝗧𝗶𝗺𝗲 𝗕𝗲𝘁𝘄𝗲𝗲𝗻 𝗘𝘃𝗲𝗻𝘁𝘀 Measure gaps between user actions - logins, clicks, purchases. Functions: LAG() 𝗠𝗼𝘃𝗶𝗻𝗴 𝗔𝘃𝗲𝗿𝗮𝗴𝗲𝘀 Smooth noisy data to spot real trends. Functions: AVG() OVER (ROWS BETWEEN …) 𝗗𝗲𝗱𝘂𝗽𝗹𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗟𝗼𝗴𝗶𝗰 Pick the correct record when duplicates sneak in. Functions: ROW_NUMBER() 𝗣𝗲𝗿𝗰𝗲𝗻𝘁𝗮𝗴𝗲 𝗖𝗼𝗻𝘁𝗿𝗶𝗯𝘂𝘁𝗶𝗼𝗻 See each row’s contribution to the whole or a subtotal. Functions: SUM() OVER () 𝗙𝘂𝗻𝗻𝗲𝗹 𝗗𝗿𝗼𝗽-𝗢𝗳𝗳 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 Understand where users fall off across stages. Functions: ROW_NUMBER(), LAG() 𝗧𝗼𝗽-𝗡 𝗽𝗲𝗿 𝗚𝗿𝗼𝘂𝗽 Top 3 sellers per category. Top 5 customers per region. Functions: ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) 𝗥𝗼𝗹𝗹𝗶𝗻𝗴 𝗠𝗲𝘁𝗿𝗶𝗰𝘀 Track rolling sums, averages, and trends over time. Functions: SUM() OVER (ROWS BETWEEN …) 𝗦𝗹𝗼𝘄𝗹𝘆 𝗖𝗵𝗮𝗻𝗴𝗶𝗻𝗴 𝗗𝗶𝗺𝗲𝗻𝘀𝗶𝗼𝗻 𝗗𝗲𝘁𝗲𝗰𝘁𝗶𝗼𝗻 Spot attribute changes over time for the same entity. Functions: LAG() 𝗙𝗶𝗿𝘀𝘁 & 𝗟𝗮𝘀𝘁 𝗩𝗮𝗹𝘂𝗲 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 Find the first purchase, last login, or most recent status. Functions: FIRST_VALUE(), LAST_VALUE() 𝗠𝗲𝗱𝗶𝗮𝗻 & 𝗣𝗲𝗿𝗰𝗲𝗻𝘁𝗶𝗹𝗲 𝗔𝗻𝗮𝗹𝘆𝘀𝗶𝘀 Understand distributions without distorting detail. Functions: PERCENTILE_CONT() 𝗔𝘂𝗱𝗶𝘁 & 𝗖𝗵𝗮𝗻𝗴𝗲 𝗧𝗿𝗮𝗰𝗸𝗶𝗻𝗴 See how values changed across consecutive records. Functions: LAG(), LEAD() 𝗔𝗻𝗼𝗺𝗮𝗹𝘆 𝗗𝗲𝘁𝗲𝗰𝘁𝗶𝗼𝗻 Detect sudden spikes or drops in metrics. Functions: AVG() OVER, LAG() Window functions aren’t advanced SQL, they’re essential analytics. If you want to think like a data analyst or analytics engineer, mastering them is non-negotiable.

Everyone says "learn SQL." Nobody gives you the actual cheat sheet. I analyzed 50+ analyst job descriptions and real queries from production dashboards. The same 10 functions appeared in almost every single one. Here they are. Syntax included. Copy and use today. 🟢 FILTERING ① WHERE SELECT * FROM orders WHERE status = 'completed' Filter rows before aggregation. This is your first instinct for any query. ② HAVING SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 100 Filter AFTER aggregation. WHERE filters rows. HAVING filters groups. Know the difference. ③ CASE WHEN SELECT order_id, CASE WHEN amount > 1000 THEN 'high' WHEN amount > 100 THEN 'medium' ELSE 'low' END AS tier FROM orders IF/ELSE logic inside SQL. Use it to create categories, flags, labels on the fly. 🟢 AGGREGATION ④ GROUP BY + COUNT / SUM / AVG SELECT region, COUNT(*) AS users, AVG(revenue) AS avg_rev FROM sales GROUP BY region The foundation of every report. Group rows → calculate metrics per group. ⑤ DISTINCT SELECT COUNT(DISTINCT customer_id) FROM orders Count unique values. Without DISTINCT you count duplicates and your numbers are wrong. 🟢 JOINS ⑥ LEFT JOIN SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.id Keep ALL rows from left table. Match what you can from right. Unmatched → NULL. This is 80% of all joins you will ever write. ⑦ INNER JOIN SELECT p.name, s.quantity FROM products p INNER JOIN stock s ON p.id = s.product_id Keep ONLY rows that match in BOTH tables. Use when you need strict matches with no NULLs. 🟢 WINDOW FUNCTIONS ⑧ ROW_NUMBER() SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders Number rows within each group. Filter WHERE rn = 1 to get the latest order per user. Use this daily. ⑨ SUM() OVER / AVG() OVER SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales Running totals and moving averages without GROUP BY. Your row-level data stays intact. ⑩ LAG() / LEAD() SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_day FROM daily_sales Access the previous or next row's value. Calculate day-over-day change in one line. 📌 QUERY TEMPLATE (covers 90% of tasks): SELECT   dimension,   COUNT(*) AS cnt,   SUM(metric) AS total FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE a.date >= '2024-01-01' GROUP BY dimension HAVING COUNT(*) > 10 ORDER BY total DESC LIMIT 20 This single template handles: → Segmented reports → Top-N analysis → Filtered aggregations → Multi-table analytics The learning path: → Week 1: ①–③ (filtering) → Week 2: ④–⑤ (aggregation) → Week 3: ⑥–⑦ (joins) → Week 4: ⑧–⑩ (window functions) 4 weeks. 10 functions. 90% of analyst SQL covered. Save this cheat sheet. Share with someone starting their analytics journey. 👇 #sql #dataanalytics #analytics #cheatsheet #career #datascience #programming

Stop overcomplicating your SQL. 🛑 If you’re still using self-joins and messy subqueries to compare rows or calculate trends, you’re working harder, not smarter. The gap between a "SQL beginner" and a "Data Pro" is usually one thing: Window Functions. These functions allow you to perform calculations across a set of rows that are related to the current row—without collapsing them into a single output like a GROUP BY does. Here is the "Cheat Sheet" for the functions that actually move the needle in interviews and real-world projects: 🛠 The Power Players 🔹 ROW_NUMBER() → Your go-to for deduplication. Assigns a unique ID to every row. 🔹 RANK() vs. DENSE_RANK() → Essential for leaderboards. RANK() leaves gaps (1, 1, 3), while DENSE_RANK() keeps it tight (1, 1, 2). 🔹 LAG() & LEAD() → The "Time Travelers." Pull data from the previous or next row to calculate Month-over-Month growth effortlessly. 🔹 SUM() OVER() → Create running totals and cumulative sums in a single line of code. 💡 Why this changes the game: 1️⃣ Readability: Your code goes from 50 lines of nested logic to 10 lines of clean, declarative SQL. 2️⃣ Performance: Most modern engines optimize window functions better than complex self-joins. 3️⃣ Interview Gold: Almost every Senior Data Analyst or Data Engineer interview will test your ability to use PARTITION BY. ✅ Practical Tip: Next time you need to compare "Current Month Sales" vs "Last Month Sales," don't join the table to itself. Use LAG(sales) OVER (ORDER BY month). Master these, and you stop being a "user" and start being an "architect" of data. 🚀 Which Window Function saved you the most time this week? Let's discuss in the comments! 👇 #SQL #DataEngineering #DataAnalytics #DataScience #CodingTips #CareerGrowth #TechSkills #Database

I've been writing SQL for 7 years. These 5 window functions handle 90% of my analytics work. 1. ROW_NUMBER(): unique rank per row, no ties → Pick the latest record per user, deduplicate, paginate results 2. RANK(): same score = same rank, then skips → Leaderboards, competition standings 3. DENSE_RANK(): same score = same rank, no skips → Percentile bands, loyalty tiers, score groupings 4. LAG(): look at the previous row's value → Month-over-month growth, detect drops, churn signals 5. LEAD(): peek at the next row's value → Time-to-next-event, session analysis, funnel drop-off The syntax that unlocks all of them: FUNCTION() OVER (PARTITION BY col ORDER BY col) That's it. PARTITION BY = your "group by" within the window ORDER BY = determines the sequence within the group Once this clicks, you stop writing messy self-joins and nested subqueries. ---------------------------- Save this. You'll need it. ♻️ Repost if someone you know is still avoiding window functions. Follow Amlan Mohanty for more Data & AI tips!

SQL is easy to learn, but hard to master. The bar keeps getting higher each year in data science and analytics interviews. Here are 8 powerful SQL window functions that go beyond the basics 👇 1️⃣ LEAD() 📦 Use Case: Get next day's sales SELECT date, amount AS today_amount, LEAD(amount) OVER (ORDER BY date) AS next_day_amount FROM Sales; 2️⃣ LAG() 📦 Use Case: Compare with previous day SELECT date, amount AS today_amount, LAG(amount) OVER (ORDER BY date) AS previous_day_amount FROM Sales; 3️⃣ ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 📦 Use Case: Rolling sum over 2 days SELECT id, value, SUM(value) OVER ( ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS sum_prev_and_current FROM Numbers; 4️⃣ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 📦 Use Case: Running total SELECT id, value, SUM(value) OVER ( ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM Numbers; 5️⃣ PERCENT_RANK() 📦 Use Case: Percentile rank of test scores SELECT student_id, score, PERCENT_RANK() OVER (ORDER BY score) AS percentile_rank FROM Scores; 6️⃣ ROW_NUMBER() 📦 Use Case: Deduplicate users by latest record SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM Users; 7️⃣ RANK() 📦 Use Case: Find salary rank (with gaps for ties) SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM Employees; 8️⃣ DENSE_RANK() 📦 Use Case: Top 3 salaries per department (no gaps) SELECT * FROM ( SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM Employees ) sub WHERE rnk <= 3; Comment if you found this helpful. Reach out via DM if you need help with stats. Repost if you think someone in your network should read this. #SQL #DataAnalytics #DataScience #WindowFunctions #InterviewPrep #LearningByDoing

This little-known SQL Window function trick replaced 5 JOINs in my query — and recruiters love it in interviews. I’ll be honest: early in my career, I was obsessed with JOINs. Need ranking? → JOIN. Need running totals? → JOIN. Need first/last values? → JOIN. The problem? My queries got slower, messier, and nearly impossible to debug. Then I started using Window Functions — and everything changed. Here’s how they saved me: ➊ Ranking & Deduplication → Instead of joining on a subquery, I used ROW_NUMBER(), DENSE_RANK() over partitions. → Clean, efficient, and no messy joins. ➋ Running Totals / Moving Averages → SUM() OVER(ORDER BY date) gave me rolling totals instantly. → No need for multiple self-joins. ➌ First & Last Records → FIRST_VALUE() and LAST_VALUE() cut down entire subqueries. → Perfect for event-based data like logins or transactions. The impact? → One query went from 200+ lines (with nested joins) to 40 lines. → Execution time dropped by ~70%. → In interviews, whenever I bring up Window Functions, recruiters nod. They know it’s the mark of someone who writes scalable SQL. Join the group: https://lnkd.in/giE3e9yH - 𝐌𝐨𝐜𝐤 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐟𝐨𝐫 𝐃𝐚𝐭𝐚 𝐄𝐧𝐠𝐢𝐧𝐞𝐞𝐫𝐬: https://lnkd.in/g8Pqypt5 - 𝐈𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐩𝐫𝐞𝐩 & 𝐏𝐫𝐨𝐯𝐞𝐧 𝐓𝐢𝐩𝐬: https://lnkd.in/gUEVYCGy - 𝐑𝐞𝐬𝐮𝐦𝐞 𝐑𝐞𝐯𝐢𝐞𝐰 𝐚𝐧𝐝 𝐎𝐩𝐭𝐢𝐦𝐢𝐳𝐚𝐭𝐢𝐨𝐧: https://lnkd.in/gp3yZsfW Follow for more 👋

You think you know SQL… until window functions show up. That’s where most queries start to break. Because once the questions become: – What changed over time? – Who’s improving faster? – What’s the latest state per user? – Where did the trend shift? GROUP BY isn’t enough. This Guide is a cheat sheet of the Top 30 SQL Window Functions that analysts, data engineers, and interviewers actually expect you to know. It covers: • Ranking logic (ROW_NUMBER, RANK, DENSE_RANK) • Time-based comparisons (LAG, LEAD) • Running totals and rolling metrics • Percentiles, variance, and distributions • ROWS vs RANGE frames • QUALIFY for cleaner queries • Real analytics patterns used in production The key shift: Window functions don’t summarize data. They help you analyze it without losing context. If SQL is part of your job or your next role, this belongs in your toolkit. Save it. Practice it. And stop flattening insights too early.

Looking to master SQL window functions? You need to understand frame clauses. Frame clauses determine which row values are the start and end of the subset of data you are looking at. Not all window functions need this specified, as the default of "range between unbounded preceding and current row" works just fine for some. However, frame clauses become crucial when using functions like FIRST_VALUE and LAST_VALUE. When you use this default for something like LAST_VALUE, it naturally looks at all of the rows BEFORE the current row, including the current row, and selects the current row value rather than the true last row value in the data subset. "Range between unbounded preceding and unbounded following" is best for these as it looks at ALL of the rows in the data subset. Here's a guide on when to use each frame clause. 💡 Even if you think you understand the behavior of window functions and frame clauses, always look at the partition to ensure it returns the expected result! Test your code before moving on.

𝗜𝗻 𝗦𝗤𝗟 𝗶𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝘀, 𝘁𝗵𝗲 𝗾𝘂𝗲𝘀𝘁𝗶𝗼𝗻 𝘁𝗵𝗮𝘁 𝘀𝗲𝗽𝗮𝗿𝗮𝘁𝗲𝘀 𝗺𝗶𝗱-𝗹𝗲𝘃𝗲𝗹 𝗳𝗿𝗼𝗺 𝘀𝗲𝗻𝗶𝗼𝗿 𝗶𝘀 𝘂𝘀𝘂𝗮𝗹𝗹𝘆 𝘁𝗵𝗲 𝗼𝗻𝗲 𝗮𝗯𝗼𝘂𝘁 𝘄𝗶𝗻𝗱𝗼𝘄 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀. Most candidates can write a basic ROW_NUMBER. Few can explain when to use ROWS vs RANGE, or why their running totals drift at month boundaries when timestamps repeat. In production, that kind of error becomes a trust problem on a dashboard. Window functions are not about syntax.  They are about reasoning over state across rows. 𝗧𝗵𝗲 𝟯 𝗽𝗶𝗲𝗰𝗲𝘀 𝘁𝗵𝗮𝘁 𝗺𝗮𝘁𝘁𝗲𝗿: → PARTITION BY. Defines the group. Without it, the window is the entire result set. → ORDER BY. Defines the sequence. Without it, "previous row" has no meaning. → Frame clause (ROWS or RANGE). Defines what the function actually sees. The frame is what most candidates miss.  ROWS counts physical rows.  RANGE includes peer rows with the same ORDER BY value.  That difference matters when timestamps, prices, or scores repeat. 𝗧𝗵𝗲 𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀 𝘄𝗼𝗿𝘁𝗵 𝗸𝗻𝗼𝘄𝗶𝗻𝗴 𝗰𝗼𝗹𝗱: → ROW_NUMBER, RANK, DENSE_RANK. Different answers when values tie. → LAG, LEAD. Compare a row to its neighbor without self-joining. → SUM, AVG with OVER. Running totals, moving averages, cumulative metrics. → FIRST_VALUE, LAST_VALUE. Anchor values within a partition. → NTILE. Bucket rows into N equal groups. The interview answer is syntax.  The production answer is choosing the correct partition, order, and frame so the result stays stable. That is how a 200-line self-join turns into 20 lines of SQL people can maintain. Which window function has saved you the most self-joins? #DataEngineering #SQL #DataAnalytics

I almost failed a Google SQL interview. Because I didn't know Window Functions. Even though I had learned about Window Functions... They never "clicked" for me. Because I couldn't grok their real-world application. So don't make the same mistake as me. Here are 5 key window functions & their applications: 𝟭/ 𝗥𝗢𝗪_𝗡𝗨𝗠𝗕𝗘𝗥() ROW_NUMBER() assigns a sequential integer to each row within a partition. 𝗙𝗼𝗿 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: We can use ROW_NUMBER() to assign a unique identifier to each transaction per customer. This allows for easy tracking and referencing of transactions within a customer's history. 𝟮/ 𝗥𝗔𝗡𝗞() RANK() assigns rankings within a partition of a result set, leaving gaps in the ranking when there are ties. 𝗙𝗼𝗿 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: In a e-commerce company, RANK() can be used to rank products by sales volume. We can use this to identify top-selling items within categories. 𝟯/ 𝗟𝗔𝗚() 𝗮𝗻𝗱 𝗟𝗘𝗔𝗗() LAG() accesses data from previous rows, while LEAD() accesses data from subsequent rows within a partition. 𝗙𝗼𝗿 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: We can use LAG() to calculate month-over-month changes in revenue. This allows for easy tracking of growth trends and identification of significant changes. 𝟰/ 𝗙𝗜𝗥𝗦𝗧_𝗩𝗔𝗟𝗨𝗘() 𝗮𝗻𝗱 𝗟𝗔𝗦𝗧_𝗩𝗔𝗟𝗨𝗘() FIRST_VALUE() returns the first value in an ordered partition, while LAST_VALUE() returns the last value. 𝗙𝗼𝗿 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: In analyzing stock prices, FIRST_VALUE() can be used to compare daily stock prices to the price at month's start, so we can measure price changes relative to the month's opening price. 𝟱/ 𝗦𝗨𝗠(), 𝗖𝗢𝗨𝗡𝗧() 𝗮𝗻𝗱 𝗔𝗩𝗚() These aggregate functions, when used with OVER(), allow for running calculations within a window. They're useful for computing cumulative totals, moving averages, or other rolling calculations. 𝗙𝗼𝗿 𝗲𝘅𝗮𝗺𝗽𝗹𝗲: In a analytics system, these functions can be used to calculate a 7-day moving average of daily active users (DAU), to smooth out daily fluctuations and identify trends in user engagement. ——— 𝗪𝗮𝗻𝘁 𝘁𝗼 𝘂𝘀𝗲 𝗪𝗶𝗻𝗱𝗼𝘄 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀 𝗼𝗻 𝗿𝗲𝗮𝗹 𝗯𝘂𝘀��𝗻𝗲𝘀𝘀 𝗽𝗿𝗼𝗯𝗹𝗲𝗺𝘀? We got you. Check out these questions on Interview Master! • Window Functions about Creators on Meta: https://lnkd.in/g3Rt_tcH • Window Functions related to Amazon Sellers: https://lnkd.in/gic9TseR • Window Functions on Microsoft Windows Updates: https://lnkd.in/gCbSpZ9i • Window Functions and Google Play store: https://lnkd.in/gajf_u2q    • Window Functions on LinkedIn Skills Endorsements: https://lnkd.in/gExPn9bb ——— ♻️ Found this useful? Repost it so others can see it too!