SQL Partition by on all dates of the past month

Question

This is my table:

Id	col	old	new	time
39	stat	A	B	2022-01-01 10:21:59 UTC
23	stat	B	C	2022-01-01 18:21:59 UTC
68	stat	B	C	2022-01-02 20:21:59 UTC
39	stat	B	C	2022-01-02 21:21:59 UTC

What I want is to get all the ids with new = C as the latest entry for that id for all days i.e. for 2022-01-01, 2022-01-02, 2022-01-03, ... . So basically how many Ids had new = C by 2022-01-01, by 2022-01-02, by 2022-01-03, and so on. Now in order to get the latest change, I was using:

SELECT Id,time
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY time DESC) as rn 
      FROM table where col = 'stat')
Where new = 'C' and rn = 1

What I can't understand is how to get the change count for every day in the past (starting from a particular day) with that day being the latest change time. So what I want is:

time	Id
2022-01-01	23
2022-01-02	68
2022-01-02	39

Thanks in advance.

Answer 1

EDIT: Ok, I'd not seen Google-BigQuery before, did a quick lookup for a demo, and now I see why you provided the data in the manner that you did. I'm thinking that you could still provide at least a CSV for someone to import but I don't know for sure.

First (considering the edit above), I don't know about "Google-BigQuery" and so I don't know if the following will work but this is how I'd do it in SQL Server. Perhaps the example can help you science it out in Google-BigQuery, which should have similar capabilities. So far, you hadn't gotten any answers and so I hope this helps a bit.

Second, to help you keep from getting down-votes on your questions and to help us help you better, always post your sample data as "Readily Consumable" data instead of a paste job from a spreadsheet. Here's one example of to do that. (EDIT: They probably don't have CONVERT but there should be something close and you'll probably need to convert the IIF's to CASE WHEN THEN ELSE END.)

 SELECT *
   INTO dbo.SomeTable
   FROM (VALUES
         (39,'stat','A','B',CONVERT(DATETIME,'2022-01-01 10:21:59')) 
        ,(23,'stat','B','C',CONVERT(DATETIME,'2022-01-01 18:21:59'))
        ,(68,'stat','B','C',CONVERT(DATETIME,'2022-01-02 20:21:59'))
        ,(39,'stat','B','C',CONVERT(DATETIME,'2022-01-02 21:21:59'))
        )v(Id,col,old,new,time)
;

And, here's my answer from SQL Server. Again, I don't know if it'll work for you in Google-BigQuery but the should be enough similarities for you to figure it out.

   WITH CTE AS
(
 SELECT [time] = CONVERT(DATE,[time])
        ,Id
        ,RowNum = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY [time] DESC)
   FROM dbo.SomeTable
  WHERE New = 'C'
)
 SELECT *
   FROM CTE
  WHERE RowNum = 1
  ORDER BY [time],Id
;