Spark Scala Capstone Boss Battle: Examples & Syntax

The dataset

The boss battle uses the orders DataFrame you now know cold:

ordersid, user_id, product, category, amount, qty, status, ordered_at

15.1

The brief

Priya is prepping the quarterly review. She wants the single best-selling product in each category — ranked by revenue, not just price — so she can feature the winners. "Revenue" means amount × qty, because selling four cheap cables can beat one pricey lamp.

This is a four-move pipeline, and you've drilled every move:

Signature

1. derive     amount * qty AS revenue          (Module 13 — expressions)
2. rank       row_number() OVER (PARTITION BY category ORDER BY revenue DESC)   (Module 7 — windows)
3. filter     keep rn = 1                       (Module 2 — filtering)
4. select     category, product, revenue        (Module 1 — columns)

This top-N-per-group shape — derive a metric, rank it inside a window, keep the top row — is one of the most common patterns in all of data engineering. Once it's muscle memory you'll reach for it constantly: top order per customer, latest event per session, best-selling SKU per store.

15.2

The Scala you're aiming for

Here's the full DataFrame version. Your mission below is to reproduce its result in the live SQL engine — but study the Scala first, because this is the shape you'll write on the job.

Scala

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions.row_number

val w = Window.partitionBy($"category").orderBy(($"amount" * $"qty").desc)

val topPerCategory = orders
  .withColumn("revenue", $"amount" * $"qty")          // 1. derive
  .withColumn("rn", row_number().over(w))              // 2. rank within category
  .filter($"rn" === 1)                                 // 3. keep the winner
  .select($"category", $"product", $"revenue")         // 4. project
  .orderBy($"category")

topPerCategory.show(false)

the target result — one top-revenue product per category

category	product	revenue
Electronics	Mechanical Keyboard	80.0
Home	Desk Lamp	32.0
Stationery	Notebook	19.5

baked output — Spark-only type, not runnable in the browser engine

Note why row_number and not max: a groupBy(category).agg(max(revenue)) gives you the winning number but loses the product name attached to it. The window keeps the whole row, so you get "which product" for free. That's the everyday reason windows beat plain aggregation for "the row where X is biggest".

15.3

★ Boss battle

Your turn. Reproduce the result above in the editor. You'll need a computed revenue = amount * qty, a row_number() window partitioned by category and ordered by that revenue descending, and a filter to rn = 1. The starter gives you the skeleton — fill in the window.

PriyaFounder · Kettle & Co.now

🏆 The big one. Give me the top-selling product in each category by revenue (revenue = amount × qty). I want three columns — category, product, revenue — one row per category, the winner only. Nail this and you've earned it. 🚀

🎯 Your mission From orders, return the single highest-revenue (amount × qty) product per category. Output columns: category, product, revenue — one row per category.

SQL

⌘/Ctrl + Enter

Partition the window by category and order by amount * qty DESC so rn = 1 is the top earner in each category.
The window is row_number() OVER (PARTITION BY category ORDER BY amount * qty DESC).
WITH ranked AS (SELECT category, product, amount * qty AS revenue, row_number() OVER (PARTITION BY category ORDER BY amount * qty DESC) AS rn FROM orders) SELECT category, product, revenue FROM ranked WHERE rn = 1 ORDER BY category;

15.4

You made it 🎉

That's the whole DataFrame API, end to end. You can now create DataFrames, reference and reshape columns, filter with conditional logic, wrangle strings, numbers, and dates, aggregate and window, unnest arrays/maps/structs/JSON, join, dedup, and read and write real formats — and you know when to reach for each one and where the sharp edges are.

Where to go next. Take these functions into production reading: PySpark DataFrame fundamentals, Spark join strategies, repartition vs coalesce, handling data skew, and executor tuning. Then go write a real pipeline — that's where it sticks.

Thanks for learning with SolutionGigs. If this handbook saved you a dozen Google searches, that was the whole point. Now go build something.

Frequently asked questions

How do I get the top product per category by revenue in Spark?

Derive revenue = amount * qty, add row_number() over a window partitioned by category and ordered by revenue descending, then filter to rn = 1:

orders.withColumn("revenue", $"amount" * $"qty").withColumn("rn", row_number().over(Window.partitionBy("category").orderBy($"revenue".desc))).filter($"rn" === 1)

. The window keeps the full row, so you retain the product name alongside the winning value.

Why use row_number instead of max for a top-N-per-group query in Spark?

A groupBy(...).agg(max(...)) returns only the aggregated value and loses the other columns of the winning row, such as the product name. A row_number() window ranks whole rows, so filtering to rn = 1 keeps the entire winning row — that's why windows are the standard tool for 'the row where X is largest'.

What order should I learn Spark DataFrame functions in?

Start with creating DataFrames and column basics, then filtering and conditional logic, then the scalar function families (strings, numbers, dates), then aggregations and window functions, then complex types (arrays, maps, structs, JSON), and finally joins, set operations, UDFs, and I/O. This handbook's modules follow exactly that path, ending in a capstone that chains them together.

Capstone Boss Battle