Query to mark if a value falls between a set of values

I have two tables. And I want to check if a value from one, falls between any two numbers in the other table. For example the tables are:

table_a:
+--------+-------+---------------------+
|  name  | count |        time         |
+--------+-------+---------------------+
| table1 | 10    | 2019-05-03 10:30:00 |
| table1 | 20    | 2019-05-03 11:30:00 |
| table1 | 30    | 2019-05-03 12:30:00 |
| table1 | 40    | 2019-05-03 13:30:00 |
| table1 | 50    | 2019-05-03 14:30:00 |
+--------+-------+---------------------+

table_b:
+--------+-------+
|  name  | count |
+--------+-------+
| table1 | 35    |
+--------+-------+

From this I want to get the closest value above and below the number in table_b that exists in table_a, and then show this as a “match” and then also display the times of the closest value above and below this number. So the results should look something similar to:

Result:
+--------+--------------+---------------------+---------------------+
|  name  |    count     |     time_before     |     time_after      |
+--------+--------------+---------------------+---------------------+
| table1 | Counts Match | 2019-05-03 12:30:00 | 2019-05-03 13:30:00 |
+--------+--------------+---------------------+---------------------+

If this doesn’t match, then it will show this in the results as not matching and the times will just be blank.

Thanks

Answer

You can do this by generating a table of before and after values for count and time in table_a, and then JOINing that to table_b such that table_b.count is between count_before and count_after:

SELECT a.name, 
       a.count_before || ',' || a.count_after  AS count,
       a.time_before,
       a.time_after
FROM (SELECT name,
             lag(count) over (order by time) AS count_before,
             count AS count_after,
             lag(time) over (order by time) AS time_before,
             time AS time_after
      FROM table_a) a
JOIN table_b b ON b.count BETWEEN a.count_before AND a.count_after

Output:

name    count   time_before                 time_after
table1  30,40   2019-05-03T12:30:00.000Z    2019-05-03T13:30:00.000Z

Demo on dbfiddle