CODEWITHSUNDEEP
google-bigquery
mike winston
mike winston

Reputation: 169

How can you do SQL joins on dates between specific dates?

This may be incredibly hard or incredibly simple, I don't know which but I'm stuck.

How do I join data that happened between specific days? How do I write that? The tricky thing is that every row would have a different time period, a unique period for every user. Example:

Table A enter image description here

Table B

enter image description here

Table C (the one I would like to get)

enter image description here

I am using Google BigQuery if anyone is wondering

Upvotes: 4

Views: 3508

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below is for BigQuery Standard SQL (see Enabling Standard SQL) 

SELECT 
  a.User_Id, Start_date, End_Date, 
  (SELECT IFNULL(SUM(clicks),0) FROM TableB WHERE user_Id = a.User_Id 
  AND date BETWEEN Start_date AND End_date) AS Clicks_from_tableB
FROM TableA AS a
ORDER BY user_Id

or 

SELECT 
  a.User_Id, Start_date, End_Date, 
  SUM(IFNULL(clicks, 0))  AS Clicks_from_tableB
FROM TableA AS a
LEFT JOIN TableB AS b
ON a.User_Id = b.User_Id
AND b.date BETWEEN Start_date AND End_date
GROUP BY 1, 2, 3
ORDER BY user_Id

Upvotes: 6

Related Questions