Hi all, I'm new to this forum and just starting out on my SQL learnings in Google's Big Query.
I have an issue where I'm trying to show data in a particular way based on multiple events. I'll try my best to explain my problem, and hopefully someone can help or offer advice. Thanks in advance.
Problem statement -
I'm trying to show when a customer (who has a unique identifier) has a series of job references booked over a period of 3 weeks, in order to show volume of repeat jobs for that customer.
Example of what I have (trying to layout how I have the data in rows and columns)
Row 1 - (Column 1) = Week 1, (Column 2) = Customer 123, (Column 3) = Job ref 123
Row 2 - (Column 1) = Week 3, (Column 2) = Customer 123, (Column 3) = Job ref 345
I'd like to be able to simplify this view and show that over a 3 week period, that Customer 123 has had a repeat job ref against their unique customer number.
I'd like it to almost show something like the below
Row 1 - (Column 1) = Week of initial job, (Column 2) = Customer 123, Column 3 = count of first job in week 1, (Column 4) = Count of any repeat jobs in the weeks after week 1 occurred.
Hopefully that makes sense, and this is my first time posting, so any further info needed then please ask.
Thanks in advance for any help relating to my issue.