Bigquery - alternative for cross join

Question

I have to have below columns in my output in addition to the ones that I have specified in the query
count of (nativeprodID) for each userID in last 7 days
count of (nativeProdID) for each userID in last 15 days
count of (nativeprodID) for each userID in last 30 days

SELECT userid, nativeprodID, nativeVersion, nativeVersionName,
MAX(StartTime) AS Last_Used_TimeStamp,
FROM
[ProdTable]
WHERE NativeProd ='AAA'
AND GlobalPod='AAA'
GROUP BY 1,2,3,4
LIMIT 10

I was thinking of using cross join but is there any other easier technique?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-08-04T22:50:21

Try below

SELECT 
  userid, 
  nativeprodID, 
  nativeVersion, 
  nativeVersionName, 
  MAX(StartTime) AS Last_Used_TimeStamp, 
  SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -7, "DAY")), 1, 0)) AS count_last_7_days, 
  SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -15, "DAY")), 1, 0)) AS count_last_15_days, 
  SUM(IF(DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -30, "DAY")), 1, 0)) AS count_last_30_days, 
FROM [ProdTable] 
WHERE NativeProd ='AAA' AND GlobalPod='AAA' 
GROUP BY 1,2,3,4 
LIMIT 10

Bigquery - alternative for cross join

1 Answers