-1

I need to do a subquery in BigQuery as follows

SELECT
  *
FROM 
  table1
WHERE
  user_id IN 
(
  SELECT
    DISTINCT user_id
  FROM table1
  WHERE 
    date >= '2023-01-01'
);

I tried the solution in this post
MySQL DELETE FROM with subquery as condition

and my query turns into this

SELECT e.*
FROM table1 e
WHERE user_id IN (SELECT user_id
                  FROM (SELECT DISTINCT user_id
                        FROM table1
                        WHERE date >= '2023-01-01') x);

but this came up

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

How can I fix my query?

ps: my table looks like

user_id balance date
1 0.01 2022-01-01
1 0.02 2023-01-02
2 0.03 2022-01-01
2 0.04 2022-12-31
3 0.05 2023-12-31

I need all users data whose latest record is later than 2023-01-01, which is

user_id balance date
1 0.01 2022-01-01
1 0.02 2023-01-02
3 0.05 2023-12-31
k07224115
  • 93
  • 6

1 Answers1

0
SELECT
  a.*
FROM
  table1 a
INNER JOIN (
  SELECT
    DISTINCT user_id
  FROM
    table1
  WHERE
    date >= '2023-01-01' ) b
ON
  a.user_id = b.user_id

This is what I came up with now, not sure if it's the right one

Edit:
Since the error message told us that "subqueries that reference other tables are not supported, use JOIN instead",
so I am using inner join to get all the users records I want.

k07224115
  • 93
  • 6
  • 1
    Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** – Jeremy Caney Aug 20 '23 at 00:49