Imprimer Compléter: Mastering SQL Window Functions ()

running_total AS amount amount sale_date FROM SUM BY SELECT sale_date OVER sales ORDER

Problem 1 : Calculate Running Total
Question : You have a table sales ( sale_date DATE , amount DECIMAL ) . Write a SQL query to calculate a running total of amount , ordered by sale_date .

Solution :

____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

current_avg BETWEEN AND SUM BY as ORDER FROM PRECEDING running_total CURRENT CURRENT CURRENT sale_date sum_to_end AND amount OVER sale_date BY as OVER sales ORDER amount FROM sale_date AND amount CURRENT as AVG FOLLOWING sale_date sale_date amount sale_date sales BETWEEN ROW sales ROWS ROWS PRECEDING BETWEEN SELECT PRECEDING AVG amount OVER SELECT sale_date ORDER sales sale_date AVG sales OVER ORDER 3 OVER 6 amount SUM BY FROM CURRENT ROW FROM as ROWS 3 BY ROW moving_avg BETWEEN SELECT amount ROWS ROWS BETWEEN UNBOUNDED ORDER moving_avg sale_date ROW AND BY FOLLOWING SELECT amount FROM ROW as amount UNBOUNDED AND

Problem 2 : Calculate Moving Average
Question : Calculate a 7 - day moving average of sales from the sales table .

Solution :

____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

Example 2 : Fixed Range with Both PRECEDING and FOLLOWING

____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

This calculates the average amount using a window that includes three rows before , the current row , and three rows after the current row .

Example 3 : From Start of Data to Current Row
____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

This query computes a running total starting from the first row in the partition or result set up to the current row .

Example 4 : Current Row to End of Data
SELECT sale_date , amount ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

This sums the amount from the current row to the last row of the partition or result set .

Example 5 : Current Row Only
____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

This calculates the average of just the current row's amount , which effectively returns the amount itself .

BY OVER SELECT total_purchases id customers rank RANK DESC FROM AS ORDER total_purchases name

Problem 3 : Rank Customers by Sales

Question : From a table customers ( id INT , name VARCHAR , total_purchases DECIMAL ) , rank customers based on their total_purchases in descending order .

Solution :

____________________ ____________________ , ____________________ , ____________________ ,
____________________ ( ) ____________________ ( ____________________ ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;
Explanation : RANK ( ) assigns a unique rank to each row , with gaps in the ranking for ties , based on the total_purchases in descending order .

BY FROM AS ORDER sales sale_date row_num sale_date amount ROW_NUMBER() OVER SELECT

Problem 4 : Row Numbering

Question : Assign a unique row number to each sale in the sales table ordered by sale_date .

Solution :

____________________ ____________________ , ____________________ ,
____________________ ( ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

Explanation : ROW_NUMBER ( ) generates a unique number for each row , starting at 1 , based on the ordering of sale_date .

customer_id PARTITION purchases OVER purchase_date MIN customer_id first_purchase BY AS FROM SELECT

Problem 5 : Find the First Purchase Date for Each Customer
Question : Given a table purchases ( customer_id INT , purchase_date DATE ) , write a SQL query to find the first purchase date for each customer .

Solution :

____________________ ____________________ , ____________________ ( ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

Explanation : MIN ( ) window function is used here , partitioned by customer_id so that the minimum purchase date is calculated for each customer separately .

sale_date sale_date FROM BY OVER LAG 1 previous_day_amount AS sales_data LAG amount amount amount SELECT 1 ORDER ORDER change_in_amount sale_date BY amount AS OVER

The LAG function is very useful in scenarios where you need to compare successive entries or calculate differences between them . For example , calculating day - over - day sales changes :

SELECT sale_date ,
amount ,
LAG ( amount , 1 ) OVER ( ORDER BY sale_date ) AS previous_day_amount ,
amount - LAG ( amount , 1 ) OVER ( ORDER BY sale_date ) AS change_in_amount
FROM sales_data ;

____________________ ____________________ ,
____________________ ,
____________________ ( ____________________ , ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ) ____________________ ____________________ ,
____________________ - ____________________ ( ____________________ , ____________________ ) ____________________ ( ____________________ ____________________ ____________________ ) ____________________ ____________________
____________________ ____________________ ;

In this query , the change_in_amount field computes the difference in sales between consecutive days . If the LAG function references a row that doesn't exist ( e . g . , the first row in the dataset ) , it will return NULL unless a default value is specified .

The LAG window function in SQL is used to access data from a previous row in the same result set without the need for a self - join . It's a part of the SQL window functions that provide the ability to perform calculations across rows that are related to the current row . LAG is particularly useful for comparisons between records in ordered data .

How LAG Works :
LAG takes up to three arguments :

Expression : The column or expression you want to retrieve from a preceding row .
Offset : An optional integer specifying how many rows back from the current row the function should look . If not specified , the default is 1 , meaning the immediate previous row .
Default : An optional argument that provides a default value to return if the LAG function attempts to go beyond the first row of the dataset .
Syntax :
LAG ( expression , offset , default ) OVER ( [ PARTITION BY partition_expression ] ORDER BY sort_expression )