Research tool

China Plus One, 2016–2017 vs 2024–2025

Origin shares and a shipper-string versus load-port split for ten large US ocean consignees. Snapshot generated 2026-08-23T08:20:11+00:00 on kirchner.bols. Paper: Did large US importers replace Chinese factories, or only Chinese ports?

Re-run: php scripts/run-china-plus-one.php. Snapshot: config/research-china-plus-one.json.

Unique BOLs 2016–2025

137,135,281

Mean Δ China share

-3.3 pp

Mean Δ Vietnam share

+6.7 pp

Plus-One bills, new shipper string

88.0%

Corpus origin shares (unique ocean bills)

Year	Bills	China	Vietnam	Mexico	India	Hong Kong
2016	10,904,088	40.7%	3.5%	1.5%	3.6%	3.0%
2017	11,343,531	41.0%	3.8%	1.5%	3.7%	2.7%
2018	12,130,965	41.7%	4.0%	1.5%	3.9%	2.5%
2019	11,991,741	38.8%	5.1%	1.6%	4.2%	2.1%
2020	12,621,341	43.7%	5.7%	1.4%	3.7%	1.5%
2021	13,801,384	43.0%	5.7%	1.4%	4.4%	1.4%
2022	13,937,256	42.3%	6.3%	1.2%	4.7%	1.1%
2023	13,231,810	42.6%	6.0%	1.3%	4.7%	0.9%
2024	17,331,599	58.0%	5.0%	0.8%	3.5%	0.6%
2025	20,019,139	59.0%	5.9%	0.6%	3.4%	0.4%

Firm origin shares (2016–17 vs 2024–25)

Importer	Pre bills	Post bills	Δ CN	Δ VN	Δ MX	Δ IN	Plus-One, new string
Walmart	2,470	61,494	+75.2 pp	0.0 pp	+0.1 pp	-0.3 pp	100.0%
The Home Depot	5,079	76,551	-41.5 pp	+24.3 pp	0.0 pp	-0.3 pp	94.7%
IKEA	133,231	274,258	+1.6 pp	+0.7 pp	0.0 pp	+3.3 pp	90.3%
Nike	94	834	+23.4 pp	+0.0 pp	+0.1 pp	+19.8 pp	100.0%
Tesla	2,483	13,404	+20.8 pp	+5.9 pp	+3.2 pp	+2.2 pp	100.0%
Costco	13,501	8,321	-1.0 pp	+1.6 pp	0.0 pp	+0.9 pp	48.9%
Toyota	4,661	5,370	+2.5 pp	0.0 pp	+0.6 pp	0.0 pp	100.0%
Mattel	15,432	7,594	-28.9 pp	+16.5 pp	+6.5 pp	+0.5 pp	91.9%
lululemon	2,138	3,083	-5.1 pp	+10.9 pp	+0.1 pp	0.0 pp	89.9%
Target	9,409	878	-52.9 pp	+0.0 pp	0.0 pp	+6.9 pp	100.0%

pooled 2016–2017 and 2024–2025 unique BOLs each >= 500; decomposition mean restricted to firms with >= 100 Plus-One (VN/MX/IN) BOLs in 2024–2025

Queries (all 93)

check_0_coverage · 1 rows

SELECT min(actual_arrival_date) AS min_date, max(actual_arrival_date) AS max_date,
  count() AS rows, uniqExact(bill_of_lading) AS unique_bols
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')

check_0b_country_values · 6 rows

SELECT country, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (match(upperUTF8(country), 'CHINA|VIET|MEXICO|INDIA|HONG KONG') OR match(upperUTF8(country), '^(CN|VN|MX|IN|HK)(,|\s|$)'))
GROUP BY country ORDER BY bols DESC LIMIT 40

check_0c_corpus_origin · 60 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_1_names_walmart · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'WALMART') OR startsWith(upperUTF8(consignee_name), 'WAL-MART'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_home_depot · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'HOME DEPOT') OR startsWith(upperUTF8(consignee_name), 'THE HOME DEPOT'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_ikea · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'IKEA'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_nike · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'NIKE'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_tesla · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'TESLA'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_costco · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'COSTCO'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_toyota · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'TOYOTA MOTOR'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_mattel · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'MATTEL'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_lululemon · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'LULULEMON'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_1_names_target · 30 rows

SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2017-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2024-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExact(bill_of_lading) AS bols_2016_2025
FROM bols
WHERE actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'TARGET'))
GROUP BY name
ORDER BY bols_2016_2025 DESC
LIMIT 30

