BigQuery Deduplication Extra Credit

BQDeduplicationExtraCredit.sql

WITH
  raw_person AS (
  SELECT
    1 AS id,
    'Joshua' AS first_name,
    'Trusty' AS last_name,
    'red' AS favorite_color,
    '09-14-2023' AS date
  UNION ALL
  SELECT
    1,
    'Joshua',
    'Trusty',
    'scarlet',
    '01-01-2023'
  UNION ALL
  SELECT
    2,
    'Misael',
    'Gonzelez',
    'red',
    '09-14-2023' ),
  person AS (
  SELECT
    GENERATE_UUID() AS uuid,
    *
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY first_name, last_name ORDER BY date DESC) AS row_num
    FROM
      raw_person )
  WHERE
    row_num = 1 )
SELECT
  * EXCEPT(row_num)
FROM
  person