← Problems89. Unpivot quarterly targetsMediumPySpark
00:00 / 20:00

Unpivot quarterly targets

MediumNot attempted yetAsked at Deloitte, Microsoft, TCS

Planning maintains targets in a spreadsheet, one column per quarter. The warehouse wants them long, not wide.

Input

targets region string q1 double -- null where no target was set q2 double q3 double q4 double

Output

One row per region and quarter that has a target, with the columns:

  • region
  • quarter — 'Q1', 'Q2', 'Q3' or 'Q4'
  • target

A quarter with no target produces no row. A region with no targets at all produces none.

Example

regionq1q2q3q4
East5.0null7.0null
regionquartertarget
EastQ15.0
EastQ37.0

Constraints

  • A target of 0.0 is a target somebody set. Only null means unset.
  • Read the table once. Four selects unioned together read it four times for the same rows.
  • Row order is not graded. Column names and their order are.

Example 1two regions with a full year each

Input · targets
q1q2q3q4region
10203040North
1234South
Output
regionquartertarget
NorthQ110
NorthQ220
NorthQ330
NorthQ440
SouthQ11
SouthQ22
2 more rows in this case

Example 2a quarter with no target set produces no row

Input · targets
q1q2q3q4region
5null7nullEast
1.52.53.54.5West
Output
regionquartertarget
EastQ15
EastQ37
WestQ11.5
WestQ22.5
WestQ33.5
WestQ44.5

Topics

aggregationdata quality

Similar problems

Community-reported interview topic. Not an official company question and no affiliation is implied.

solution.py
Loading editor…
Draft not saved yet · Spaces 4 · UTF-8⌘↵ run ⌘⇧↵ submit
Nothing run yet

Run against the public tests, or submit to score against all of them.