Access to subquery value in joined inner subquery

Question

LEFT JOIN should implement FOR EACH date in my case without using procedures

SELECT * FROM
    (   
        SELECT DISTINCT(toDate(date_update)) as dist_date
        FROM prod.point_pg pp
    )Q1
LEFT JOIN
    (
    SELECT sum(Q2.lockers_count), max(lockers_date) as l_date FROM
        (
            SELECT count(pg.id) as lockers_count, toDate(pg.date_update) as lockers_date FROM prod.point_pg pg
            WHERE pg.state=0 and pg.publish=TRUE
            GROUP BY toDate(pg.date_update) 
        )Q2
    WHERE Q2.lockers_date BETWEEN '2023-01-01' AND Q1.dist_date
    )Q3
ON Q1.dist_date = Q3.l_date

But subquery Q2 can't acccess to first table variable Q1

    SQL Error [47] [07000]: Code: 47. DB::Exception: 
Missing columns: 'Q1.dist_date' while processing query: 

How it is possible to access to dist_date then? Final result should contain each date and sum of counters between start date and dist_date but I need a value for range in WHERE statement

Answer 1

Probably this query can much simpler, but without create_table/insert/expected result it's unclear.

you cannot address dist_date inside the subquery, only outside:

SELECT * FROM
    (   
        SELECT DISTINCT(toDate(date_update)) as dist_date
        FROM prod.point_pg pp
    )Q1
LEFT JOIN
    (
    SELECT sum(Q2.lockers_count), max(lockers_date) as l_date FROM
        (
            SELECT count(pg.id) as lockers_count, toDate(pg.date_update) as lockers_date FROM prod.point_pg pg
            WHERE pg.state=0 and pg.publish=TRUE
            GROUP BY toDate(pg.date_update) 
        )Q2
    WHERE Q2.lockers_date >= '2023-01-01'
    )Q3
ON Q1.dist_date = Q3.l_date

WHERE Q2.lockers_date BETWEEN '2023-01-01' AND Q1.dist_date