Modify data in a specific format

I want to represent data in a specific format. Currently, the data looks like below-

product_id  order_id   product_type  day1_sale day2_sale  day3_sale  day4_sale
  123         456           A           null      0.2        0.3        null
  123         456           B           null      null       0.4        null
  111         222           A           null      null       null       null
  333         444           B            0.7      0.1        0.2        0.6

I want to represent it in the below format-

product_id  order_id   product_type   sale_day    %sales_on_day  
  123         456           A           day2          0.2      
  123         456           A           day3          0.3       
  123         456           B           day3          0.4      
  111         222           A           null          null
  333         444           B           day1          0.7
  333         444           B           day2          0.1
  333         444           B           day3          0.2
  333         444           B           day4          0.6

Is is there a way to get the data in this format?

Answer

You want to unpivot and filter. Here is a BigQuery’ish way to do this:

with t as (
      select 123 as product_id, 456 as order_id, 'A' as product_type, null as day1_sale, 0.2 as day2_sale, 0.3 as day3_sale, null as day4_sale UNION ALL
      select 123, 456, 'B', null,      null,       0.4,        null UNION ALL
      select 111, 222, 'A', null,      null,       null,       null UNION ALL
      select 333, 444, 'B', 0.7,      0.1,        0.2,        0.6
     )
select t.product_id, t.order_id, t.product_type, ds.*
from t cross join
     unnest(array[struct('1' as day, day1_sale as day_sale),
                  ('2', day2_sale),
                  ('3', day3_sale),
                  ('4', day4_sale)
                 ]
            ) ds
where day_sale is not null;