Interview Focus: DataFramesDifficulty: Medium

Customer Order Analytics

Estimated duration: 15 mins

Lab Overview & Scenario

Join customer profiles with order histories and run multi-column groupings.

Business Scenario

The sales division wants to know who their top local buyers are. Generate a report of total purchase amounts grouped by customer name and order date.

1. Local Raw Mock Dataset

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

customer_id	customer_name	country
1	Alice	USA
2	Bob	UK
3	Charlie	USA
---
order_id	customer_id	order_date	amount
901	1	2026-07-01	150.0
902	2	2026-07-01	200.0
903	1	2026-07-02	300.0
904	3	2026-07-02	120.0
905	2	2026-07-02	80.0

View Raw CSV Dataset

customer_id,customer_name,country
1,Alice,USA
2,Bob,UK
3,Charlie,USA
---
order_id,customer_id,order_date,amount
901,1,2026-07-01,150.0
902,2,2026-07-01,200.0
903,1,2026-07-02,300.0
904,3,2026-07-02,120.0
905,2,2026-07-02,80.0

2. Your Tasks

Create customers and orders DataFrames from memory data.

Join both DataFrames using customer_id.

Group by customer_name and order_date.

Calculate the sum of transaction amounts.

Sort results by total purchase amount in descending order.

3. Pipeline Skeleton Script

Copy this starter template to write the data transformation logic:

python

# starter.py - Customer Order Analytics
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum

def analyze_orders():
    spark = SparkSession.builder.appName("OrderAnalytics").getOrCreate()
    
    cust_data = [(1, "Alice", "USA"), (2, "Bob", "UK"), (3, "Charlie", "USA")]
    order_data = [
        (901, 1, "2026-07-01", 150.0),
        (902, 2, "2026-07-01", 200.0),
        (903, 1, "2026-07-02", 300.0),
        (904, 3, "2026-07-02", 120.0),
        (905, 2, "2026-07-02", 80.0)
    ]
    # TODO: Perform Join, Group by name & date, Sum amounts, Sort DESC
    pass

if __name__ == "__main__":
    analyze_orders()

4. Interactive Solution & Execution Output

Reveal Solution Pipeline Code

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

Pipeline Flow

Raw Mock Lists

↓

Create DataFrames

↓

Inner Join on ID

↓

Group by Name, Date

↓

Sum & Sort DESC

↓

Display Output

Step-by-Step Implementation Guide

Step 1

Initialize customer and order DataFrames from mock list data.

Step 2

Join the DataFrames using customer_id.

Step 3

Group the joined records using multiple columns: groupBy("customer_name", "order_date").

Step 4

Calculate total transaction sum using sum("amount"), rename it as total_purchased, and sort by that field descending.

Common Mistakes

Trying to group on customer_id but selecting customer_name, which raises aggregation errors in standard SQL/Spark environments.
Using outer joins when matches on both tables are required.

Interviewer's Expectations

Why this is asked:

Tests multi-table joins followed by complex multi-dimensional groupings.

What they look for:

Appropriate join keys configuration and multi-column groupBy logic.

Common mistakes:

Missing join keys or incorrectly specifying grouping keys.

An excellent answer includes:

Explain how multi-column grouping increases cardinatility and causes shuffles across cluster executors.

✔ What You Learned

✔Join multiple DataFrames on join keys
✔Aggregate data across multiple grouping columns
✔Sort grouped aggregate outputs

Continue Learning

Previous LabEmployee Bonus Calculator Related Theory LessonJoins Next LabEmployee Ranking Dashboard