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 sales SUM amount amount SELECT AS sale_date running_total BY FROM OVER ORDER sale_date 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 BETWEEN AVG sales CURRENT OVER amount PRECEDING OVER BY sale_date BETWEEN FROM BY ORDER BETWEEN AVG ORDER ROW sale_date ROW sales ROWS PRECEDING SUM ROWS FOLLOWING FROM running_total OVER ROW current_avg amount SUM amount amount sales amount FROM SELECT CURRENT FROM amount AND as SELECT ROW FROM sale_date CURRENT sum_to_end AND FOLLOWING ROWS moving_avg moving_avg CURRENT BETWEEN 6 AND UNBOUNDED sale_date as 3 SELECT as sales amount as amount PRECEDING 3 BY AND as BETWEEN ROW sale_date OVER ORDER sale_date ORDER CURRENT sale_date BY AND BY SELECT UNBOUNDED AVG amount sale_date sales OVER ORDER sale_date ROWS ROWS 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 ORDER total_purchases DESC id customers FROM name RANK BY SELECT AS rank total_purchases OVER 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 sale_date ORDER row_num SELECT BY sale_date FROM AS ROW_NUMBER() OVER sales amount 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 MIN OVER BY first_purchase customer_id PARTITION customer_id AS FROM purchases purchase_date 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 . 6 LAG amount LAG BY OVER sale_date sales_data sale_date ORDER amount SELECT amount ORDER BY sale_date AS OVER previous_day_amount AS change_in_amount amount 1 1 FROM 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

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

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

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

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

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

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

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

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

MIN OVER BY first_purchase customer_id PARTITION customer_id AS FROM purchases purchase_date 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 .

6

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

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 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