Icon Crear Crear

Mastering SQL Window Functions

Completar frases

Drills to master window functions in SQL

Descarga la versión para jugar en papel

0 veces realizada

Creada por

Estados Unidos

Top 10 resultados

Todavía no hay resultados para este juego. ¡Sé el primero en aparecer en el ranking! para identificarte.
Crea tu propio juego gratis desde nuestro creador de juegos
Compite contra tus amigos para ver quien consigue la mejor puntuación en esta actividad

Top juegos

  1. tiempo
    puntuacion
  1. tiempo
    puntuacion
tiempo
puntuacion
tiempo
puntuacion
 
game-icon

Completar frases

Mastering SQL Window FunctionsVersión en línea

Drills to master window functions in SQL

por Good Sam
1

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

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 CURRENT amount as amount ROWS UNBOUNDED sale_date moving_avg sales ORDER sum_to_end sale_date BY AND FROM AND sales OVER as sale_date 6 UNBOUNDED amount sales SELECT amount ROW current_avg BETWEEN amount SELECT sale_date FOLLOWING ROWS sales sales ROW OVER OVER ORDER ROW amount BETWEEN CURRENT CURRENT ROW FROM ROWS SUM as AND FROM CURRENT AVG BETWEEN sale_date BY PRECEDING BY running_total sale_date 3 FROM FROM ROWS ORDER as amount AVG ROW as PRECEDING CURRENT ORDER amount OVER AND SUM FOLLOWING amount sale_date BY AVG OVER BETWEEN 3 BY ORDER BETWEEN PRECEDING AND SELECT sale_date moving_avg sale_date SELECT

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

RANK FROM BY AS name rank ORDER id DESC OVER SELECT total_purchases customers 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

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

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

LAG amount 1 sale_date previous_day_amount amount LAG BY BY ORDER 1 sale_date amount sales_data ORDER sale_date AS AS SELECT change_in_amount FROM amount OVER 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 )


educaplay suscripción