CODEWITHSUNDEEP
sqlgoogle-bigquerypartitioning
user16438416
user16438416

Reputation: 35

SQL BigQuery Using Partition By on multiple columns

I have the following table Transaction Table

USERID TRANSACTION_DATE Product
111 2021-05-03 A
112 2021-07-04 C
111 2021-08-21 A
113 2021-07-01 B
114 2021-07-09 A

I want to get a summary it so that I can get the MIN(TRANSACTION_DATE) for each product for each customer (basically the first date each customer started buying each of the product) like below.

Note: Not all customers have bought all the products

Desired Output:

USERID Product FIRST_BOUGHT
111 A 2021-05-01
111 B 2021-03-01
111 D 2021-11-07
112 A 2021-05-09
112 C 2021-06-01

I have so far tried below but it isn't giving the expected result. Any help would be appreciated.

SELECT USERID, 
PRODUCT, 
MIN(TRANSACTION_DATE) OVER(PARTITION BY USERID,PRODUCT ORDER BY TRANSACTION_DATE) AS FIRST_BOUGHT, 
FIRST_VALUE(PRODUCT) OVER(PARTITION BY USERID,PRODUCT ORDER BY TRANSACTION_DATE) AS Product
FROM Table1

Upvotes: 0

Views: 803

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Try this one:

select userid, product, min(transaction_date) as first_bought
FROM Table1
GROUP BY userid, product

Upvotes: 2

Related Questions