Before&After purchase of a product

Question

I have two tables:

• orders_product: all the orders. Each line is a product sold with some details about the order in which it was included. So, if the order has more than 1 product, there are more than 1 line for this order.

• orders_grouped: each line is an order with some details about this specific order.

I would like know if there was a previous purchase and a following purchase for each product.

SELECT
  product_name,
  last_value(product_all_grouped_list) over (partition by ord.customer_id order by created_at asc rows  between unbounded preceding and 1  preceding ) as last_order,
  last_value(product_all_grouped_list) over (partition by ord.customer_id order by created_at desc rows  between unbounded preceding and 1  preceding ) as next_order_products,
  last_value(basket_size) over (partition by ord.customer_id order by created_at desc rows  between unbounded preceding and 1  preceding ) as next_order_basket_size

FROM
  `orders_product` ord
  left join `orders_grouped` ordgroup 
on ord.order_number=ordgroup.order_number

When the order has only one product (basket_size=1), everything is correct but when the basket_size>1, the results for the first product of this order is OK but for the rest of products of the order is wrong.

Can someone help me?

Answer 1

Because several orders items are present and thus several rows the windows function has to be different.

RANGE instead of ROWS in the over statement.

Also use window at the end:

With tbl as (
  Select * from unnest(generate_timestamp_array("2022-09-01","2022-09-15",interval 1 hour)) update_time
)

SELECT
  *,
  LAST_VALUE(update_time) OVER (ORDER BY update_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),
  timestamp_diff(update_time,timestamp("1999-01-01"),second) ,
  LAST_VALUE(update_time) OVER SETUP_window
FROM
  tbl
  window SETUP_window as (ORDER BY timestamp_diff(update_time,timestamp("1999-01-01"),second) ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 36000 PRECEDING )
  order by update_time desc