Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Window example—rank per group: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees. PySpark: w = Window.partitionBy('dept').orderBy(col('salary').desc()); df.withColumn('rn', row_number().over(w)). Use for: top-N, dedup, running metrics....
Window example—rank per group: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees. PySpark: w = Window.partitionBy('dept').orderBy(col('salary').desc()); df.withColumn('rn', row_number().over(w)). Use for: top-N, dedup, running metrics. Combine with filter: WHERE rn <= 3. Why it matters: Design choices compound at scale—wrong approach can cause 100× overhead. Scalability trade-offs: Profile before optimizing; validate on sample then full. Cost implications: Suboptimal choices multiply at billion-row scale.
Red Flag: Arbitrary partition counts. Pro-Move: 'Partitions = size/150MB; we monitored Spark UI for skew, adjusted when P99 > 2× median.'
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.