How to get correct min, max date for each customer's changing label in wide format in BigQuery?

Question

I have a table that records customer purchases, for example:

customer_id	label	date	purchase_id	price
2	A	2022-01-01	asd	10
3	A	2022-01-01	asdf	5
4	B	2022-02-04	asdfg	200
2	A	2022-01-03	asdjg	4
3	B	2022-02-01	dfs	20
2	G	2022-04-05	fdg	40
2	G	2022-04-10	fdg	40
2	A	2022-06-06	fgd	20

I want to see how many days/money each customer has spent in each label, so far what I'm doing is:

SELECT
customer_id,
label,
COUNT(DISTINCT(purchase_id) as orders_count,
SUM(price) as total_spent,
min(date) as first_date,
max(date) as last_date,
DATE_DIFF(max(date), min(date), DAY) as days
FROM
TABLE
WHERE
date > '2022-01-01'
GROUP BY
customer_id,
label

which gives me a long table, like this:

customer_id	label	orders_count	total_spent	first_date	last_date	days
2	A	3	34	2022-01-01	2022-06-06	180
2	G	1	40	2022-04-05	2022-04-10	5

etc

Just for simplicity I show a few columns, but customers have orders all the time. The issue with the above is that, for example for customer 2, that he starts with label A, then changes to G, then he is back to A so this is not visible in the results table (min(date) is correct, but max(date) takes their 2nd A max(date)) and that I'd prefer to have it in wide format. For instance, ideally, columns called next_label_{i} that you get values for each changing label would be the best for me.

Could you advise me of a way of a) dealing with accomodating with this label change(future label change is the same as an earlier label) and b) a way to produce it into a wide format?

Thanks

edit: example output (correct date, wide format) [columns would go as wide as the max number of unique labels for any customer]

customer_id	first_label	first_first_date	first_last_date	first_total_spent	first_days	next_label	next_first_date	next_last_date	next_days	next_label_2	next_first_date_2	next_last_date_2	next_days_2
2	A	2022-01-01	2022-01-03	2	14	G	2022-04-05	2022-04-05	0	A	2022-06-06	2022-06-06	0

etc

Sorry this is not exactly accurate (missing the orders_count, total_spent) but it's a pain in the ass for format it here, but hopefully you get the idea. In principle, it's something as if you used python's pivot_table on the previous dataset.

Alternatively, I'd be glad for just a solution in the long format that distinguishes between a customer's label and the same customer's repeated label ( as in customer 2 who starts with A and after changing to G, returns to A)

Mikhail Berlyant · Answer

Could you advise me of ... b) a way to produce it into a wide format?

First, I want to say that I hope you have really good reason to get that output as usually it is not what is considered a best practices and rather is being left for presentation layer to handle.

With that in mind - consider below approach

select * from (
  select customer_id, offset, purchase.*
  from (
    select customer_id, 
      array_agg((struct(label, date, purchase_id, price)) order by date) purchases
    from your_table 
    group by customer_id
  ), unnest(purchases) purchase with offset
  order by customer_id, offset
)
pivot (
  any_value(label) label, 
  any_value(date) date, 
  any_value(purchase_id) purchase_id, 
  any_value(price) price
  for offset in (0,1,2,3,4,5)
)

if applied to sample data in your question - output is

Note: Above has silly assumption that you know the max number of steps (in this case I used 6 - from 0 till 5). There are plenty of posts here on SO that shows how to use same technique to make it dynamic. I do not want to duplicate them as it is against SO policies. So, just do your extra homework on this :o)

How to get correct min, max date for each customer's changing label in wide format in BigQuery?

1 Answers