Reviewed by Aditya Kumar · Last reviewed 2026-03-24
from pyspark.sql import Window from pyspark.sql.functions import avg, col, row_number df = spark.table("employees").filter("age < 30") dept_avg = df.groupBy("dept_id").agg(avg("salary").alias("dept_avg_sal")) df2 = df.join(dept_avg, "dept_id").filter(col("salary") >...
from pyspark.sql import Window
from pyspark.sql.functions import avg, col, row_number
df = spark.table("employees").filter("age < 30")
dept_avg = df.groupBy("dept_id").agg(avg("salary").alias("dept_avg_sal"))
df2 = df.join(dept_avg, "dept_id").filter(col("salary") > col("dept_avg_sal"))
window = Window.partitionBy("dept_id").orderBy(col("salary").desc())
df2.withColumn("rn", row_number().over(window)).filter("rn <= 3").select("dept_id", "employee_id", "salary", "age").show() Why: Filter early; compute dept avg once; window for top-N. Scalability: Broadcast dept_avg if small; ensure even partition distribution.
Red Flag: Computing department average in UDF per row. Pro-Move: 'Join to pre-aggregated dept_avg; broadcast if <100 rows.'
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.