Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window frame
Analytic functions
26. Analytic functions – Exercise 4
ORDER BY PARTITION BY
Order of evaluation
Summary

Instruction

That was no challenge at all for you. Ok, the last analytic exercise.

Exercise

For each customer, show the following information: first_name, last_name, the sum of payments (AS sum_of_payments) for all single rentals and the sum of payments of the median customer in terms of the sum of payments (since there are 7 customers, pick the 4th customer as the median).

Stuck? Here's a hint!

That's a difficult one! You will need to use GROUP BY with first_name and last_name and ORDER BY SUM(payment_amount). Remember that NTH_VALUE requires two arguments and redefine the window frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Console

Code editor

Result

TableConsole