Efficient way of marking duplicates (except first) based on a subset of columns in BigQuery

Question

So I have a dataset which looks like:

user_ID	order_ID	order_start_date	is_returning?
1	1234	23-Mar-2021	0
2	1235	23-Mar-2021	0
2	1236	23-Mar-2021	1
1	1237	24-Mar-2021	1
3	1238	28-Mar-2021	0

Except for the is_returning column and I want that to be calculated based on if that user_ID is seen before or not.

In pandas, its an easy one-liner as:

all_data['is_returning'] = all_data.user_id.duplicated().astype(int)

But in BQ, I am yet to find a straightforward way of doing so. As of now, I have got till

(SELECT COUNT(USER_ID) AS num_users_with_2_orders FROM
 (SELECT COUNT(DISTINCT ORDER_NUMBER) AS ORDERS, USER_ID FROM `my_project.my_dataset.my_table` WHERE
    order_number IN (SELECT DISTINCT ORDER_NUMBER FROM (SELECT order_number, sum(usd_total_price) as totals
         FROM `my_project.my_dataset.my_table`
         GROUP BY order_number
         HAVING totals <= @maximum_value AND totals >= @minimum_value))
   AND USER_ID IN
   (SELECT DISTINCT USER_ID
     FROM `my_project.my_dataset.my_table` WHERE
     USER_ID <> -1 AND
     
     CAST(order_start_date AS DATE) >= PARSE_DATE('%Y%m%d', @DS_START_DATE) AND CAST(order_start_date AS DATE) <= PARSE_DATE('%Y%m%d', @DS_END_DATE))

      
 GROUP BY USER_ID
 HAVING ORDERS >= 2))

Then I use these user_IDs to compare. If they are in original table, I say its returning. While being overly complex, it doesn't serve the purpose of keep = 'first' argument as it marks all orders not excluding the first one. For that I have to apply an additional condition of order_start_date <> MIN(ORDER_START_DATE) grouped by user_ID.

So my question is: What would be a more efficient method of achieving the same?

user_13473525 · Answer

In BigQuery this can be achieved with analytic functions like row_number:

with my_table as (
  select 1 as user_id, 1234 as order_id union all
  select 2, 1235 union all
  select 2, 1236 union all
  select 1, 1237 union all
  select 3, 1238
)
select 
  user_id,
  order_id,
  IF(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_id) > 1, 1, 0) AS is_returning
from my_table
order by order_id

Efficient way of marking duplicates (except first) based on a subset of columns in BigQuery

1 Answers