How can I retrieve the first date and last date with windows function?

Question

I have some data like(The table has more columns, this is a sample output):

And I want to retrieve the factory_id, first_read_date, last_read_date and total_count. (first_read_date, last_read_date are based on factory_id) I tried to use windows function but the last read_date is not correct. Here is what I did:

with temptable as(
select
factory_id,
read_date,
rank() over(partition by factory_id order by read_date asc) as Ranked_first_read_date,
rank() over(partition by factory_id order by read_date desc) as Ranked_last_read_data,
concat(read_date,' _ ',rank() over(partition by factory_id order by read_date asc)) as first_read_date ,
concat(read_date,' _ ',rank() over(partition by factory_id order by read_date desc)) as last_read_date,
count(*) over() as total_count
from TestDatabase.sensor_data
order by factory_id,read_date)
select * from temptable a
where Ranked_first_read_date=1 and 
Ranked_last_read_data=(select max(b.Ranked_last_read_data) from temptable b where a.factory_id=b.factory_id group by factory_id)

I am using BigQuery. Any help would be greatly appreciated.

Answer 1

You should try executing following query:-

select factory_id , min(read_date) as first_read_date, max (read_date) as last_read_date  , count(*) as total_count_per_FacID,
(select count(*) from table) as total
FROM table
group by factory_id