SQL Initial User Mapping

-- 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
);

-- Enhance the initial mappings by including user's first login event
CREATE OR REPLACE TEMPORARY TABLE refined_user_mappings AS (
    SELECT
        i.user_id,
        i.mapped_id,
        i.earliest_mapping_timestamp,
        MIN(e.event_timestamp) AS first_login_timestamp
    FROM initial_user_mappings i
    JOIN user_event_data e ON i.user_id = e.user_id
    WHERE e.event_type = 'login'
    GROUP BY i.user_id, i.mapped_id, i.earliest_mapping_timestamp
);

-- Create a final mappings table with additional data verification
CREATE OR REPLACE TEMPORARY TABLE final_user_mappings AS (
    SELECT
        r.user_id,
        r.mapped_id,
        r.earliest_mapping_timestamp,
        r.first_login_timestamp,
        CASE 
            WHEN u.email_verified THEN 'verified'
            ELSE 'unverified'
        END AS email_status
    FROM refined_user_mappings r
    JOIN user_profiles u ON r.user_id = u.user_id
);

-- Example of using the final mappings for further analysis
-- Count the number of verified and unverified users
SELECT
    email_status,
    COUNT(*) AS num_users
FROM final_user_mappings
GROUP BY email_status;

-- Additional logic to handle different user types and status updates
INSERT INTO final_user_mappings (user_id, mapped_id, earliest_mapping_timestamp, first_login_timestamp, email_status)
SELECT
    user_id,
    mapped_id,
    earliest_mapping_timestamp,
    first_login_timestamp,
    'updated' AS email_status
FROM final_user_mappings
WHERE email_status = 'unverified' AND DATE(first_login_timestamp) > '2022-01-01';

-- Cleanup by dropping temporary tables if no longer needed
-- This is commented out; uncomment in production after use
-- DROP TABLE IF EXISTS initial_user_mappings;
-- DROP TABLE IF EXISTS refined_user_mappings;
-- DROP TABLE IF EXISTS final_user_mappings;