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

Question

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

Table B

Table C (the one I would like to get)

I am using Google BigQuery if anyone is wondering

thanks anyway. I knew this was too hard – mike winston Oct 18 '16 at 11:54 — mike winston, Oct 18 '16 at 11:54

Mikhail Berlyant · Accepted Answer · 2016-10-18T12:39:56.240

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

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

1 Answers1