Query I used to generate the spreadsheet for Kylee for the parent id cleanup

updated_parent_field.sql

with changed_data as (
    select
           ah.account_id,
           ah.created_by_id as edited_by_id,
           ah.created_date as edited_date,
           'Remind API User' as edited_by,
           'Parent Account' as field_event,
           ah.new_value as cur_value,  -- for clarity we are reversing the change in the row we are looking at, so the new
           ah.old_value as new_value   -- value in the row is the value currently in SFDC, and the old is what we want after
      from salesforce_ft.account_history ah
     where ah.created_by_id = '0052J000008c1arQAA' -- only the remind API user
       and ah.field = 'Parent'  -- only changes to the parent field
       and is_deleted = false -- rows that haven't been deleted from history
       and len(ah.new_value) = 18 -- fun fact: the history table contains both the account id and the name change
       and ah.new_value like '00%' -- as two separate entries, since I need the ids, restrict to just those.
),
accounts as (
    select a.id, a.dw_organization_id_c, a.name, a.parent_id, a.is_deleted, a.owner_name_c
      from salesforce_ft.account a
)
select
       substring(c.account_id, 1, 15) as account_id, -- substring in redshift is 1 indexed, not 0
       a_main.dw_organization_id_c as organization_id,
       a_main.name as account_name,
       a_main.owner_name_c as account_owner,
       c.edited_by,
       c.edited_date,
       c.field_event,
       substring(c.cur_value, 1, 15) as current_value_parent_account_id, -- substring in redshift is 1 indexed, not 0
       a_cur.name as current_value_parent_account,
       a_new.name as new_value_parent_account,
       substring(c.new_value, 1, 15) as new_value_parent_account_id, -- substring in redshift is 1 indexed, not 0
       c.account_id as account_id_18_char,
       c.cur_value as current_value_parent_id_18_char,
       c.new_value as new_value_parent_id_18_char,
       case
           when lower(current_value_parent_account_id) = lower(new_value_parent_account_id) then 'Case Sensitive'
           else ''
       end as issue
  from changed_data c
  left outer join accounts a_main on c.account_id = a_main.id
  left outer join accounts a_cur on c.cur_value = a_cur.id
  left outer join accounts a_new on c.new_value = a_new.id
 where nvl(a_main.is_deleted, false) = false
   and nvl(a_cur.is_deleted, false) = false
   and nvl(a_new.is_deleted, false) = false -- skip accounts where any have been deleted (just in case)
   and a_main.parent_id = c.cur_value -- only keep rows that haven't changed since
  ;