SET min_compress_block_size = 2604006, max_compress_block_size = 910796, max_block_size = 21072, min_external_table_block_size_bytes = 1, max_joined_block_size_rows = 20516, max_insert_threads = 2, max_threads = 3, max_parsing_threads = 1, max_read_buffer_size = 864865, connect_timeout_with_failover_ms = 2000, connect_timeout_with_failover_secure_ms = 3000, idle_connection_timeout = 36000, s3_max_get_rps = 1000000, s3_max_get_burst = 2000000, s3_max_put_rps = 1000000, s3_max_put_burst = 2000000, s3_check_objects_after_upload = true, max_remote_read_network_bandwidth = 1000000000000, max_remote_write_network_bandwidth = 1000000000000, max_local_read_bandwidth = 1000000000000, max_local_write_bandwidth = 1000000000000, stream_like_engine_allow_direct_select = true, replication_wait_for_inactive_replica_timeout = 30, min_count_to_compile_aggregate_expression = 0, group_by_two_level_threshold = 1000000, group_by_two_level_threshold_bytes = 23489985, distributed_aggregation_memory_efficient = false, allow_nonconst_timezone_arguments = true, group_by_use_nulls = true, min_chunk_bytes_for_parallel_parsing = 13099976, output_format_parallel_formatting = false, merge_tree_coarse_index_granularity = 22, min_bytes_to_use_direct_io = 3873612246, min_bytes_to_use_mmap_io = 10737418240, log_queries = true, insert_quorum_timeout = 60000, table_function_remote_max_addresses = 200, merge_tree_read_split_ranges_into_intersecting_and_non_intersecting_injection_probability = 0.8799999952316284, http_wait_end_of_query = true, http_response_buffer_size = 7404103, fsync_metadata = false, query_plan_join_swap_table = true, http_send_timeout = 60., http_receive_timeout = 60., use_index_for_in_with_subqueries_max_values = 1000000000, enable_zstd_qat_codec = true, enable_deflate_qpl_codec = true, opentelemetry_start_trace_probability = 0.10000000149011612, enable_vertical_final = false, max_rows_to_read = 20000000, max_bytes_to_read = 1000000000000, max_bytes_to_read_leaf = 1000000000000, max_rows_to_group_by = 10000000000, max_bytes_ratio_before_external_group_by = 0., max_rows_to_sort = 10000000000, max_bytes_to_sort = 10000000000, prefer_external_sort_block_bytes = 0, max_bytes_ratio_before_external_sort = 0.2, max_bytes_before_remerge_sort = 342051232, max_result_rows = 1000000000, max_result_bytes = 1000000000, max_execution_time = 60., max_execution_time_leaf = 600., max_execution_speed = 100000000000, max_execution_speed_bytes = 10000000000000, timeout_before_checking_execution_speed = 300., max_estimated_execution_time = 600., max_columns_to_read = 20000, max_temporary_columns = 20000, max_temporary_non_const_columns = 20000, max_rows_in_set = 10000000000, max_bytes_in_set = 10000000000, max_rows_in_join = 10000000000, max_bytes_in_join = 10000000000, join_algorithm = 'full_sorting_merge', cross_join_min_rows_to_compress = 100000000, cross_join_min_bytes_to_compress = 100000000, max_rows_to_transfer = 1000000000, max_bytes_to_transfer = 1000000000, max_rows_in_distinct = 10000000000, max_bytes_in_distinct = 10000000000, max_memory_usage = 10000000000, max_memory_usage_for_user = 19896743116, max_untracked_memory = 1048576, memory_profiler_step = 1048576, max_network_bandwidth = 100000000000, max_network_bytes = 1000000000000, max_network_bandwidth_for_user = 100000000000, max_network_bandwidth_for_all_users = 100000000000, max_temporary_data_on_disk_size_for_user = 100000000000, max_temporary_data_on_disk_size_for_query = 100000000000, max_backup_bandwidth = 100000000000, log_comment = '02789_functions_after_sorting_and_columns_with_same_names_bug_2.sql', send_logs_level = 'warning', prefer_localhost_replica = false, optimize_aggregation_in_order = true, aggregation_in_order_max_block_bytes = 26976857, read_in_order_two_level_merge_threshold = 98, max_hyperscan_regexp_length = 1000000, max_hyperscan_regexp_total_length = 10000000, allow_introspection_functions = true, database_atomic_wait_for_drop_and_detach_synchronously = true, optimize_trivial_approximate_count_query = true, optimize_or_like_chain = true, optimize_if_chain_to_multiif = true, optimize_if_transform_strings_to_enum = true, query_cache_max_size_in_bytes = 10000000, query_cache_max_entries = 100000, database_replicated_allow_replicated_engine_arguments = 1, distributed_ddl_entry_format_version = 6, external_storage_max_read_rows = 10000000000, external_storage_max_read_bytes = 10000000000, local_filesystem_read_method = 'read', remote_filesystem_read_prefetch = false, merge_tree_min_bytes_per_task_for_remote_reading = 16777216, merge_tree_compact_parts_min_granules_to_multibuffer_read = 42, async_insert_busy_timeout_max_ms = 5000, enable_filesystem_cache = true, enable_filesystem_cache_on_write_operations = true, throw_on_error_from_cache_on_write_operations = true, filesystem_cache_segments_batch_size = 3, use_page_cache_for_disks_without_file_cache = true, page_cache_inject_eviction = true, load_marks_asynchronously = true, allow_prefetched_read_pool_for_remote_filesystem = false, allow_prefetched_read_pool_for_local_filesystem = false, filesystem_prefetch_step_bytes = 104857600, filesystem_prefetch_max_memory_usage = 134217728, filesystem_prefetches_limit = 10, allow_deprecated_database_ordinary = true, max_streams_for_merge_tree_reading = 1000, optimize_sorting_by_input_stream_properties = false, insert_keeper_max_retries = 100, insert_keeper_retry_initial_backoff_ms = 1, insert_keeper_retry_max_backoff_ms = 10, insert_keeper_fault_injection_probability = 0.009999999776482582, ignore_drop_queries_probability = 0.20000000298023224, optimize_distinct_in_order = false, session_timezone = 'America/Mazatlan', optimize_extract_common_expressions = false;

