Assuming that AVALUE is based on the ID value (that is, the same ID always has the same AVALUE), then you can leave AVALUE out of the GROUP BY.  If...

Or take advantage of SQL's inherent capabilities by using the sql_variant data type (yes, technically you don't need the second CAST):

SELECT DISTINCT 
    ...

The above general approach should work, there just may be a more efficient way that's not popping into my head right now.

We could brute force it; I'll try to think of a better way when I get a chance:

;WITH cte_person AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY...

Combining them wouldn't optimize it.  For three different tables, SQL must still scan each table/covering index to provide the results.

Realistically at this point, I'm just trying to gather a better understanding of what is going on here for my own sanity.  If the Execution plan shows SQL expects...

Personally, I'd try using a temp table and see if it works well before I spent lots of time trying other work-arounds:

declare @p3 dbo.IdList

select * into #p3 from @p3

create unique...

I don't know Snowflake SQL, but based on Google for their syntax, derived tables are allowed in an UPDATE:

UPDATE target SET v = b.v /* actual UPDATE example from Snowflake...

I think it's easier to use ROW_NUMBER(), like this:

;WITH cte_STUDENTIDPOOL AS (
    SELECT STATESTUDENTID, ROW_NUMBER() OVER (ORDER BY STATESTUDENTID) AS row_num
   ...

Yes, that is correct.

Roughly:

;WITH cte_person AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID) AS row_num,
        ROW_NUMBER() OVER(PARTITION...

Typically that's done with a cte that uses ROW_NUMBER().  Easy for first/specific row num, but for last to be easy, you'd have to do a DESC ORDER BY in the...

I prefer a cross-tab approach for these types of queries:

;WITH cte_data AS (
    SELECT MyId, PayeeId, ROW_NUMBER() OVER(PARTITION BY MyId ORDER BY PayeeId) AS...

Because SQL, deliberately, has delayed verification.  This allows you to create a proc before other objects exist.  For example, this should run fine:

USE tempdb;

GO

CREATE PROC proc1 AS SELECT * FROM...

If you need to update PoolId based on another table, you should do the lookup in the same query as the UPDATE.  And there's no reason to update PoolId twice...