Aggregate multiple rows between two numbers

Question

Let's say we have this table: table1

sensor_id	start_time_index	end_time_index
1	1	4
1	2	6
2	1	3
2	2	4

And another table with: table2

sensor_id	time_index	value
1	1	'A'
1	2	'B'
1	3	'A'
1	4	'C'
1	5	'D'
1	6	'B'
2	1	'B'
2	2	'C'
2	3	'D'
2	4	'A'

Desired table is:

sensor_id	start_time_index	end_time_index	values_concatenated
1	1	4	"ABAC"
1	2	6	"BACDB"
2	1	3	"BCD"
2	2	4	"CDA"

I didn't know how to aggregate between a range that's specified between two values that are in two columns.

Answer 1

Using "range join" ON col BETWEEN ... AND ... and LISTAGG:

SELECT tab1.sensor_id, tab1.start_time_index, tab1.end_time_index,
  LISTAGG(tab2.value) WITHIN GROUP(ORDER BY tab2.time_index) AS values_contatenated
FROM tab1
JOIN tab2
  ON tab1.sensor_id = tab2.sensor_id
 AND tab2.time_index BETWEEN tab1.start_time_index AND tab1.end_time_index
GROUP BY tab1.sensor_id, tab1.start_time_index, tab1.end_time_index