<jupyter_start><jupyter_text>Ranking using Windowing FunctionsLet us see how we can assign ranks using different **rank** functions.<jupyter_code>%%HTML
<iframe width="560" height="315" src="https://www.youtube.com/embed/NsqHuZbUrAw?rel=0&amp;controls=1&amp;showinfo=0" frameborder="0" allowfullscreen></iframe><jupyter_output><empty_output><jupyter_text>Let us start spark context for this Notebook so that we can execute the code provided. You can sign up for our [10 node state of the art cluster/labs](https://labs.itversity.com/plans) to learn Spark SQL using our unique integrated LMS.<jupyter_code>val username = System.getProperty("user.name")
import org.apache.spark.sql.SparkSession

val username = System.getProperty("user.name")
val spark = SparkSession.
    builder.
    config("spark.ui.port", "0").
    config("spark.sql.warehouse.dir", s"/user/${username}/warehouse").
    enableHiveSupport.
    appName(s"${username} | Spark SQL - Windowing Functions").
    master("yarn").
    getOrCreate<jupyter_output><empty_output><jupyter_text>If you are going to use CLIs, you can use Spark SQL using one of the 3 approaches.**Using Spark SQL**```spark2-sql \    --master yarn \    --conf spark.ui.port=0 \    --conf spark.sql.warehouse.dir=/user/${USER}/warehouse```**Using Scala**```spark2-shell \    --master yarn \    --conf spark.ui.port=0 \    --conf spark.sql.warehouse.dir=/user/${USER}/warehouse```**Using Pyspark**```pyspark2 \    --master yarn \    --conf spark.ui.port=0 \    --conf spark.sql.warehouse.dir=/user/${USER}/warehouse``` * If we have to assign ranks globally, we just need to specify **ORDER BY*** If we have to assign ranks with in a key then we need to specify **PARTITION BY** and then **ORDER BY**.* By default **ORDER BY** will sort the data in ascending order. We can change the order by passing **DESC** after order by.* We have 3 main functions to assign ranks - `rank`, `dense_rank` and `row_number`. We will see the difference between the 3 in a moment.Here is an example to assign sparse ranks using the table daily_product_revenue with in each day based on revenue. We can use `rank` function to assign sparse ranks.<jupyter_code>%%sql

USE itversity_retail
%%sql

SELECT t.*,
  rank() OVER (
    PARTITION BY order_date
    ORDER BY revenue DESC
  ) AS rnk
FROM daily_product_revenue t
ORDER BY order_date, revenue DESC
LIMIT 100<jupyter_output><empty_output><jupyter_text>```{note}Here is another example to assign ranks using employees data set with in each department. We can also use other functions such as `dense_rank` and `row_number` to assign ranks.```<jupyter_code>%%sql

USE itversity_hr
%%sql

SELECT
  employee_id,
  department_id,
  salary,
  rank() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) rnk,
  dense_rank() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) drnk,
  row_number() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) rn
FROM employees
ORDER BY department_id, salary DESC
spark.sql("""
SELECT
  employee_id,
  department_id,
  salary,
  rank() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) rnk,
  dense_rank() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) drnk,
  row_number() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC, employee_id
  ) rn
FROM employees
ORDER BY department_id, salary DESC
""").
    show(100, false)
%%sql

SELECT * FROM employees ORDER BY salary LIMIT 10<jupyter_output><empty_output><jupyter_text>```{note}Here is the example for global rank with out `PARTITION BY` clause.```<jupyter_code>%%sql

SELECT employee_id, salary,
    dense_rank() OVER (ORDER BY salary DESC) AS drnk
FROM employees<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10757291945410757, 'fraction_numerical': 0.008027829810008028, 'mean_word_length': 4.272214386459803, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '305593', 'license': 'MIT', 'n_tokens_mistral': 1229, 'n_tokens_neox': 1161, 'n_words': 403, 'path': '07_windowing_functions/07_ranking_using_windowing_functions.ipynb', 'repo_name': 'itversity/spark-sql', 'size': '3737'}