I've hit a bit of a dead end with this BigQuery/SQL question. After ~1 hour of Googling I still haven't figured it out, so I figured I'd ask here.
I have a BigQuery table (mycompany.engagement.product_orders) of customer order data. Every row in the table describes an order placed by a customer and it looks something like this:
| Row | Product | Timestamp | Type | CustomerName |
|---|---|---|---|---|
| 1 | Apple | 2021-08-19 11:41:08.874 UTC | Gala | Philippe Kahn |
| 2 | Orange | 2021-08-19 11:41:12.874 UTC | Navel | Grace Hopper |
| 3 | Pear | 2021-08-19 11:41:24.874 UTC | Bosc | Vladimir Nabokov |
| 4 | Apple | 2021-08-19 11:41:47.874 UTC | Melba | Sylvia Plath |
| 5 | Pear | 2021-08-19 11:41:55.874 UTC | Anjou | Alan Turing |
| 6 | Pear | 2021-08-19 11:42:10.874 UTC | Asian | Sylvia Plath |
| 7 | Apple | 2021-08-19 11:42:11.874 UTC | Fuji | Vladimir Nabokov |
| 8 | Orange | 2021-08-19 11:42:37.874 UTC | Blood | Ada Lovelace |
| 9 | Orange | 2021-08-19 11:42:49.874 UTC | Cara | Grace Hopper |
| 10 | Apple | 2021-08-19 11:42:51.874 UTC | Melba | Alan Turing |
I would like to formulate a SQL query that will count the products ordered by customers in 1 minute intervals (or any interval really) to return a table that looks (something) like this:
| Row | Product | Timestamp | Count |
|---|---|---|---|
| 1 | Apple | 2021-08-19 11:41:00.000 UTC | 2 |
| 2 | Orange | 2021-08-19 11:41:00.000 UTC | 1 |
| 3 | Pear | 2021-08-19 11:41:00.000 UTC | 2 |
| 4 | Pear | 2021-08-19 11:42:00.000 UTC | 1 |
| 5 | Apple | 2021-08-19 11:42:00.000 UTC | 2 |
| 6 | Orange | 2021-08-19 11:42:00.000 UTC | 2 |
Some notes:
The examples I found that were relevant (eg: 