CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Banrakshas
Banrakshas

Reputation: 33

Struct Array in Bigquery with nested columns

I have two tables called source and base. The source table has a bunch of ids and all combination of weekly dates. The base table as ids, their tagged devices and the device start and end dates.

Example source table :

id com_date
acc_1 11/25/2022
acc_1 11/18/2022
acc_1 11/11/2022
acc_2 11/25/2022
acc_3 11/25/2022
acc_3 11/25/2022

Example of base table :

id device_id start_date end_date
acc_1 d1 11/24/2022 12/31/2999
acc_1 d2 11/19/2022 12/31/2999
acc_1 d3 11/12/2022 11/28/2022
acc_2 d4 11/20/2022 11/26/2022
acc_3 d5 11/17/2022 11/24/2022
acc_3 d6 11/10/2022 12/31/2999

I would like my final table to look something like this with nested columns -

enter image description here

Column count should be the count of distinct devices applicable for that com_date and each com_date should lie between start_date and end_date

Upvotes: 0

Views: 1446

Answers (1)

Jaytiger
Jaytiger

Reputation: 12254

You might consider below query.

(I've tested it after changing last com_date in source_table to 11/18/2022.)

SELECT s.id, s.com_date AS dates, 
       COUNT(DISTINCT device_id) count,
       ARRAY_AGG(STRUCT(b.device_id, b.start_date AS to_date, b.end_date AS from_date)) d
  FROM source_table s JOIN base_table b 
    ON s.id = b.id 
   AND PARSE_DATE('%m/%d/%Y', com_date) BETWEEN PARSE_DATE('%m/%d/%Y', start_date) AND PARSE_DATE('%m/%d/%Y', end_date)
 GROUP BY 1, 2;

enter image description here

Upvotes: 2

Related Questions