CODEWITHSUNDEEP
google-bigquery
sam123
sam123

Reputation: 119

Join on 3 tables in bigquery

I have the below query in my BQ. Basically i have 3 tables (Users, product and Visits), and i am trying to make a join on date.

SELECT
  product.Date,
  SUM(product.quantity) AS quantity,
  SUM(users.Users) AS users,
  SUM(visitsdata.Visits) AS visits
FROM (
  SELECT
    product.Date,
    product.product,
    users.date,
    users.Users,
    visitsdata.Visits
  FROM
    [Testdata.productsold] AS product
  JOIN
   [Testdata.userswithpurchase] AS users
  ON
    product.Date=users.date) AS usersproduct
JOIN
  [Testdata.visits] visitsdata
ON
  usersproduct.Date= visitsdata.date
GROUP BY
  product.Date

I am not an expert in writing query, but can someone please tell me what is wrong with my query?

Thanks

Upvotes: 0

Views: 3355

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Shooting in the air, but - Try below 

SELECT
  [Date],
  SUM(quantity) AS quantity,
  SUM(Users) AS users,
  SUM(Visits) AS visits
FROM (
  SELECT
    product.Date AS [Date],
    product.quantity AS quantity,
    users.Users AS Users,
    visitsdata.Visits AS Visits
  FROM [Testdata.productsold] AS product
  JOIN [Testdata.userswithpurchase] AS users
    ON product.Date=users.date
  JOIN [Testdata.visits] visitsdata
    ON product.Date= visitsdata.date
) AS usersproduct
GROUP BY 1

Depends on nature of your data - you can consider LEFT JOINs vs [INNER] JOIN

Upvotes: 2

Related Questions