Getting data from current date and same date last year

Question

I spent some hours searching and coding but i am not able to find a solution. I have a table with dates (format yyyy-mm-dd) and payment value. Each row after agregating has only one date e related values. I need generate a resultset with the folowing format: [date] [value from current date] [value from date exact last year] [value from same day from last month]

Anyone could help me? My DB is BigQuery.

`--Didn't work select current.date, previous.date --extract(year from previous.date) -1

from f_table.agg current left join f_table.agg previous on extract(day from current.date) = extract(day from previous.date) and (extract(month from current.date) = extract(month from previous.date) and extract(year from current.date) = (extract(year from previous.date) - 1) )`

--Didn't work select current.date, sum(current.value) --lag( sum(current.value), 1) over (order by extract(year from current.date))

from f_table.agg current

group by current.date order by current.date desc

Answer 1

I need generate a resultset with the folowing format: date value from current date value from date exact last year value from same day from last month

I think you can try self join like below.

-- sample data
WITH `f_table.agg` AS (
  SELECT DATE '2022-04-15' date, 10 value UNION ALL
  SELECT DATE '2022-04-29', 20 UNION ALL
  SELECT DATE '2023-03-15', 30 UNION ALL
  SELECT DATE '2023-04-15', 40 UNION ALL
  SELECT DATE '2023-04-27', 50 UNION ALL
  SELECT DATE '2023-04-28', 60 UNION ALL
  SELECT DATE '2023-04-29', 70
)
-- query starts here
SELECT cur.date, cur.value,
       last_year.value AS last_year_value,
       last_month.value AS last_month_value
  FROM `f_table.agg` cur
  LEFT JOIN `f_table.agg` last_year ON cur.date - INTERVAL 1 YEAR = last_year.date
  LEFT JOIN `f_table.agg` last_month ON cur.date - INTERVAL 1 MONTH = last_month.date;

Query result