sql - Calculate order sequence in BigQuery

Question

I am new to BigQuery SQL and struggling with calculating an "order sequence" from a table of customer orders.

In my example, I am working with customer purchases, where I have the sales channel, the unique order number, the order date and other purchase as well as customer information. I am also joining two tables in my query to get all the necessary data into one table.

This is how my current query looks like:

SELECT
t1.channel as sales_channel,
t1.number as order_number,
t1.date as order_date,
t1.email as customer,
t1.price as gross_price,
t1.sku as SKU,
t1.quantity as sku_quantity,
t1.sku_price as sku_price,
t2.order_status,
t2.weight,
t2.size
FROM order_itemsinfo t1
JOIN order_statusinfo t2
ON t1.number= t2.number
where t1.price > 0 AND 
t1.sku in ("ABC-233-20L","DEF-100-10Y")

However, based on this query, I also want to add another column with the "order sequence", which is calculated on a customer level and based on the purchase date.

Hence, the result should look like the following with the calculated order sequence:

sales_channel | order_number | order_date | customer              | ...  | order_sequence
US            | US184746     | 2020-01-04 | [email protected]  | ...  | 1
US            | US752646     | 2020-02-14 | [email protected]  | ...  | 2
US            | US975246     | 2020-02-28 | [email protected]  | ...  | 3
US            | US997512     | 2020-05-07 | [email protected]  | ...  | 4
FR            | FR210000     | 2020-03-22 | [email protected]  | ...  | 1
FR            | FR354884     | 2020-04-10 | [email protected]  | ...  | 2
FR            | FR578410     | 2020-04-30 | [email protected]  | ...  | 3
UK            | UK454100     | 2020-04-01 | [email protected]  | ...  | 1
UK            | UK884212     | 2020-08-21 | [email protected]  | ...  | 2
IT            | IT898990     | 2020-08-04 | [email protected]  | ...  | 1
IT            | IT999181     | 2020-10-24 | [email protected]  | ...  | 2
IT            | IT999652     | 2020-11-06 | [email protected]  | ...  | 3

How would I best integrate this in my query above without interfering with the JOIN and my filters?

Thank you!

question from:https://stackoverflow.com/questions/65844254/calculate-order-sequence-in-bigquery

Answer 1

If I understand correctly, you would use window functions:

SELECT . . .,
       ROW_NUMBER() OVER (PARTITION BY ii.customer ORDER BY ii.date) as seqnum
FROM order_itemsinfo ii JOIN
     order_statusinfo si
     ON ii.number = si.number
WHERE ii.price > 0 AND 
      ii.sku in ('ABC-233-20L', 'DEF-100-10Y');

Note that I changed the table aliases. Instead of using meaningless letters, these are abbreviations for the table names. That makes the query much simpler to follow.