-- goal: create a list of update statements to correct a batch change error
-- using data from the auditor.student_card_school_history table to repopulate
-- the correct values
select 'update student_card.school set addr_city = ''' ||  -- || = "concatenate text", ''' = a way of escaping single quotes so the result is single quoted
        coalesce(x.addr_city, 'null') ||  -- COALESCE is a function to address possibly NULL values by supplying a value - necessary because otherwise you get blank rows
        ''', addr_state = ''' ||
        coalesce(x.addr_state, 'null') ||
        ''', addr_post_code = ''' ||
        coalesce(x.addr_post_code, 'null') ||
        ''' where id = ' || x.id || ';'
        -- resulting row should look like:
        -- update student_card.school set addr_city = 'Bloomingdale', addr_state = 'GA', addr_post_code = '31302-9278' where id = 4;
from ( -- this starts out as just a normal subquery, aliased to "x"
    select  audit_id,
            id,
            addr_city,
            addr_state,
            addr_post_code,
            row_number() over (partition by id order by audit_id desc) -- this is called a "window function" - it basically creates an on-the-fly "table" you can query from
            -- partition by - similar to "group by" in an aggregate function like COUNT() - you have to tell the window function what to consider a "unit" of data, or effectively, a "row"
            -- row_number() literally just adds a number to the row so you can identify the first row of the set we create in the window function
            from auditor.student_card_school_history -- snapshots of the data as it existed before each change
            where state_id in (173, 199, 299, 1052, 2050, 3050, 3052, 4052, 5050)
            and district_id <> 88 -- not the district that I *meant* to change
    ) as x -- the alias
where row_number = 1; -- since we sort by audit_id descending, "row number 1" will be the *newest* row that matches the school ID