Reviewed by Aditya Kumar · Last reviewed 2026-03-24
Best 3 marks + average: WITH top3 AS (SELECT student_id, mark, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY mark DESC) rn FROM marks) SELECT student_id, SUM(mark)/3 AS best3_avg FROM top3 WHERE rn <= 3 GROUP BY student_id. Or use SUM with conditional: SUM(CASE WHEN rn<=3...
Best 3 marks + average: WITH top3 AS (SELECT student_id, mark, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY mark DESC) rn FROM marks) SELECT student_id, SUM(mark)/3 AS best3_avg FROM top3 WHERE rn <= 3 GROUP BY student_id. Or use SUM with conditional: SUM(CASE WHEN rn<=3 THEN mark END)/3. For overall average: AVG(mark). 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: Generic textbook answers. Pro-Move: 'At scale we measured X, implemented Y, achieved Z%—validated and iterated.'
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.