CODEWITHSUNDEEP
sqlgoogle-bigquery
J. Awesome
J. Awesome

Reputation: 13

Using MIN(TIMESTAMP) in WHERE

First off, I am a beginner in SQL just learning and I am stuck on one problem looked everywhere but not able to find an answer for it.

SCHEMA: time_ts TIMESTAMP,id BYTES,sale_amount FLOAT,client STRING.

The report I am trying to export is the clients who are newly acquired within the last 12 months that has made 2 and 3 purchase over the last 12 months as well.

DATA SAMPLE:
Row time_ts id  sale_amount client   
1   2011-12-02 16:17:01.280 UTC James   97.67   104795   
2   2010-03-29 19:43:07.723 UTC Mark    90.0    106186   

EXPECTED RES
Number_of_Orders Revenue_Total Year Total_Num_of_orders
1, 100$ 2010 60
2, 150$ 2010 65

What I have so far ( Which returns 0 results):

SELECT client, COUNT(id) AS sales, MIN(time_ts),
FROM [bigquery-public-data:hacker_news.comments]
WHERE time_ts >= TIMESTAMP(time_ts) > DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -12, 'MONTH')
GROUP BY client
HAVING COUNT(id) = 2;

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You are close. But the condition in the where needs to be in the having:

SELECT client, COUNT(id) AS sales, MIN(time_ts),
FROM [bigquery-public-data:hacker_news.comments]
GROUP BY client
HAVING COUNT(id) = 2 AND
       MIN(time_ts) > DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -12, 'MONTH');

I am assuming that the date arithmetic is right. I stopped using legacy SQL a while ago and you should use standard SQL as well.

Upvotes: 4

Related Questions