wesseljt

-- Create or replace the view with complex logic to select unique main user IDs
CREATE OR REPLACE VIEW final_user_id_mapping AS
WITH detailed_user_mappings AS (
    -- Extract detailed information including all timestamps and sources
    SELECT
        m.user_id,
        u.merged_id_array,
        MIN(u.earliest_mapping_timestamp) AS first_mapping,
        MAX(u.last_activity_timestamp) AS last_activity,
        json_agg(u.audit_details) AS audit_details,

wesseljt

-- Create a table to store the final merged user IDs
CREATE TEMPORARY TABLE merged_user_ids (
    user_id INT,
    merged_id_array INT[],
    earliest_mapping_timestamp TIMESTAMP,
    audit_details JSON
);

-- Recursive query to find all connected components (clusters of user IDs)
WITH RECURSIVE recursive_user_mappings AS (

wesseljt

-- Create the stitching table to store results
CREATE TEMPORARY TABLE user_stitching (
    user_id INT,
    mapped_id INT,
    mapping_type VARCHAR(10),
    earliest_mapping_timestamp TIMESTAMP,
    last_activity_timestamp TIMESTAMP,
    is_active BOOLEAN,
    source VARCHAR(50)
);

wesseljt

-- Create a temporary table for initial user mapping based on user events
CREATE OR REPLACE TEMPORARY TABLE initial_user_mappings AS (
    SELECT
        source_id AS user_id,
        target_id AS mapped_id,
        MIN(event_timestamp) AS earliest_mapping_timestamp
    FROM user_event_data
    WHERE event_type = 'registration'
    GROUP BY source_id, target_id
);

wesseljt

{
   "id":820982911946154508,
   "email":"jon@doe.ca",
   "closed_at":null,
   "created_at":"2018-12-18T09:47:54-05:00",
   "updated_at":"2018-12-18T09:47:54-05:00",
   "number":234,
   "note":null,
   "token":"123456abcd",
   "gateway":null,