How to create a new (date) column based on another column with date in BigQuery for comparison dates?

Question

I would like to create a table with the visits (Google Analytics) of a certain date, where I add the visits from the previous year (minus 364 days) to compare these days.

In the first column I have extracted the date and added the totals.visits as sessions (which works). I want to add the date of the previous year, so that I can left join a second table with those dates.

My first output is as follows:

Date this year	Visits this year
2023-01-10	100
2023-01-11	200

And I'm trying to retrieve this result:

Date this year	Visits this year	Date last year	Visits last year
2023-01-10	100	2022-01-11	150
2023-01-11	200	2022-01-12	210

To add the date of last year, I tried DATE_SUB() with 'date', but it doesn't work. I get the following error;

No matching signature for function DATE_SUB for argument types: STRING, INTERVAL INT64 DATE_TIME_PART. Supported signatures: DATE_SUB(DATE, INTERVAL INT64 DATE_TIME_PART); DATE_SUB(DATETIME, INTERVAL INT64 DATE_TIME_PART); DATE_SUB(TIMESTAMP, INTERVAL INT64 DATE_TIME_PART)

SELECT
  *
FROM (
  WITH
    variable AS (
    SELECT
      '20230110' AS start_date,
      '20230126' AS end_date ) (
    SELECT
      date,
      SUM(totals.visits) AS sessions,
      DATE_SUB(date, INTERVAL 364 DAY) AS date_lastyear
    FROM
      `my_table`,
      variable
    WHERE
      date >= start_date
      AND date <= end_date
    GROUP BY
      date ) ) 

Does anyone know how to use DATE_SUB() based on a date in a row? OR any other way to get the result I want?

Answer 1

Let's debug

No matching signature for function DATE_SUB for argument types: STRING, INTERVAL INT64 DATE_TIME_PART.

It says there is no function such as date sub which takes a string and interval

Supported signatures: DATE_SUB(DATE, INTERVAL INT64 DATE_TIME_PART)

The only supported arguments are date and internal

So the column you are providing as date is a string, it needs to be converted to date so that you can subtract 1 year from it.

you can convert like this - SELECT PARSE_DATE("%Y%m%d", date) from my_table