Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Data Analytics with Window Functions

- 16.08.26 - ErcanOPAK

📊 Window Functions = Data Analytics

Advanced analytics needs window functions. SQL window functions enable complex data analysis.

📝 Window Functions Examples

-- Ranking
SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) as rank,
    DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) as row_number
FROM products;

-- Partitioned Ranking
SELECT 
    category,
    product_name,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

-- Running Total
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving Average
SELECT 
    date,
    sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as seven_day_avg
FROM daily_sales;

🎯 Advanced Analytics

-- LAG and LEAD
SELECT 
    customer_id,
    order_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order,
    LEAD(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as next_order
FROM orders;

-- Percentile
SELECT 
    product_name,
    price,
    PERCENT_RANK() OVER (ORDER BY price) as percentile_rank,
    CUME_DIST() OVER (ORDER BY price) as cumulative_distribution
FROM products;

-- NTILE
SELECT 
    customer_id,
    total_spent,
    NTILE(4) OVER (ORDER BY total_spent) as quartile
FROM customer_summary;

-- First and Last Value
SELECT 
    category,
    product_name,
    price,
    FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price) as cheapest,
    LAST_VALUE(product_name) OVER (PARTITION BY category ORDER BY price 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as most_expensive
FROM products;

-- Year over Year Growth
WITH yearly_sales AS (
    SELECT 
        YEAR(order_date) as year,
        SUM(amount) as total_sales
    FROM orders
    GROUP BY YEAR(order_date)
)
SELECT 
    year,
    total_sales,
    LAG(total_sales) OVER (ORDER BY year) as previous_year,
    (total_sales - LAG(total_sales) OVER (ORDER BY year)) / LAG(total_sales) OVER (ORDER BY year) * 100 as growth
FROM yearly_sales;

💡 Window Function Tips

  • Use PARTITION BY for groups
  • Use ORDER BY for ordering
  • Use ROWS/RANGE for sliding windows
  • Combine with CTEs for complex queries
  • Index partition columns

Window functions unlock the full power of SQL for analytics. They’re essential for data professionals.

— Data Analyst

Related posts:

How to reset identity seed after deleting records in SQL

How to reset identity seed after deleting records in SQL

SQL Deadlocks Appear Under Load

SQL Deadlocks Appear Under Load

SQL Server: Fix a Trigger That Fires Multiple Times for a Single Multi-Row Update

SQL Server: Fix a Trigger That Fires Multiple Times for a Single Multi-Row Update

Views: 11

Post navigation

.NET Core: Building REST APIs with Minimal APIs
C#: Implementing Timeouts with Cancellation Tokens

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com