check_2_yearly_walmart · 44 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_walmart · 46 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_walmart · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_walmart · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_walmart · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_walmart · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_walmart · 0 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_walmart · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART PUERTO RICO','WALMART PUERTO RICO INC','WALMART GLOBAL PROCUREMENT STORES I','WALMART PUERTO RICO MERCHANT NUMBER','WALMART GLOBAL PROCUREMENT STORES INCORPORATED','WALMART PUERTO RICO TAX ID','WALMART')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_home_depot · 47 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_home_depot · 57 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_home_depot · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_home_depot · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_home_depot · 4 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_home_depot · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_home_depot · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_home_depot · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC','THE HOME DEPOT','HOME DEPOT PRODUCT AUTHORITY LLC','THE HOME DEPOT USA','THE HOME DEPOT USA DBA THE COMPANY','HOME DEPOT','THE HOME DEPOT STORE','HOME DEPOT DBA YOUR OTHER WAREHOUSE','HOME DEPOT IDC','HOME DEPOT USA DBA CROWN BOLT')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_ikea · 58 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_ikea · 60 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_ikea · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_ikea · 4 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_ikea · 4 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_ikea · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_ikea · 5 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_ikea · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L','IKEA DISTRIBUTION SERVICES','IKEA DISTRIBUTION SERVICES INC S','IKEA SUPPLY AG IKEA DISTRIBUTION SE','IKEA WHOLESALE INC','IKEA DISTRIBUTION SERVICES INC P','IKEA DISTRIBUTION SERVICES INC M','IKEASUPPLY AG','IKEA PURCHASING SERVICES US INC','IKEA FOOD SUPPLY US INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_nike · 33 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_nike · 32 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_nike · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_nike · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_nike · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_nike · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_nike · 0 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_nike · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE AUSTRALIA PTY LTD','NIKE PHILIPPINES INC','NIKE NEW ZEALAND COMPANY','NIKE IHM INC','NIKE USA SHELBY','NIKE KOREA LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_tesla · 41 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_tesla · 41 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_tesla · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_tesla · 4 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_tesla · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_tesla · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_tesla · 0 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_tesla · 5 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA GIGAFACTORY','TESLA MOTOR INC','TESLA MOTORS NETHERLANDS B V','TESLA MOTORS','TESLA MANUFACTURING BRANDENBURG SE','TESLA MANUFACTURING BRANDENBURG SE TESLA','TESLA GDC NETHERLANDS','TESLA FREMONT POWERTRAIN DOCK FREMONT','TESLA MOTORS INCORPORATED','TESLA RAGOW WAREHOUSE AM')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_costco · 45 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_costco · 47 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_costco · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_costco · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_costco · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_costco · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_costco · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_costco · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE CORPORTION','COSTCO WHOLESALE','COSTCO WHOLESALE KOREA LTD','COSTCO WHOLESALE US','COSTCO WHOLESALE JAPAN LTD','COSTCO WHOLESALE TAIWAN INC','COSTCO DEPOT','COSTCO WHOLESALE USA','COSTCO PRESIDENT TAIWAN INC','COSTCO DRY','COSTCO WHOLEESALE CORP')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_toyota · 27 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_toyota · 36 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_toyota · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_toyota · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_toyota · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_toyota · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_toyota · 0 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_toyota · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TOYOTA MOTOR SALES USA','TOYOTA MOTOR SALES USA INC','TOYOTA MOTOR MANUFACTURING KENTUCKY INC','TOYOTA MOTOR MANUFACTURING INDIANA INC','TOYOTA MOTOR MANUFACTURING','TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC','TOYOTA MOTOR MANUFACTURING TEXAS INC','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING KENTUCKY','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING INDIANA')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_mattel · 55 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_mattel · 44 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_mattel · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_mattel · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_mattel · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_mattel · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_mattel · 4 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_mattel · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('MATTEL IMPORT SERVICES CORP','MATTEL INC','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED','MATTEL DO BRASIL LTDA','MATTEL EUROPA B V UKDC MATCH ROMAN','MATTEL EUROPA BV','MATTEL EUROPA B V CZDC','MATTEL EUROPA B V WEDC','MATTEL OYUNCAKCILIK TIC LTD','MATTEL EUROPA B V EUDC','MATTEL EUROPA B V ITPL')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_lululemon · 32 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_lululemon · 31 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_lululemon · 8 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_lululemon · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_lululemon · 3 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_lululemon · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_lululemon · 5 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_lululemon · 8 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('LULULEMON USA INC','LULULEMON ATHLETICA USA INC','LULULEMON TILBURY DC','LULULEMON COLUMBUS US DC','LULULEMON ATHLITICA USA INC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8

check_2_yearly_target · 40 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')) AS shippers
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2b_yearly_port_target · 45 rows

SELECT toYear(actual_arrival_date) AS year,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year, origin ORDER BY year, origin

check_2c_country_vs_port_target · 10 rows

SELECT toYear(actual_arrival_date) AS year,
  uniqExact(bill_of_lading) AS bols,
  uniqExactIf(bill_of_lading, (multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER')) != (multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER'))) AS bols_disagree
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year

