Interview Focus: Window FunctionsDifficulty: Medium

Employee Ranking Dashboard

Estimated duration: 15 mins

Lab Overview & Scenario

Use Window functions to calculate salary rankings within departments.

Business Scenario

The compensation committee wants to rank employees by salary within each department. You need to assign dense ranks starting at 1 for the highest earners in each department.

1. Local Raw Mock Dataset

The processing pipeline will ingest the following local mock file structures (e.g. simulated as `dataset.csv`):

employee_id	name	department	salary
1	Alice	Engineering	120000
2	Bob	Engineering	100000
3	Charlie	Marketing	90000
4	David	Sales	85000
5	Eve	Marketing	95000
6	Frank	Engineering	120000

View Raw CSV Dataset

employee_id,name,department,salary
1,Alice,Engineering,120000
2,Bob,Engineering,100000
3,Charlie,Marketing,90000
4,David,Sales,85000
5,Eve,Marketing,95000
6,Frank,Engineering,120000

2. Your Tasks

Read the raw employee dataset.

Define a Window specification partitioned by department and sorted by salary in descending order.

Calculate the dense rank of employees in the window.

Display name, department, salary, and salary_rank.

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python

# starter.py - Employee Ranking Dashboard
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, dense_rank

def rank_employees():
    spark = SparkSession.builder.appName("EmployeeRanking").getOrCreate()
    # TODO: Read dataset.csv, create window spec, apply dense_rank()
    pass

if __name__ == "__main__":
    rank_employees()

4. Interactive Solution & Execution Output

Reveal Solution Pipeline Code

Implement the correct transformations using PySpark to achieve the aggregated values metrics output.

Pipeline Flow

dataset.csv

↓

Read CSV

↓

Define Window Partition/Order

↓

Apply dense_rank() over window

↓

Display Output

Step-by-Step Implementation Guide

Step 1

Load the employee CSV file.

Step 2

Define the Window boundary rules: partition rows by department and order them by salary descending.

Step 3

Add the salary_rank column calling dense_rank().over(windowSpec). Using dense rank guarantees that employees with identical salaries receive the same rank without skipping consecutive rank numbers.

Step 4

Select fields and display the ranking.

Common Mistakes

Using rank() instead of dense_rank(), which would skip ranks (e.g. 1, 1, 3) in case of ties.
Forgetting to import Window from pyspark.sql.window.

Interviewer's Expectations

Why this is asked:

Window operations are the most frequently tested intermediate Spark topic in design loops.

What they look for:

Familiarity with partitioning and ordering specifications within Windows.

Common mistakes:

Using standard rank() instead of dense_rank() when consecutive ranks are required.

An excellent answer includes:

Explain the difference between rank, dense_rank, and row_number, and mention how partitioning triggers data shuffling.

✔ What You Learned

✔Create window partitions using Window.partitionBy()
✔Sort data inside windows using Window.orderBy()
✔Assign ranks using dense_rank().over()

Continue Learning

Previous LabCustomer Order Analytics Related Theory LessonWindow Functions: ROW_NUMBER/RANK Next LabMonthly Sales Report