DataPlayArena
Interview Focus: DataFramesDifficulty: Easy

Department Salary Report

Estimated duration: 10 mins

Lab Overview & Scenario

Aggregate employee tables to generate department average and total salary summaries.

Business Scenario

The finance team is planning new budgets. They need a summary report containing the total salary spend and the average salary per department.

1. Local Raw Mock Dataset

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

employee_idnamedepartmentsalary
1AliceEngineering120000
2BobEngineering100000
3CharlieMarketing90000
4DavidSales85000
5EveMarketing95000
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

2. Your Tasks

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python
# starter.py - Department Salary Report
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, avg, sum

def generate_report():
    spark = SparkSession.builder.appName("DeptReport").getOrCreate()
    # TODO: Read dataset.csv, group by department, aggregate sum and avg
    pass

if __name__ == "__main__":
    generate_report()

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
↓
Group by department
↓
Aggregate sum & avg
↓
Display Output

Step-by-Step Implementation Guide

Step 1

Load the employees dataset with types inferred.

Step 2

Call groupBy("department") to group rows sharing the same department value.

Step 3

Apply .agg() method combining sum("salary") and avg("salary"). Use .alias() to rename the output columns for cleaner reports.

Step 4

Print the department statistics.

Common Mistakes

  • Trying to select non-aggregated columns that are not included in the groupBy clause.
  • Using raw Python sum() or average() instead of importing sum and avg from pyspark.sql.functions.

Interviewer's Expectations

Why this is asked:

Aggregations are the most common operation used in metric reports.

What they look for:

Clean usage of groupBy and .agg() syntax.

Common mistakes:

Confusing python built-in sum() with Spark's distributed column sum function.

An excellent answer includes:

Explain that groupBy triggers a shuffle operation where data is partitioned by keys across nodes.

✔ What You Learned

  • ✔Group records using groupBy()
  • ✔Run multi-aggregate functions using .agg()
  • ✔Alias aggregated output fields

Continue Learning

Previous LabProduct Price AnalysisRelated Theory LessonAggregationsNext LabTop Paid Employees