44. Survey Scores by Question

Difficulty: Medium · Topics: Pivot, Unpivot & Rollup, Aggregations

survey has one row per respondent and one column per question: respondent, q1, q2, q3, each a score from 1 to 5, or null if the question was skipped.

Return one row per question with question ('q1', 'q2', 'q3'), answered (how many respondents answered it) and avg_score (average of the answers, rounded to 2 decimals). Skipped questions do not count as answers. Sort by question.

Row order matters for this problem. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

survey

respondent	q1	q2	q3
r1	5	4	null
r2	3	null	null
r3	4	5	2
r4	null	4	1

Expected output

question	answered	avg_score
q1	3	4
q2	3	4.33
q3	2	1.5

Hints

Hint 1

Reshape wide to long first: survey.unpivot("respondent", ["q1", "q2", "q3"], "question", "score").

Hint 2

count("score") and avg("score") both ignore nulls, which is exactly what "skipped" means here.

Hint 3

A question nobody answered still appears, with 0 answers and a null average.

Learn the concepts

unpivot · 3 min read. Turn columns into rows (wide to long) with unpivot or the SQL stack() function.
groupBy + agg · 3 min read. Aggregate rows per group: counts, sums, averages, and how count(*) differs from count(col).
orderBy · 3 min read. Sort rows ascending or descending, control where nulls go, and know what a global sort costs.

PySpark functions you'll practise

groupBy
agg
avg
count
unpivot
orderBy

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · Learn · All problems