check_3_decomp_country_target · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_3b_decomp_port_target · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(loading_port), 'HONG KONG|HKHKG|KWAI CHUNG'), 'HK',
  match(upperUTF8(loading_port), 'SHANGHAI|NINGBO|NINGPO|YANTIAN|SHEKOU|QINGDAO|XINGANG|TIANJIN|XIAMEN|DALIAN|NANSHA|GUANGZHOU|SHENZHEN|TAICANG|CHIWAN|YANGSHAN|QINZHOU|LIANYUNGANG|CHINA|, CN')
    AND NOT match(upperUTF8(loading_port), 'HONG KONG'), 'CN',
  match(upperUTF8(loading_port), 'HAI PHONG|HAIPHONG|HO CHI MINH|CAT LAI|VUNG TAU|CAI MEP|DANANG|DA NANG|SAIGON|SAI GON|DINH VU|LACH HUYEN|VIET NAM|VIETNAM|, VN'), 'VN',
  match(upperUTF8(loading_port), 'MANZANILLO|LAZARO|VERACRUZ|ALTAMIRA|ENSENADA|MEXICO|, MX'), 'MX',
  match(upperUTF8(loading_port), 'NHAVA|MUNDRA|CHENNAI|MADRAS|KOLKATA|CALCUTTA|PIPAVAV|HAZIRA|VISAKH|COCHIN|KOCHI|MUMBAI|BOMBAY|INDIA|, IN')
    AND NOT match(upperUTF8(loading_port), 'INDONESIA'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (
  SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'
),
post AS (
  SELECT shipper, origin, bol FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN','HK')
)
SELECT
  post.origin AS origin,
  uniqExact(post.bol) AS bols,
  uniqExactIf(post.bol, pre_cn.shipper != '') AS bols_same_string,
  uniqExactIf(post.bol, pre_cn.shipper = '') AS bols_new_string,
  uniqExact(post.shipper) AS shippers,
  uniqExactIf(post.shipper, pre_cn.shipper != '') AS shippers_same_string,
  uniqExactIf(post.shipper, pre_cn.shipper = '') AS shippers_new_string
FROM post
LEFT JOIN pre_cn ON post.shipper = pre_cn.shipper
GROUP BY origin ORDER BY origin

check_4_dual_target · 1 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_plus AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin IN ('VN','MX','IN'))
SELECT
  (SELECT count() FROM post_cn) AS shippers_cn_post,
  (SELECT count() FROM post_plus) AS shippers_plus_post,
  (SELECT count() FROM post_cn INNER JOIN post_plus USING shipper) AS shippers_dual

check_5_same_string_vn_target · 0 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
INNER JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
LEFT JOIN post_cn ON post_vn.shipper = post_cn.shipper
ORDER BY vn_bols_post DESC LIMIT 8

check_5b_new_string_vn_target · 2 rows

WITH base AS (
SELECT upperUTF8(trimBoth(shipper_name)) AS shipper,
  toYear(actual_arrival_date) AS y,
  multiIf(
  match(upperUTF8(country), 'HONG KONG') OR upperUTF8(country) IN ('HK','HK, HONG KONG'), 'HK',
  match(upperUTF8(country), 'CHINA') OR match(upperUTF8(country), '^CN(,|\\s|$)'), 'CN',
  match(upperUTF8(country), 'VIET') OR match(upperUTF8(country), '^VN(,|\\s|$)'), 'VN',
  match(upperUTF8(country), 'MEXICO') OR match(upperUTF8(country), '^MX(,|\\s|$)'), 'MX',
  (match(upperUTF8(country), 'INDIA') AND NOT match(upperUTF8(country), 'INDONESIA'))
    OR match(upperUTF8(country), '^IN(,|\\s|$)'), 'IN',
  'OTHER') AS origin,
  bill_of_lading AS bol
FROM bols
WHERE consignee_name IN ('TARGET STORES DIVISION OF TARGET C','TARGET STORE','TARGET MARKETING SYSTEMS INC','TARGET 33 SOUTH 6TH','TARGET MARKETING SYSTEM INC','TARGET LOS ANGELES','TARGET PRICE INC','TARGET CUSTOMS BROKERS INC','TARGET STEEL INC','TARGET COM','TARGETING A INC','TARGET STONE LLC')
  AND actual_arrival_date >= toDate('2016-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND trimBoth(shipper_name) != '' AND lengthUTF8(trimBoth(shipper_name)) >= 3 AND upperUTF8(trimBoth(shipper_name)) NOT IN ('N/A','NA','NONE','NULL','UNKNOWN','-','--','N.A.')
),
pre_cn AS (SELECT DISTINCT shipper FROM base WHERE y IN (2016, 2017) AND origin = 'CN'),
post_vn AS (
  SELECT shipper, uniqExact(bol) AS vn_bols FROM base WHERE y IN (2024, 2025) AND origin = 'VN' GROUP BY shipper
)
SELECT post_vn.shipper AS shipper, post_vn.vn_bols AS vn_bols_post
FROM post_vn
LEFT JOIN pre_cn ON post_vn.shipper = pre_cn.shipper
WHERE pre_cn.shipper = ''
ORDER BY vn_bols_post DESC LIMIT 8