CODEWITHSUNDEEP
sqlgoogle-bigqueryproductpurchase-order
Mariotalo
Mariotalo

Reputation: 9

Before&After purchase of a product

I have two tables:

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?

Upvotes: -1

Views: 76

Answers (1)

Samuel
Samuel

Reputation: 3528

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

Upvotes: 1

Related Questions