Completar frases: Mastering SQL Window Functions


Completar frases Mastering SQL Window FunctionsVersión en línea Drills to master window functions in SQL por Good Sam 1 running_total amount SUM sales SELECT FROM OVER ORDER sale_date BY AS 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 6 amount as BY CURRENT as FROM sales AVG sales 3 sale_date BETWEEN FOLLOWING ROW amount ROW PRECEDING amount CURRENT ROWS amount AVG SELECT sale_date OVER OVER amount AND BETWEEN amount AND ROW sale_date as ORDER BY BETWEEN running_total FROM sum_to_end PRECEDING sales ROW amount FROM sale_date BY SELECT current_avg UNBOUNDED AND ORDER BETWEEN SUM ROWS amount BY OVER BETWEEN FOLLOWING ORDER ROWS sale_date ORDER OVER sale_date AND sale_date ROW SELECT CURRENT amount CURRENT sales ROWS 3 moving_avg as OVER moving_avg FROM SELECT as ORDER sales PRECEDING BY sale_date AVG SUM ROWS CURRENT UNBOUNDED AND sale_date FROM 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 name total_purchases AS FROM RANK DESC rank ORDER customers OVER BY SELECT id total_purchases 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 BY sale_date SELECT sale_date row_num sales amount FROM AS ORDER ROW_NUMBER() OVER 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 FROM customer_id BY OVER first_purchase PARTITION SELECT purchase_date AS MIN customer_id purchases 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 ORDER sale_date OVER LAG amount AS sale_date ORDER AS 1 LAG sales_data FROM OVER amount amount SELECT amount change_in_amount BY 1 BY sale_date previous_day_amount 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

running_total amount SUM sales SELECT FROM OVER ORDER sale_date BY AS 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

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

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

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

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

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

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

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

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

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

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 )

Mastering SQL Window Functions

Usa estas credenciales para integrar el juego en un LMS compatible con LTI 1.1 o LTI 1.3 como Canvas, Moodle, o Blackboard. De esta manera podrás guardar las puntuaciones automáticamente en el libro de calificaciones de esa plataforma.

Mastering SQL Window Functions