Struct Array in Bigquery with nested columns

Question

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 -

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

In source table, acc_3 has same date twice . one shouldn't be '11/28/2022' ? — Jaytiger
– Jaytiger, Commented Nov 26, 2022 at 2:17

Jaytiger · Accepted Answer · 2022-11-26 02:51:17Z

2

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;

answered Nov 26, 2022 at 2:51

Jaytiger

12.3k2 gold badges7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Struct Array in Bigquery with nested columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related