How do I "append" a column with a value from a table in SQL?

Question

Context

I have a SQL table with the following structure:

# TableName
+------------+------------+
| ColumnA    | ColumnB    |
+------------+------------+
| Person A   | 2022-03-01 |
| Person B   | 2022-03-01 |
| Person C   | 2022-03-01 |
| ...        | ...        |
| Person AAA | 2022-04-12 |
| Person BBB | 2022-04-12 |
| Person CCC | 2022-04-12 |
+------------+------------+

I would like a query that returns all people in ColumnA from N days ago (call it 5), along with the max date in ColumnB in the table. For example:

# TableName
+------------+------------+
| ColumnA    | ColumnB    |
+------------+------------+
| Person D   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person E   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person F   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
| Person G   | 2022-04-15 | # This record's value for ColumnB = 2022-04-10
+------------+------------+

The Catch(es)

This query is being executed via Panda's read_sql(), so I can't do something like the following (must be one SELECT statement):

SET @max_date = (SELECT MAX(ColumnB) FROM TableName);

SELECT
    ColumnA,
    ColumnB,
    @max_date
FROM TableName
WHERE ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

Additionally, ColumnB doesn't include all days so I can't do something like this:

SELECT
    ColumnA,
    DATE_ADD(ColumnB, INTERVAL 4 DAYS) as Blah
FROM TableName
WHERE ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

The Question

How would I write such a query? Is there some way I can "append" the MAX(ColumnB) via a JOIN or something like that?

Answer 1

A little more complex/explicit

SELECT
  a.*,
  b.MaxColumnB
FROM TableName a
  JOIN (SELECT MAX(ColumnB) As MaxColumnB FROM TableName) b ON 1 = 1
WHERE a.ColumnB = (
    SELECT
        DISTINCT
        ColumnB
    FROM TableName
    ORDER BY ColumnB DESC
    LIMIT 1
    OFFSET 4
);

Answer 2

I hope I got your question right:

SELECT 
    ColumnA,
    max(ColumnB) as ColumnB
FROM
    TableName
WHERE
    ColumnB >= NOW() - INTERVAL 5 DAYS;