how can you use rowid in bigquery to get the first value of your dataset by date and put all other values as 0 for a given day

Question

I have a dataset of 3 columns; date, sales and new_sales.

What i am trying to do in bigquery is for a given date, grab the first sales value and populate this into a new column called new_sales whilst leaving the rest of the days for that given date a value of 0.

How would i go about creating this query in bigquery?

Answer 1

You can use row_number() - but you nede a column that defines the ordering of rows having the same date - I assumed id:

select t.*,
    case when row_number() over(partition by date order by id) = 1 then sales end as new_sales
from mytable t

Answer 2

Here is an example i made earlier - it should work for you:

http://sqlfiddle.com/#!17/5c48e/8/0

Although this answer assumes that your sales values stay consistent on the dates and do not change, if they do change e.g. 12/10/2020 has two different dates then you would need to order by date.

my code is below:

CREATE TABLE links (
    date_item varchar(255),
    sales INT
);

INSERT INTO links (date_item, sales)
VALUES('12/10/2020',5), 
('12/10/2020',5),
('12/10/2020',5),
('13/10/2020',7),
('13/10/2020',7),
('13/10/2020',7),
('13/10/2020',7),
('13/10/2020',7),
('13/10/2020',7),
('13/10/2020',7), 
('14/10/2020',3),
('14/10/2020',3),
('14/10/2020',3);


select t.*,
    case when ROW_NUMBER () OVER (partition BY date_item) =1 then sales else 0 end as new_sales
from links as t