John Wessel wesseljt

Founder & CEO @ Agreeable Data

wesseljt / Final SQL View Example

Created May 7, 2024 22:32

	-- 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 / SQL Merge Logic Example

Created May 7, 2024 22:29

	-- 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 / SQL Stitching Logic Example

Created May 7, 2024 22:26

	-- 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 / SQL Initial User Mapping

Created May 7, 2024 22:21

	-- 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 / shopify_order_json

Created February 26, 2019 20:22