DataPlayArena
Interview Focus: DataFramesDifficulty: Medium

Monthly Sales Report

Estimated duration: 15 mins

Lab Overview & Scenario

Parse timestamp strings to extract year-month groups for chronological reporting.

Business Scenario

The accounting department needs monthly sales numbers. You must extract the year and month from raw purchase timestamps and calculate total sales grouped by month.

1. Local Raw Mock Dataset

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

order_idorder_dateamount
12026-01-15150.0
22026-01-20120.0
32026-02-05300.0
42026-02-18220.0
52026-03-12450.0
View Raw CSV Dataset
order_id,order_date,amount
1,2026-01-15,150.0
2,2026-01-20,120.0
3,2026-02-05,300.0
4,2026-02-18,220.0
5,2026-03-12,450.0

2. Your Tasks

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python
# starter.py - Monthly Sales Report
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date, date_format, sum

def monthly_sales():
    spark = SparkSession.builder.appName("MonthlySales").getOrCreate()
    # TODO: Read dataset.csv, cast dates, format to YYYY-MM, sum amount
    pass

if __name__ == "__main__":
    monthly_sales()

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
↓
Cast to Date type
↓
Format date to YYYY-MM
↓
Group & Sum Amount
↓
Display Output

Step-by-Step Implementation Guide

Step 1

Load the transactions dataset.

Step 2

Parse the order_date string column into a Date type using to_date(col("order_date"), "yyyy-MM-dd").

Step 3

Format the date column into yyyy-MM format string using date_format().

Step 4

Group the dataset by year_month, calculate the sum of transaction amounts, and sort sequentially.

Common Mistakes

  • Using python standard datetime libraries inside udf wrappers, which slows down execution significantly.
  • Using lowercase MM for minutes instead of uppercase MM for month formatting.

Interviewer's Expectations

Why this is asked:

Temporal transformations (date formats, time zones) are key parts of timeline metrics aggregation.

What they look for:

Proper application of Spark's built-in date formatting functions rather than custom python UDFs.

Common mistakes:

Using python imports (datetime) inside slow UDF loops.

An excellent answer includes:

Explain that native date functions run on the JVM without calling python subprocesses, avoiding serialization overhead.

✔ What You Learned

  • ✔Cast strings to Dates using to_date()
  • ✔Format timestamps using date_format()
  • ✔Chronologically group transactional data

Continue Learning

Previous LabEmployee Ranking DashboardRelated Theory LessonDefining SchemasNext LabDetect Duplicate Transactions