DeCorrelated SubQueries in Google BigQuery?

Question

I have been struggling with a problem for hours. I have found myself down multiple rabbit holes and into the realms of DeCorrelated SubQueries which are frankly beyond me...

I have two tables and I'm trying to pull from both without a common column to join against. I need to take the a value from table 1, find the closest value (that is lower) in table 2 and then pull related data from table 2.

table_1

id	score
1	99.983545
2	98.674359
3	97.832475
4	96.184545
5	93.658572
6	89.963544
7	87.427353
8	82.883345

table_2

average_level	percentile
99.743545	99
97.994359	98
97.212485	97
96.987545	96
95.998573	95
88.213584	94
87.837384	93
80.982147	92

From the two tables above I need to:

1. Take the id and score
2. identify the closest average_level to the score
3. include the correlating average_level and percentile

The hoped for output would look like this...

id	score	average_level	percentile
1	99.983545	99.743545	99
2	98.674359	97.994359	98
3	97.832475	97.212485	97
4	96.184545	95.998573	95
5	93.658572	88.213584	94
6	89.963544	88.213584	94
7	87.427353	80.982147	92
8	82.883345	80.982147	92

Any help or advice would be very much appreciated

Answer 1

You can do this by joining both tables with table_1.score >= table_2.average_level and then getting the max(average_level) and max(average_level) - which will be the closest yet inferior or equal values from table_2 - and grouping by the fields in table_1:

SELECT TABLE_1.ID, TABLE_1.SCORE, 
MAX(TABLE_2.AVERAGE_LEVEL) AS AVERAGE_LEVEL, 
MAX(TABLE_2.PERCENTILE) AS PERCENTILE
FROM TABLE_1 INNER JOIN TABLE_2
ON TABLE_1.SCORE >= TABLE_2.AVERAGE_LEVEL
GROUP BY TABLE_1.ID, TABLE_1.SCORE
ORDER BY TABLE_1.ID

I add the fiddle example here, it also includes @Ömer's answer

Answer 2

if we say first table score and second one avarage you can try this.

select *
from Score s
inner join average a on a.Percentile = (select top(1) al.Percentile from average al order by Abs(average_level - s.score))

enter image description here