Issue

This Content is from Stack Overflow. Question asked by SwimJim

I am using big query and looking to find the toalAfterRefund value of the first order of an emailAddress.
Both these select statements work as individual statements, but I’m having trouble joining them.
I get the error

Syntax error: Expected end of input but got keyword JOIN at [1:1]

I’m having trouble understanding what I need to do to join these as these statements together in bigquery.
I am trying to get the toalAfterRefund value of the emailAddresses first orderDate.

SELECT  H.emailAddress, H.orderId, H.orderDate, H.totalAfterRefund
           FROM  `nexgen-362616.orders.allOrders` AS H
          WHERE   totalAfterRefund>0 
          
JOIN
          (SELECT emailAddress, MIN(OrderDate) AS first_order
          FROM  `nexgen-362616.orders.allOrders` as X
          WHERE   totalAfterRefund>0 
          GROUP BY emailAddress) 
          ON H.emailAddress = X.emailAddress AND H.orderDate = X.first_order

Solution

WITH table_emailAddress as
(SELECT emailAddress, MIN(OrderDate) AS first_order
          FROM  `nexgen-362616.orders.allOrders` as X
          WHERE   totalAfterRefund>0 
          GROUP BY emailAddress) 

SELECT  H.emailAddress, H.orderId, H.orderDate, H.totalAfterRefund
           FROM  `nexgen-362616.orders.allOrders` AS H
       
          
JOIN table_emailAddress  as X
     
          ON H.emailAddress = X.emailAddress AND H.orderDate = X.first_order

   WHERE   totalAfterRefund>0

This Question was asked in StackOverflow by SwimJim and Answered by Samuel It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.