Bigquery alternative for partition by or cross join

Question

In the below query I need to include 2 more additional columns that calculates count of nativeprod(each of the 3 native prods - AAA, BBB, CCC) for each user id in last 1 day. I will change the where clause as NativeProd IN('AAA','BBB','CCC') and GlobalProd remains the same. Additionl column names below:

BBB_count_last_1_Day
CCC_count_last_1_Day

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

I tried using partition by nativeprod but i got an error saying "missing function in analytic expression". My next option is to use join and match user ids to compute the values. Is there any other better way to handle this?

btw, looks like you are using here exactly what was suggested in previous answers - but to be sure we are in right direction - i suggest you to go back and vote/accept on your recent questions. or provide comments if something didn't work — Mikhail Berlyant
I did upvote for your answer to my previous question. But i must apologies that i forgot to accept the answer. Also, Thanks Mikhail. I am learning bigquery only because of your help. But this is another question that is related to the same question. Hence I created a new one — rbkk2016

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-08-08T18:58:51

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,
-- new columns for last 1 day  
  SUM(IF(NativeProd = 'AAA' AND DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -1, "DAY")), 1, 0)) AS AAA_count_last_1_Day,
  SUM(IF(NativeProd = 'BBB' AND DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -1, "DAY")), 1, 0)) AS BBB_count_last_1_Day,
  SUM(IF(NativeProd = 'CCC' AND DATE(StartTime) > DATE(DATE_ADD(CURRENT_DATE() , -1, "DAY")), 1, 0)) AS CCC_count_last_1_Day
FROM [ProdTable]
WHERE NativeProd IN('AAA','BBB','CCC') AND GlobalPod='AAA'
GROUP BY 1,2,3,4
LIMIT 10

Bigquery alternative for partition by or cross join

1 Answers