Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Window Functions > Flashcards

Window Functions Flashcards

1
Q

What are the various window aggregate functions that can be used?

A
  • SUM
  • COUNT
  • AVG
  • MIN
  • MAX

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What are the three main parts in the over clause?

A
  • Partitioning
  • Ordering
  • Framing

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

When using OVER() with none of the three clauses it can take, what does it represent?

A

All of the rows in the query. You could use it for a grand total, for example.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What do UNBOUNDED PRECEEDING and UNBOUND FOLLOWING stand for?

A
  • UNBOUNDED PRECEEDING: Beginning of the partition
  • UNBOUNDED FOLLOWING: End of the partition

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What does <n> ROWS PRECEEDING and <n> ROWS FOLLOWING stand for?

A
  • <n> ROWS PRECEEDING: <n> rows before the current row
  • <n> ROWS FOLLOWING: <n> rows after the current row

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How does using window aggregate functions for running totals compare to using joins, subqueries or group aggregate functions in terms of performance?

A

It will perform better than the other three, especially when using UNBOUNDED PRECEEDING as the first delimiter.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What clause is required to use a framing clause?

A

An ORDER BY clause in the OVER clause

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What clause is mandatory when using a window ranking function?

A

ORDER BY within the OVER clause

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What cluase that can be used in OVER cannot be used when you use a ranking function?

A

A frame clause

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does the NTILE ranking function do?

A

Allows you to arrange rows within the partition into a requested number of tiles. The NTILE column will have the tile number for the particular row.

Ex.
830 rows total. You specify 100 tiles. Since 100 does not divide equally into 830, the first 30 tiles will have an extra row and the remaining tiles will have 8 rows.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

General form when using NTILE function.

A 
NTILE(n) OVER(ORDER BY val) AS NTILE

The NTILE function goes before the OVER clause
n=the number of tiles you want

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What could you do if you need to refer to a Window Aggregate function in a WHERE clause?

A

Create a CTE that creates the desired column using a window aggregate function. Refer to that column from the outer query.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the logical query processing order?

A
  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

If you use the LAG or LEAD functions and there is no row available for the offset you specified, what value is returned?

A

Say you specified LAG(val,2) but there is no row two rows before the current row, a null will be returned.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

In the framing clause, is it better to use RANGE or ROWS performance wise?

A
  • ROWS
  • Example: ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What main query clauses can a window function be in?

A
  1. SELECT
  2. ORDER BY

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is one difference between grouped queries and windowed queries with respect to how rows are treated?

A
  • Grouped queries arrange rows in groups
  • Windowed queries may apply a function to a group of rows but each individual row is still returned in the result set.

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

When using LEAD or LAG, if you don’t specify the # of rows to offset, what is the default?

A

It will offset 1 row

SQL Server 70-461 05-03

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

When a window frame is applicable to a function but you don’t include an explicit window frame clause, what is the default?

A

RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW

SQL Server 70-461 05-03

20
Q

Why is it important to explicitly define a window frame clause if one is applicable to a function?

A

If you don’t and you’re using the function LAST_VALUE, for example, to get the last row in the partition you won’t get the result you’re expecting since the default is RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW. Bottom line, you may get unexpected results.

Also, for performance it is preferred to use ROWS instead of RANGE.

SQL Server 70-461 05-03

21
Q

What is the general structure of the OVER clause?

A 
Aggregate Function(col_name) OVER (
PARTITION BY col_name
ORDER BY col_name
ROWS BETWEEN top delimiter
AND bottom delimiter
)
  • Partitioning PARTITION BY col_name
  • Ordering ORDER BY col_name
  • Framing ROWS BETWEEN top delimiter
    AND bottom delimiter
  • NOTE: col_name does not have to be the same in aggregate function, partition by and order by.

SQL Server 70-461 05-03

22
Q

Real example of OVER clause based on test data from the book

A 
SELECT
empid, 
ordermonth,
val,
SUM(val) OVER (PARTITION BY empid
ORDER BY ordermonth
ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
) AS runval
  • Partitioning PARTITION BY empid
  • Ordering ORDER BY ordermonth
  • Framing ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW

SQL Server 70-461 05-03

23
Q

What does the ORDER BY clause within the OVER clause do?

A

Orders data only for the sake of the computation, not for how the data result will be presented when it is done.

SQL Server 70-461 05-03

24
Q

What does the framing clause do?

A

It filters a subset of rows within the partition.

SQL Server 70-461 05-03

25
Q

General form when using RANK

A
  • It goes before an OVER clause
  • Example: RANK() OVER(ORDER BY val) AS rnk

SQL Server 70-461 05-03

26
Q

What does the DENSE_RANK ranking function do?

A

Returns the number of distinct ordering values that are lower than the current row plus 1.

Example: So if there were 9 rows with a value lower than the current row but the value in two of them was the same you would have 8 distinct values plus 1 for the DENSE_RANK of the current row.

