My LAG function not working as expected MySQL 8.x

Question

My table looks like this:

metro_region, value, date

with multiple values (one for each date) across the month of November. There are about 100 metro regions.

I want my report to have the following data:

Metro_region  Today  Yesterday  2daysAgo  3dayAgo
MetroRegionA   40.1    54.3       64.8     48.1
MetroRegionB   31.1    53.1       97.8     43.2

What I tried:

select 
  metro_region, 
  date,
  LAG(value,3) over (Partition by metro order by metro) as "3daysAgo", 
  LAG(value,2) over (Partition by metro order by metro) as "2daysAgo", 
  LAG(value,1) over (Partition by metro order by metro) as "Yesterday", 
  value as Today
from mytable 
where date = curdate();

I suspect I'm not partitioning properly...or merely grossly missing how to use LAG...any insights are appreciated!

Answer 1

You need results of today and the last 3 days, so you must not filter only for today.
First get the results for 4 days and then filter for today.
Also, in every partition you must sort by date and not metro:

select metro_region, date, `3daysAgo`, `2daysAgo`, `Yesterday`, Today
from (
  select metro_region, 
         date, 
         LAG(value,3) over (partition by metro_region order by date) as `3daysAgo`, 
         LAG(value,2) over (partition by metro_region order by date) as `2daysAgo`, 
         LAG(value,1) over (partition by metro_region order by date) as `Yesterday`, 
         value as Today 
  from mytable 
  where date between curdate() - interval 3 day and curdate()
) t
where date = curdate()

See a simplified demo.