CODEWITHSUNDEEP
sqlgoogle-bigquerydatediff
Андрей Буланкин
Андрей Буланкин

Reputation: 11

SQL. BigQuery. DATE_DIFF A in B

I have table

CREATE TABLE tests.segments (`date` date, ClientID STRING(6), SegmentID int)

With data

INSERT tests.segments VALUES ('2018-01-31' ,'A11111', 2), ('2018-02-28' ,'A11111', 2), ('2018-03-31' ,'A11111', 1), ('2018-04-30' ,'A11111', 1), ('2017-11-30' ,'B22222', 1), ('2017-10-31' ,'B22222', 1), ('2017-09-30' ,'B22222', 3), ('2017-09-30' ,'C33333', 1), ('2017-10-31' ,'C33333', 1)

I need to select DATE_DIFF for each ClientID in SegmentID I know how to select it for ClientID or SegmentID separate

SELECT SegmentID, DATE_DIFF(DATE(MIN(date)), DATE(MAX(date)), DAY) as days_diff
FROM tests.segments
GROUP BY SegmentID

And how to do it without SQL :) Please help.

Upvotes: 0

Views: 137

Answers (1)

Regressor
Regressor

Reputation: 1973

I am not sure if you are looking for this

enter image description here

But here is the query to achieve using the clientId in the group by clause. Let me know if this is the expected output.

SELECT SegmentID, clientid, DATE_DIFF(DATE(MIN(date)), DATE(MAX(date)), DAY) as days_diff
FROM tests.segments
GROUP BY SegmentID, clientid
order by SegmentID, clientid

Upvotes: 1

Related Questions