SQL Server 70-461 05-03

27
Q

General form when using DENSE_RANK function

A
  • It goes before OVER clause
    ~~~
    DENSE_RANK OVER(ORDER BY val) AS densernk
    ~~~

SQL Server 70-461 05-03

28
Q

What clauses of the main query clauses are window function allowed in?

A
  1. SELECT
  2. ORDER BY

SQL Server 70-461 05-03

29
Q

What clause within the OVER clause can you not use with LAG and LEAD?

A
  • Cannot be used: You cannot use the framing clause
  • Can be used: You can use the partitioning and ordering clauses

SQL Server 70-461 05-03

30
Q

How do you define the rows a window aggregate function will be applied to

A

User the OVER clause

SQL Server 70-461 05-03

31
Q

What does the partitioning clause do?

A 
SUM(val) OVER(PARTITION BY empid) AS totalval
  • It tells you what rows the function should act on based on the current row
  • So in the above example, if the current row has empid of 1, then the sum of val for that row will only be the sum of the rows that also have an empid of 1.

SQL Server 70-461 05-03

32
Q

What is the general form of the framing clause?

A

ROWS BETWEEN <top delimiter> AND <bottom delimiter>

SQL Server 70-461 05-03

33
Q

What are the three delimiters that can be used in the framing clause?

A
  1. UNBOUNDED PRECEEDING OR UNBOUNDED FOLLOWING
  2. Current Row
  3. <n> ROWS PRECEEDING OR ROWS FOLLOWING

SQL Server 70-461 05-03

34
Q

What is a window ranking function?

A

It allows you to rank rows within a partition based on specified ordering.

SQL Server 70-461 05-03

35
Q

Do you have to include a partition clause when using a ranking function?

A

No. If you don’t indicate a partition, the entire result set of the query will be considered a partition.

SQL Server 70-461 05-03

36
Q

What are the four ranking functions?

A
  1. ROW_NUMBER
  2. RANK
  3. DENSE_RANK
  4. NTILE

SQL Server 70-461 05-03

37
Q

What does the ROW_NUMBER ranking function do?

A

It gives a unique sequential integer to each row in the specified partition based on the ordering in the over clause. Always starts with 1.

If no partition is indicated, the entire query result is considered one partition.

SQL Server 70-461 05-03

38
Q

General form when using ROW_NUMBER

A
  • It goes before the OVER clause
  • Example: ROW_NUMBER() OVER(ORDER BY val) AS rownum

SQL Server 70-461 05-03

39
Q

What does the RANK ranking function do?

A
  • For the current row, it returns the number of rows in the specified partition that have a lower ordering value plus 1.
  • Example: If the current row order by column has $45.00 and there are 9 rows that have a value less than $45.00, the RANK for the current row is 10.

SQL Server 70-461 05-03

40
Q

What are the 4 window offset functions?

A
  1. LAG
  2. LEAD
  3. FIRST_VALUE
  4. LAST_VALUE

SQL Server 70-461 05-03

41
Q

What clauses within the OVER clause can you use with FIRST_VALUE and LAST_VALUE?

A
  1. Partitioning
  2. Ordering
  3. Framing

SQL Server 70-461 05-03

42
Q

How does a window offset function work?

A

It returns an element from a single row that is
1. offset a specified number of rows from the current row
or
2. the first or last row in a window fram

SQL Server 70-461 05-03

43
Q

How do the LAG and LEAD functions work?

A
  • LAG: returns an element from the row in the current partition that is a requested number of rows before the current row
  • LEAD: returns an element from the row in the current partition that is a requested number of rows after the current row

SQL Server 70-461 05-03

44
Q

What is the general form of the LAG and LEAD functions?

A

LAG
LAG(val) OVER(PARTITION BY custid
ORDER BY orderdate,
orderid) AS prev_val

LEAD
LEAD(val) OVER(PARTITION BY custid
ORDER BY orderdate,
orderid) AS prev_val

SQL Server 70-461 05-03

45
Q

What are the three arguments the LEAD and LAG functions accept?

A
  1. element to return
  2. # rows to offset
  3. value to assign if null

LAG(element to return, # rows to offset, value to assign if null)

Example: LAG(val, 6, 0)
or
Example: LEAD(val, 6, 0)

SQL Server 70-461 05-03

46
Q

How do the FIRST_VALUE and LAST_VALUE functions work?

A
  • They return a value from the last or first row of a window frame.

SQL Server 70-461 05-03

47
Q

What is the general form when using FIRST_VALUE and LAST_VALUE

A

FIRST_VALUE
FIRST_VALUE(val) OVER(PARTITION BY custid,
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
) AS first_val

LAST_VALUE
LAST_VALUE(val) OVER(PARTITION BY custid,
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW
) AS last_val

SQL Server 70-461 05-03

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions