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 | customer1@sample.com | ... | 1 US | US752646 | 2020-02-14 | customer1@sample.com | ... | 2 US | US975246 | 2020-02-28 | customer1@sample.com | ... | 3 US | US997512 | 2020-05-07 | customer1@sample.com | ... | 4 FR | FR210000 | 2020-03-22 | customer2@sample.com | ... | 1 FR | FR354884 | 2020-04-10 | customer2@sample.com | ... | 2 FR | FR578410 | 2020-04-30 | customer2@sample.com | ... | 3 UK | UK454100 | 2020-04-01 | customer3@sample.com | ... | 1 UK | UK884212 | 2020-08-21 | customer3@sample.com | ... | 2 IT | IT898990 | 2020-08-04 | customer4@sample.com | ... | 1 IT | IT999181 | 2020-10-24 | customer4@sample.com | ... | 2 IT | IT999652 | 2020-11-06 | customer4@sample.com | ... | 3
How would I best integrate this in my query above without interfering with the JOIN and my filters?
Thank you!
Advertisement
Answer
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.