How to Use SQL Window Functions

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!

SQL Window Functions: RANK, DENSE_RANK, and ROW_NUMBER In SQL, 𝐰𝐢𝐧𝐝𝐨𝐰 𝐟𝐮𝐧𝐜𝐭𝐢𝐨𝐧𝐬 are incredibly useful for assigning unique rankings to rows within a result set. Each window function consists of three key components: 1. Function + OVER() 2. PARTITION BY 3. ORDER BY Let's explore the `RANK`, 'DENSE_RANK', and `ROW_NUMBER` functions, how they work, and when to use them: 1. 𝐑𝐀𝐍𝐊():   - Purpose: Assigns a rank to each row within a partition of a result set. Rows with equal values receive the same rank, and the next rank is incremented by the number of tied rows.   - Usage: Useful when you need to rank items but want to account for ties.   - Use Case: In a sales leaderboard, `RANK()` can be used to rank salespeople by their sales figures. Tied sales figures will get the same rank, reflecting fair competition. 2. 𝐃𝐄𝐍𝐒𝐄_𝐑𝐀𝐍𝐊():   - Purpose: Similar to `RANK()`, but ranks are consecutive integers. No gaps are left in the ranking sequence when there are ties.   - Usage: Useful when you need ranking without gaps between tied ranks.   - Use Case: When determining medal positions in a sports event, `DENSE_RANK()` can be used to rank athletes. Tied positions get the same rank, but subsequent ranks follow consecutively. 3. 𝐑𝐎𝐖_𝐍𝐔𝐌𝐁𝐄𝐑():   - Purpose: Assigns a unique sequential integer to rows within a partition of a result set, starting at 1 for the first row in each partition.   - Usage: Useful when you need a unique identifier for each row.   - Use Case: When paginating results on a website, `ROW_NUMBER()` can assign a unique number to each row for easier navigation through pages. 𝐕𝐢𝐬𝐮𝐚𝐥𝐢𝐳𝐞 𝐚 𝐭𝐚𝐛𝐥𝐞 𝐨𝐟 𝐬𝐚𝐥𝐞𝐬 𝐢𝐧 𝐞𝐚𝐜𝐡 𝐫𝐞𝐠𝐢𝐨𝐧: - 𝐑𝐀𝐍𝐊: Ties get the same rank, and the next rank skips ahead. - 𝐃𝐄𝐍𝐒𝐄_𝐑𝐀𝐍𝐊: Ties get the same rank, but ranks are consecutive. - 𝐑𝐎𝐖_𝐍𝐔𝐌𝐁𝐄𝐑: Each row gets a unique number, even for ties. There are Aggregate Functions, Value Functions, and Navigation Functions in window functions. Will make a post for them soon:) These functions are powerful tools for managing and analyzing data. They help you gain insights by effectively ranking and numbering rows based on your criteria. 𝐓𝐡𝐢𝐬 𝐢𝐬 𝐚𝐥𝐬𝐨 𝐨𝐧𝐞 𝐨𝐟 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐢𝐦𝐩𝐨𝐫𝐭𝐚𝐧𝐭 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰 𝐪𝐮𝐞𝐬𝐭𝐢𝐨𝐧𝐬! If you find these helpful, feel free to... 👍 React 💬 Comment ♻️ Share #sql #dataanalytics #windowfunctions #dataanalysis

One of the most underutilized SQL window functions is LAG(). If you’ve ever needed to calculate change over time then LAG() is your function. It lets you look at a previous row without having to self-join to the same table. For example: suppose you're working with e-commerce data and want to see how product prices have changed month-over-month: SELECT   product_id,  month,  price,  price - LAG(price) OVER (PARTITION BY product_id ORDER BY month) AS price_change FROM product_pricing This gives you the monthly price difference for each product. No joins. No nested queries. No CTEs. Just clean logic using window functions. #sql #datascience #analytics #data

Most of us are familiar with the basic use of window functions with OVER (PARTITION BY ... ORDER BY), but what if you want to calculate a moving average in #SQL? Suppose you have a sales table and you want to calculate a 3-day moving average of sales. This is when you need to explore the ROWS clause. 👇 In the example below, we do just that: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW defines a window frame that includes the current row and the two rows before it. You can use the CREATE and INSERT statements below on https://www.db-fiddle.com/ to test the moving average query yourself. -- Create the sales table CREATE TABLE sales (   sale_id INT PRIMARY KEY,   sale_date DATE,   amount DECIMAL(10, 2) ); -- Insert sample data INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, '2023-01-01', 100.00), (2, '2023-01-02', 150.00), (3, '2023-01-03', 200.00), (4, '2023-01-04', 120.00), (5, '2023-01-05', 180.00), (6, '2023-01-06', 90.00), (7, '2023-01-07', 210.00), (8, '2023-01-08', 130.00), (9, '2023-01-09', 160.00), (10, '2023-01-10', 140.00); - - - - - - - - - - - - ✅ 𝗟𝗼𝘃𝗲𝗱 𝗶𝘁? Follow Lasha Dolenjashvili for more. 🚀 #SASpace #DataEngineering #DataAnalytics