Add a column with customers orders count at the time they passed the order

user_11538800 0
Viewed 48

I have the following table

order_id created_at customer_id
1 2020-01-02 11
2 2020-02-03 12
3 2020-02-03 11

I would like to add a column "customer_orders_count" that will assign the number of orders that a customer passed to each transaction, ie obtain this table :

order_id created_at customer_id customer_orders_count
1 2020-01-02 11 1
2 2020-02-03 12 1
2 2020-02-03 11 2

My problem it's I can't find how to calculated a local "customer_orders_count" dependind on each order, I only managed to add a column with the global "customer_orders_count" and for example for the first row order_id=1 I'll get customer_orders_count=2 whereas I'll like to be 1.

Does anyone has and idea ?

google-bigquery sql
2 Answers

Use cumulative count:

with mytable as (
  select 1 as order_id, date '2020-01-02' as created_at, 11 as customer_id union all
  select 2, '2020-02-03', 12 union all
  select 3 , '2020-02-03', 11
)
select *, count(*) over (partition by customer_id order by created_at) as customer_orders_count
from mytable
order by order_id

enter image description here

user_134735250

Use row_number():

select t.*,
       row_number() over (partition by customer_id order by created_at) as customer_order_count
from t;

This is subtly different from using a cumulative count(). This version guarantees that the numbers for a given customer are never duplicated, even when the dates are the same. A cumulative count has no such guarantee.

user_11440350
Related