create table test1 (
    `pt` String,
    `brand_name` String,
    `total_indirect_order_cnt` Float64,
    `total_indirect_gmv` Float64
) ENGINE = Memory;

create table test2 (
    `pt` String,
    `brand_name` String,
    `exposure_uv` Float64,
    `click_uv` Float64
) ENGINE = Memory;

INSERT INTO test1 (`pt`, `brand_name`, `total_indirect_order_cnt`, `total_indirect_gmv`) VALUES ('20230625', 'LINING', 2232, 1008710), ('20230625', 'adidas', 125, 58820), ('20230625', 'Nike', 1291, 1033020), ('20230626', 'Nike', 1145, 938926), ('20230626', 'LINING', 1904, 853336), ('20230626', 'adidas', 133, 62546), ('20220626', 'LINING', 3747, 1855203), ('20220626', 'Nike', 2295, 1742665), ('20220626', 'adidas', 302, 122388);

INSERT INTO test2 (`pt`, `brand_name`, `exposure_uv`, `click_uv`) VALUES ('20230625', 'Nike', 2012913, 612831),  ('20230625', 'adidas', 480277, 96176), ('20230625', 'LINING', 2474234, 627814), ('20230626', 'Nike', 1934666, 610770), ('20230626', 'adidas', 469904, 91117), ('20230626', 'LINING', 2285142, 599765), ('20220626', 'Nike', 2979656, 937166), ('20220626', 'adidas', 704751, 124250), ('20220626', 'LINING', 3163884, 1010221);

SELECT * FROM ( SELECT m0.pt AS pt ,m0.`uvctr` AS uvctr ,round(m1.uvctr,4) AS uvctr_hb_last_value ,round(m2.uvctr,4) AS uvctr_tb_last_value FROM ( SELECT m0.pt AS pt ,COALESCE(m0.brand_name,m1.brand_name) AS brand_name ,if(isNaN(`click_uv` / `exposure_uv`) OR isInfinite(`click_uv` / `exposure_uv`),NULL,`click_uv` / `exposure_uv`) AS `uvctr` FROM ( SELECT pt AS pt ,brand_name AS `brand_name` ,exposure_uv AS `exposure_uv` ,click_uv AS `click_uv` FROM test2 WHERE pt = '20230626' ) m0 FULL JOIN ( SELECT pt AS pt ,brand_name AS `brand_name` ,total_indirect_order_cnt AS `total_indirect_order_cnt` ,total_indirect_gmv AS `total_indirect_gmv` FROM test1 WHERE pt = '20230626' ) m1 ON m0.brand_name = m1.brand_name AND m0.pt = m1.pt ) m0 LEFT JOIN ( SELECT m0.pt AS pt ,if(isNaN(`click_uv` / `exposure_uv`) OR isInfinite(`click_uv` / `exposure_uv`),NULL,`click_uv` / `exposure_uv`) AS `uvctr` ,COALESCE(m0.brand_name,m1.brand_name) AS brand_name ,`exposure_uv` AS `exposure_uv` ,`click_uv` FROM ( SELECT pt AS pt ,brand_name AS `brand_name` ,exposure_uv AS `exposure_uv` ,click_uv AS `click_uv` FROM test2 WHERE pt = '20230625' ) m0 FULL JOIN ( SELECT pt AS pt ,brand_name AS `brand_name` ,total_indirect_order_cnt AS `total_indirect_order_cnt` ,total_indirect_gmv AS `total_indirect_gmv` FROM test1 WHERE pt = '20230625' ) m1 ON m0.brand_name = m1.brand_name AND m0.pt = m1.pt ) m1 ON m0.brand_name = m1.brand_name AND m0.pt = m1.pt LEFT JOIN ( SELECT m0.pt AS pt ,if(isNaN(`click_uv` / `exposure_uv`) OR isInfinite(`click_uv` / `exposure_uv`),NULL,`click_uv` / `exposure_uv`) AS `uvctr` ,COALESCE(m0.brand_name,m1.brand_name) AS brand_name ,`exposure_uv` AS `exposure_uv` ,`click_uv` FROM ( SELECT pt AS pt ,brand_name AS `brand_name` ,exposure_uv AS `exposure_uv` ,click_uv AS `click_uv` FROM test2 WHERE pt = '20220626' ) m0 FULL JOIN ( SELECT pt AS pt ,brand_name AS `brand_name` ,total_indirect_order_cnt AS `total_indirect_order_cnt` ,total_indirect_gmv AS `total_indirect_gmv` FROM test1 WHERE pt = '20220626' ) m1 ON m0.brand_name = m1.brand_name AND m0.pt = m1.pt ) m2 ON m0.brand_name = m2.brand_name AND m0.pt = m2.pt ) c0 ORDER BY pt ASC, uvctr DESC;