'Join GA table with a normal table on date field in Big Query
I want to join in BigQuery some GA sessions tables with one normal table on all the dates between two date fields (Start and End) in my normal table. My goal is to obtain for each session the time, the fullVisitorId, the day that session took place and other infos relative to that date that are stored in the normal table. The normal table has 3 columns Start(datetime), End(datetime), Event(string). This is what i've done:
SELECT * FROM `normal_table` as A
JOIN (
SELECT fullVisitorId, MAX(h.time)/1000 SessionTime,date FROM `analytics_table.ga_sessions_*`, UNNEST(hits) as h
WHERE _table_suffix BETWEEN '20211207' AND '20211213'
) AS B
ON PARSE_DATE("%Y%m%d", B.date) Between DATE(A.Start) AND DATE(A.END)
The problem is that i think i am duplicating observations with this type of join or something else is going wrong because i have lots of rows duplicated.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
