CODEWITHSUNDEEP
sessiongoogle-analyticsgoogle-bigquery
user11776437
user11776437

Reputation: 13

Dynamic date selection in bigquery

i like to export GA sessions information(ex: visitStartTime) for current_day-1 and current_day - 8.

current_day is 12072019,

current_day-1day is 11072019

current_day - 8 days is 03072019.

Is it possible to extract this info with single query?

Expected result:

enter image description here

Upvotes: 0

Views: 822

Answers (1)

justbeez
justbeez

Reputation: 1387

I would expect that you could just use this in your WHERE clause conditions (I did my best to match the input formats you mentioned and the output format your showed; you may be able to simplify that if you don't require the output to be formatted):

-- Example Data for testing
WITH
  data AS (SELECT RAND() AS VisitStartTimeCount,t AS DAte FROM UNNEST(["11072019","04072019","10072019"]) AS t)

SELECT
  VisitStartTimeCount,
  FORMAT_DATE("%Y%m%d",PARSE_DATE("%d%m%Y",Date)) AS Date
FROM
  data
WHERE
  PARSE_DATE("%d%m%Y",Date) IN(
    DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY),
    DATE_SUB(CURRENT_DATE(),INTERVAL 8 DAY)
  )

(While you could use two expressions with OR, using IN() lets you not repeat the column name.)

Upvotes: 1

Related Questions