Fill in the Blanks: Mastering SQL Window Functions


Fill in the Blanks Mastering SQL Window FunctionsOnline version Drills to master window functions in SQL by Good Sam 1 amount running_total ORDER sale_date SUM sales OVER FROM AS BY SELECT sale_date amount 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 : , , ( ) ( ) ; 2 ROWS ROW OVER sale_date SELECT FROM PRECEDING FROM SELECT sales sales amount UNBOUNDED SUM 3 FROM sales sale_date ORDER ROW amount BETWEEN AND sale_date ORDER AVG CURRENT amount sale_date sum_to_end running_total OVER BETWEEN BY ORDER sale_date OVER moving_avg AND BY PRECEDING BY CURRENT amount BETWEEN ROW sale_date AND OVER UNBOUNDED ROWS as ROWS amount 6 moving_avg CURRENT CURRENT sales ROW BETWEEN ORDER BY FOLLOWING FROM ROW AVG PRECEDING AND amount as AND sale_date BY as sale_date ROWS ORDER sale_date FOLLOWING BETWEEN as amount SELECT SELECT 3 SUM as current_avg ROWS CURRENT FROM sales OVER amount AVG amount 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 . 3 total_purchases RANK ORDER AS customers FROM total_purchases BY DESC id OVER name SELECT rank 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 . 4 ROW_NUMBER() OVER row_num amount SELECT ORDER sales BY sale_date AS sale_date FROM 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 . 5 customer_id purchases customer_id AS first_purchase SELECT OVER BY MIN FROM PARTITION purchase_date 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 . 6 sales_data OVER change_in_amount 1 sale_date FROM 1 sale_date BY AS amount BY SELECT amount LAG sale_date LAG previous_day_amount AS ORDER amount OVER amount ORDER 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 )

1

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

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 :

, ,
( ) ( )
;

2

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

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 .

3

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

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 .

4

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

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 .

5

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

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 .

6

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

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 )

You can integrate the game into an LMS compatible with LTI 1.1 or LTI 1.3 such as Canvas, Moodle, or Blackboard. This way, the scores will be automatically saved into the platform’s gradebook.

Mastering SQL Window Functions