SQL count all rows with a value within a range from a join

Question

I am attempting a version of what was answered here: Getting count of rows that have values within a value range MySQL. But, the rows I am trying to count are inside a join, and as far as I can tell, I need to be able to assign the join to an alias, which I can't figure out how to do. FWIW, I'm using duckdb, but there shouldn't be a problem, since it accepts SQL queries.

Say my data is like this:

Table1:

id	val
1	1
2	1
3	3
4	2

Table2:

id	words
1	foo
1	bar
2	baz
3	nu
4	olde

And I want to get the count of all the words within 1 val. I suppose after doing an inner join on identical id's and aggregating, the result should look like this:

Joined and counted table:

id	val	words	cnt
1	1	foo	4
1	1	bar	4
2	1	baz	4
3	3	nu	2
4	2	olde	5

Analogizing from the original question, my query is as follows. Note that I have bracketed the INNER JOIN portion in an attempt to assign it to a variable/alias so I can refer to it during the LEFT JOIN:

SELECT
    id,
    val,
    words,
    COUNT(*) AS cnt
FROM (Table1
    INNER JOIN Table2
    ON Table1.id=Table2.id) t1
LEFT JOIN t1 t2
    ON ABS(t1.val - t2.val) <= 1
GROUP BY
    t1.val, t1.id;

Answer 1

Can this also be written as a Correlated Subquery?

with t1 as (
   from Table1 join Table2 using (id)
)
from t1 t2 -- the `t2` alias here makes it a correlated subquery
select
   *,
   (select count(*) from t1 where abs(t1.val - t2.val) <= 1) count

┌───────┬───────┬─────────┬───────┐
│  id   │  val  │  words  │ count │
│ int64 │ int64 │ varchar │ int64 │
├───────┼───────┼─────────┼───────┤
│     1 │     1 │ foo     │     4 │
│     1 │     1 │ bar     │     4 │
│     2 │     1 │ baz     │     4 │
│     3 │     3 │ nu      │     2 │
│     4 │     2 │ olde    │     5 │
└───────┴───────┴─────────┴───────┘