Common PySpark Coding Patterns

Apache Spark · Interview Prep

Theory

Live coding rounds reuse a small set of patterns, and knowing them by heart saves time. Top-N or Nth highest per group: dense_rank over a window, then filter. Latest record per key: row_number ordered by timestamp desc, keep 1. Running totals and moving averages: sum/avg over a framed window. Rows in A not in B: a left_anti join (and left_semi for 'rows in A that have a match'). Pivot: groupBy(...).pivot(col).agg(...) to turn values into columns.

A classic harder one is gaps and islands, such as the longest streak of consecutive login days. Give each user's distinct login dates a row_number in date order; subtracting (row_number - 1) days from the date gives the same anchor date for every day in an unbroken streak. Grouping by user and anchor then counts the streak length. For example logins on 1, 2, 3 and 5 January get row numbers 1 to 4 and anchors 1, 1, 1 and 2 January, so the streaks are 3 days and 1 day.

Why we use it

Coding rounds are timed, and most questions are variations of these patterns. Recognising the pattern quickly lets you write a correct, efficient DataFrame solution and spend the remaining time on edge cases such as ties, nulls and duplicates.

When to use it

Practise these until you can write them without looking up syntax, in both the DataFrame API and Spark SQL. In an interview, state the pattern first ("this is latest-record per key"), then code it, then mention tie and null handling.

How it works

  1. Read the problem and spot the pattern
  2. Does it need row context (rank, previous, running)? Yes: Window function with the right partition and order. No: groupBy, join or pivot.
  3. Write the DataFrame code step by step with clear aliases
  4. Handle duplicates, ties and nulls
  5. Test on a tiny DataFrame with show()
  6. Explain complexity: which steps shuffle

Syntax

dense_rank().over(Window.partitionBy(g).orderBy(F.desc(v))) == 2   # 2nd highest
a.join(b, "key", "left_anti")                                     # in a, not in b
df.groupBy("region").pivot("product").agg(F.sum("amount"))
F.date_sub("d", F.row_number().over(w) - 1)                         # streak anchor

Example

from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window

spark = SparkSession.builder.appName("techtown-spark").getOrCreate()

orders = spark.createDataFrame([
    (1, 101, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (2, 102, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (3, 101, "North", "Mouse",  3,  1500.0, "2026-01-07"),
    (4, 103, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (5, 102, "West",  "Tablet", 1, 25000.0, "2026-01-09"),
    (6, 104, "North", "Phone",  1, 18000.0, "2026-01-10"),
    (7, 103, "South", "Mouse",  2,  1000.0, "2026-01-11"),
    (8, 105, "West",  "Laptop", 1, 60000.0, "2026-01-12"),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

customers = spark.createDataFrame([
    (101, "Asha",  "Jaipur", "Retail"),
    (102, "Ravi",  "Pune",   "Corporate"),
    (103, "Meera", "Delhi",  "Retail"),
    (104, "Karan", "Jaipur", "SMB"),
    (105, "Priya", "Mumbai", "Corporate"),
], "customer_id INT, name STRING, city STRING, segment STRING")

# 1. Second-highest order amount per region (ties share the place)
w = Window.partitionBy("region").orderBy(F.desc("amount"))
(orders.withColumn("dr", F.dense_rank().over(w)).filter("dr = 2")
       .select("region", "order_id", "amount").orderBy("region").show())

# 2. Customers with no orders on or after 2026-01-10 (left_anti)
recent = orders.filter(F.col("order_date") >= "2026-01-10")
customers.join(recent, "customer_id", "left_anti").select("customer_id", "name").orderBy("customer_id").show()

# 3. Pivot: sales per region and product
orders.groupBy("region").pivot("product").agg(F.sum("amount")).orderBy("region").show()

# 4. Gaps and islands: longest login streak per user
logins = spark.createDataFrame([
    (101, "2026-01-01"), (101, "2026-01-02"), (101, "2026-01-03"), (101, "2026-01-05"),
    (102, "2026-01-02"), (102, "2026-01-04"), (102, "2026-01-05"),
], "user_id INT, login_date STRING").withColumn("login_date", F.to_date("login_date"))

wd = Window.partitionBy("user_id").orderBy("login_date")
streaks = (logins.dropDuplicates(["user_id", "login_date"])
    .withColumn("anchor", F.date_sub("login_date", F.row_number().over(wd) - 1))
    .groupBy("user_id", "anchor")
    .agg(F.min("login_date").alias("start"), F.max("login_date").alias("end"), F.count("*").alias("days")))
streaks.orderBy("user_id", "start").show()
streaks.groupBy("user_id").agg(F.max("days").alias("longest_streak")).orderBy("user_id").show()

# Output:
# +------+--------+-------+
# |region|order_id| amount|
# +------+--------+-------+
# | North|       6|18000.0|
# | South|       7| 1000.0|
# |  West|       2|30000.0|
# +------+--------+-------+
#
# +-----------+----+
# |customer_id|name|
# +-----------+----+
# |        101|Asha|
# |        102|Ravi|
# +-----------+----+
#
# +------+-------+------+-------+-------+
# |region| Laptop| Mouse|  Phone| Tablet|
# +------+-------+------+-------+-------+
# | North|55000.0|1500.0|18000.0|   NULL|
# | South|52000.0|1000.0|   NULL|   NULL|
# |  West|60000.0|  NULL|30000.0|25000.0|
# +------+-------+------+-------+-------+
#
# +-------+----------+----------+----------+----+
# |user_id|    anchor|     start|       end|days|
# +-------+----------+----------+----------+----+
# |    101|2026-01-01|2026-01-01|2026-01-03|   3|
# |    101|2026-01-02|2026-01-05|2026-01-05|   1|
# |    102|2026-01-02|2026-01-02|2026-01-02|   1|
# |    102|2026-01-03|2026-01-04|2026-01-05|   2|
# +-------+----------+----------+----------+----+
#
# +-------+--------------+
# |user_id|longest_streak|
# +-------+--------------+
# |    101|             3|
# |    102|             2|
# +-------+--------------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic