01 Aug 2024

Posted on:

21 Jul 2024

0

Calrify Exercise #1 implementation of row functions

The Exercise #1 question states the following for row_num2

- a column containing the sequential row numbers associated to the rows for each manager, where their highest salary has been given a number equal to the number of rows in the given partition, and their lowest - the number 1.

So row_num2 should have 1 for min salary
i.e. for emp_no 110022 and salary 108407 row_num2 should be 18

Can someone please explain which is wrong, solution provided or my understanding?

->

-> dm.emp_no,

->

->     salary,

->

->     ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY salary ASC) AS row_num1,

->

->     ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY salary DESC) AS row_num2

->

-> FROM

->

-> dept_manager dm

->

->     JOIN

->

->     salaries s ON dm.emp_no = s.emp_no;

+--------+--------+----------+----------+

| emp_no | salary | row_num1 | row_num2 |

+--------+--------+----------+----------+

| 110022 | 108407 |       18 |        1 |

| 110022 | 104485 |       17 |        2 |

| 110022 | 100592 |       16 |        3 |

| 110022 | 100014 |       15 |        4 |

| 110022 |  98843 |       14 |        5 |

| 110022 |  97604 |       13 |        6 |

| 110022 |  96647 |       12 |        7 |

| 110022 |  94286 |       11 |        8 |

| 110022 |  92165 |       10 |        9 |

| 110022 |  89204 |        9 |       10 |

| 110022 |  86797 |        8 |       11 |

| 110022 |  82871 |        7 |       12 |

| 110022 |  81784 |        6 |       13 |

| 110022 |  78443 |        5 |       14 |

| 110022 |  76211 |        4 |       15 |

| 110022 |  72970 |        3 |       16 |

| 110022 |  71820 |        2 |       17 |

| 110022 |  71166 |        1 |       18 |

Posted on:

21 Jul 2024

0

Exercise #1 solution is given Solution #2

Exercise #2 solution is given Solution #1

Instructor
Posted on:

01 Aug 2024

0

Hi Palachuri!

Thanks for reaching out and pointing this out. You are right - by mistake, we had swapped the two solutions!

We have updated the relevant *.sql solution which you should be able to download and use shortly.

Hope this helps.
Best,
Martin