Research tool

Supplier turnover, 2014–2019 vs 2020–2025

Measures how often ten large US ocean importers added or left foreign suppliers (shipper names on CBP bills of lading). Every SQL statement below was executed against kirchner.bols on 2026-08-22T15:29:17+00:00. Paper: Did COVID permanently raise supplier turnover?

Re-run: php scripts/run-covid-supplier-churn.php on the Kirchner host. Snapshot file: config/research-covid-supplier-churn.json.

Unique BOLs 2014–2025

158,548,137

Mean Ξ” exit (9 firms)

-3.7%

Mean Ξ” add (9 firms)

70.8%

SQL checks in snapshot

76

Locked sample

Importer Industry Consignee names BOLs pre BOLs post Ξ” exit Ξ” add
Walmart General merchandise retail WALMART INC; WALMART STORES INC; WALMART GLOBAL LOGISTICS 120,723 182,609 2.5% -199.5%
The Home Depot Home improvement retail HOME DEPOT USA INC; HOME DEPOT USA; THE HOME DEPOT INC 124,053 107,568 -2.7% 261.8%
IKEA Furniture / home furnishings IKEA SUPPLY AG; IKEA DISTRIBUTION SERVICES INC; IKEA DISTRIBUTION SERVICES INC L 323,961 763,728 -2.2% -12.4%
Nike Athletic apparel and footwear NIKE USA INC; NIKE INC; NIKE EUROPEAN OPERATIONS 1,862 129,928 -53.2% 17.3%
Tesla Electric vehicles TESLA INC; TESLA MOTORS INC; TESLA MOTORS 6,774 36,111 9.4% 623.0%
Apple Consumer electronics APPLE INC; APPLE COMPUTER INC 6,160 1,083 β€” β€”
Intel Semiconductors INTEL CORP 2,448 3,446 18.7% -32.3%
Costco Warehouse club retail COSTCO WHOLESALE CORP; COSTCO WHOLESALE; COSTCO WHOLESALE COPRORATION 146,746 21,965 4.5% -8.7%
Toyota Automotive manufacturing 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 KENTUCKY; TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC; TOYOTA MOTOR MANUFACTURING INDIANA; TOYOTA MOTOR MANUFACTURING WEST VI; TOYOTA MOTOR CORP; TOYOTA MOTOR MANUFACTURING ALABAMA INC; TOYOTA MOTOR MANUFACTURING MISSISSI; TOYOTA MOTOR MANUFACTURING ALABAMA; TOYOTA MOTOR MANUFACTURING TEXAS IN 15,593 14,553 -4.6% -8.8%
Mattel Toys MATTEL INC; MATTEL IMPORT SERVICES CORP; MATTEL IMPORT SERVICES LLC; MATTEL INCORPORATED 42,531 32,910 -5.4% -3.7%

Yearly bills and suppliers

Walmart β€” yearly series

Usable year threshold: 6914.8 BOLs (median 27659).

YearBOLsSuppliers
2014 6,388 118
2015 4,929 184
2016 1,849 206
2017 437 42
2018 26,115 946
2019 81,006 2,813
2020 24,697 525
2021 32,546 626
2022 29,553 579
2023 34,568 508
2024 32,042 430
2025 29,203 399
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre no 118 53 119 44.9% 100.8%
2015β†’2016 pre no 184 130 152 70.7% 82.6%
2016β†’2017 pre no 206 190 26 92.2% 12.6%
2017β†’2018 pre no 42 28 932 66.7% 2,219.0%
2018β†’2019 pre yes 946 371 2,238 39.2% 236.6%
2019β†’2020 bridge yes 2,813 2,477 189 88.1% 6.7%
2020β†’2021 post yes 525 225 326 42.9% 62.1%
2021β†’2022 post yes 626 294 247 47.0% 39.5%
2022β†’2023 post yes 579 259 188 44.7% 32.5%
2023β†’2024 post yes 508 199 121 39.2% 23.8%
2024β†’2025 post yes 430 149 118 34.7% 27.4%
The Home Depot β€” yearly series

Usable year threshold: 1963.5 BOLs (median 7854).

YearBOLsSuppliers
2014 32,943 838
2015 2,819 360
2016 1,580 221
2017 2,653 287
2018 48,085 800
2019 35,978 681
2020 1,139 33
2021 20,337 731
2022 3,357 158
2023 7,289 137
2024 8,419 75
2025 67,027 1,078
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 838 653 175 77.9% 20.9%
2015β†’2016 pre no 360 272 133 75.6% 36.9%
2016β†’2017 pre no 221 98 164 44.3% 74.2%
2017β†’2018 pre yes 287 166 679 57.8% 236.6%
2018β†’2019 pre yes 800 336 217 42.0% 27.1%
2019β†’2020 bridge no 681 675 27 99.1% 4.0%
2020β†’2021 post no 33 20 718 60.6% 2,175.8%
2021β†’2022 post yes 731 662 89 90.6% 12.2%
2022β†’2023 post yes 158 74 53 46.8% 33.5%
2023β†’2024 post yes 137 78 16 56.9% 11.7%
2024β†’2025 post yes 75 24 1,027 32.0% 1,369.3%
IKEA β€” yearly series

Usable year threshold: 24421 BOLs (median 97684).

YearBOLsSuppliers
2014 32,756 794
2015 38,068 822
2016 38,123 709
2017 40,241 758
2018 77,059 1,123
2019 97,902 1,242
2020 97,466 1,259
2021 125,189 1,400
2022 139,556 1,701
2023 135,096 1,491
2024 119,611 1,449
2025 147,464 1,256
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 794 277 305 34.9% 38.4%
2015β†’2016 pre yes 822 326 213 39.7% 25.9%
2016β†’2017 pre yes 709 240 289 33.9% 40.8%
2017β†’2018 pre yes 758 188 553 24.8% 73.0%
2018β†’2019 pre yes 1,123 290 409 25.8% 36.4%
2019β†’2020 bridge yes 1,242 343 360 27.6% 29.0%
2020β†’2021 post yes 1,259 276 417 21.9% 33.1%
2021β†’2022 post yes 1,400 322 623 23.0% 44.5%
2022β†’2023 post yes 1,701 577 367 33.9% 21.6%
2023β†’2024 post yes 1,491 452 410 30.3% 27.5%
2024β†’2025 post yes 1,449 565 372 39.0% 25.7%
Nike β€” yearly series

Usable year threshold: 100 BOLs (median 41).

YearBOLsSuppliers
2014 1,584 21
2015 202 17
2016 23 14
2017 12 7
2018 20 7
2019 21 4
2020 59 13
2021 38,747 400
2022 90,520 606
2023 14 5
2024 683 22
2025 10 4
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 21 18 14 85.7% 66.7%
2015β†’2016 pre no 17 17 14 100.0% 82.4%
2016β†’2017 pre no 14 13 6 92.9% 42.9%
2017β†’2018 pre no 7 5 5 71.4% 71.4%
2018β†’2019 pre no 7 3 0 42.9% 0.0%
2019β†’2020 bridge no 4 1 10 25.0% 250.0%
2020β†’2021 post no 13 5 392 38.5% 3,015.4%
2021β†’2022 post yes 400 130 336 32.5% 84.0%
2022β†’2023 post no 606 602 1 99.3% 0.2%
2023β†’2024 post no 5 3 20 60.0% 400.0%
2024β†’2025 post no 22 20 2 90.9% 9.1%
Tesla β€” yearly series

Usable year threshold: 239.1 BOLs (median 956.5).

YearBOLsSuppliers
2014 2,036 122
2015 2,499 216
2016 1,332 129
2017 148 22
2018 476 25
2019 286 6
2020 249 30
2021 310 30
2022 581 53
2023 22,122 880
2024 1,602 41
2025 11,260 640
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 122 44 138 36.1% 113.1%
2015β†’2016 pre yes 216 126 39 58.3% 18.1%
2016β†’2017 pre no 129 122 15 94.6% 11.6%
2017β†’2018 pre no 22 17 20 77.3% 90.9%
2018β†’2019 pre yes 25 19 0 76.0% 0.0%
2019β†’2020 bridge yes 6 2 26 33.3% 433.3%
2020β†’2021 post yes 30 25 25 83.3% 83.3%
2021β†’2022 post yes 30 20 43 66.7% 143.3%
2022β†’2023 post yes 53 16 843 30.2% 1,590.6%
2023β†’2024 post yes 880 854 15 97.0% 1.7%
2024β†’2025 post yes 41 22 621 53.7% 1,514.6%
Apple β€” yearly series

Usable year threshold: 144.3 BOLs (median 577).

YearBOLsSuppliers
2014 978 14
2015 827 22
2016 1,827 41
2017 937 26
2018 805 37
2019 789 17
2020 266 32
2021 75 17
2022 20 12
2023 249 24
2024 109 24
2025 365 25
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 14 6 14 42.9% 100.0%
2015β†’2016 pre yes 22 10 29 45.5% 131.8%
2016β†’2017 pre yes 41 27 12 65.9% 29.3%
2017β†’2018 pre yes 26 9 20 34.6% 76.9%
2018β†’2019 pre yes 37 25 5 67.6% 13.5%
2019β†’2020 bridge yes 17 7 22 41.2% 129.4%
2020β†’2021 post no 32 25 10 78.1% 31.3%
2021β†’2022 post no 17 15 10 88.2% 58.8%
2022β†’2023 post no 12 6 18 50.0% 150.0%
2023β†’2024 post no 24 18 18 75.0% 75.0%
2024β†’2025 post no 24 19 20 79.2% 83.3%
Intel β€” yearly series

Usable year threshold: 126.6 BOLs (median 506.5).

YearBOLsSuppliers
2014 493 54
2015 284 41
2016 25 8
2017 141 24
2018 520 48
2019 986 62
2020 623 46
2021 194 21
2022 93 15
2023 730 68
2024 1,177 86
2025 629 69
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 54 29 16 53.7% 29.6%
2015β†’2016 pre no 41 36 3 87.8% 7.3%
2016β†’2017 pre no 8 5 21 62.5% 262.5%
2017β†’2018 pre yes 24 6 30 25.0% 125.0%
2018β†’2019 pre yes 48 21 35 43.8% 72.9%
2019β†’2020 bridge yes 62 34 18 54.8% 29.0%
2020β†’2021 post yes 46 33 8 71.7% 17.4%
2021β†’2022 post no 21 14 8 66.7% 38.1%
2022β†’2023 post no 15 4 57 26.7% 380.0%
2023β†’2024 post yes 68 33 51 48.5% 75.0%
2024β†’2025 post yes 86 50 33 58.1% 38.4%
Costco β€” yearly series

Usable year threshold: 1702.4 BOLs (median 6809.5).

YearBOLsSuppliers
2014 58,458 1,179
2015 49,894 1,115
2016 5,879 212
2017 6,875 220
2018 8,684 243
2019 16,957 548
2020 6,863 176
2021 6,756 231
2022 977 110
2023 1,052 152
2024 3,961 154
2025 2,373 98
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 1,179 583 519 49.4% 44.0%
2015β†’2016 pre yes 1,115 992 89 89.0% 8.0%
2016β†’2017 pre yes 212 102 110 48.1% 51.9%
2017β†’2018 pre yes 220 117 140 53.2% 63.6%
2018β†’2019 pre yes 243 149 454 61.3% 186.8%
2019β†’2020 bridge yes 548 463 91 84.5% 16.6%
2020β†’2021 post yes 176 109 164 61.9% 93.2%
2021β†’2022 post no 231 189 68 81.8% 29.4%
2022β†’2023 post no 110 78 120 70.9% 109.1%
2023β†’2024 post no 152 111 113 73.0% 74.3%
2024β†’2025 post yes 154 104 48 67.5% 31.2%
Toyota β€” yearly series

Usable year threshold: 634.9 BOLs (median 2539.5).

YearBOLsSuppliers
2014 2,594 19
2015 2,485 24
2016 2,770 19
2017 2,603 25
2018 2,690 20
2019 2,451 22
2020 2,098 25
2021 2,124 18
2022 2,205 16
2023 2,648 21
2024 2,348 16
2025 3,137 22
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 19 7 12 36.8% 63.2%
2015β†’2016 pre yes 24 10 5 41.7% 20.8%
2016β†’2017 pre yes 19 7 13 36.8% 68.4%
2017β†’2018 pre yes 25 14 9 56.0% 36.0%
2018β†’2019 pre yes 20 8 10 40.0% 50.0%
2019β†’2020 bridge yes 22 5 8 22.7% 36.4%
2020β†’2021 post yes 25 13 6 52.0% 24.0%
2021β†’2022 post yes 18 7 5 38.9% 27.8%
2022β†’2023 post yes 16 5 10 31.3% 62.5%
2023β†’2024 post yes 21 10 5 47.6% 23.8%
2024β†’2025 post yes 16 3 9 18.8% 56.3%
Mattel β€” yearly series

Usable year threshold: 1595.5 BOLs (median 6382).

YearBOLsSuppliers
2014 7,090 129
2015 7,961 203
2016 7,368 170
2017 8,002 204
2018 6,242 161
2019 5,868 107
2020 6,431 121
2021 5,791 150
2022 7,303 168
2023 6,333 151
2024 5,807 130
2025 1,265 66
t β†’ t+1 Period Usable From Left Added Exit Add
2014β†’2015 pre yes 129 38 112 29.5% 86.8%
2015β†’2016 pre yes 203 110 77 54.2% 37.9%
2016β†’2017 pre yes 170 71 105 41.8% 61.8%
2017β†’2018 pre yes 204 104 61 51.0% 29.9%
2018β†’2019 pre yes 161 81 27 50.3% 16.8%
2019β†’2020 bridge yes 107 45 59 42.1% 55.1%
2020β†’2021 post yes 121 47 76 38.8% 62.8%
2021β†’2022 post yes 150 63 81 42.0% 54.0%
2022β†’2023 post yes 168 65 48 38.7% 28.6%
2023β†’2024 post yes 151 61 40 40.4% 26.5%
2024β†’2025 post no 130 71 7 54.6% 5.4%

Queries (all 76)

Each block is the SQL that produced the numbered check. Run it on kirchner.bols to audit the snapshot.

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('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "min_date": "2014-01-01",
        "max_date": "2025-12-31",
        "rows": 173043175,
        "unique_bols": 158548137
    }
]
check_0b_yearly_corpus Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year, count() AS rows, uniqExact(bill_of_lading) AS unique_bols
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "rows": 11235494,
        "unique_bols": 10685269
    },
    {
        "year": 2015,
        "rows": 11317661,
        "unique_bols": 10763968
    },
    {
        "year": 2016,
        "rows": 11529522,
        "unique_bols": 10902902
    },
    {
        "year": 2017,
        "rows": 12185128,
        "unique_bols": 11342408
    },
    {
        "year": 2018,
        "rows": 12950900,
        "unique_bols": 12130298
    },
    {
        "year": 2019,
        "rows": 12649876,
        "unique_bols": 11991418
    },
    {
        "year": 2020,
        "rows": 13773553,
        "unique_bols": 12620768
    },
    {
        "year": 2021,
        "rows": 16422482,
        "unique_bols": 13798798
    },
    {
        "year": 2022,
        "rows": 16922821,
        "unique_bols": 13933642
    },
    {
        "year": 2023,
        "rows": 14553002,
        "unique_bols": 13230371
    },
    {
        "year": 2024,
        "rows": 19122279,
        "unique_bols": 17330181
    },
    {
        "year": 2025,
        "rows": 20380457,
        "unique_bols": 20017014
    }
]
check_1_names_walmart Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-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_2014_2025 DESC
LIMIT 20
[
    {
        "name": "WALMART INC",
        "bols_2014_2019": 106988,
        "bols_2020_2025": 180542,
        "bols_2014_2025": 287530
    },
    {
        "name": "WALMART STORES INC",
        "bols_2014_2019": 13735,
        "bols_2020_2025": 1054,
        "bols_2014_2025": 14789
    },
    {
        "name": "WALMART GLOBAL LOGISTICS",
        "bols_2014_2019": 0,
        "bols_2020_2025": 1013,
        "bols_2014_2025": 1013
    },
    {
        "name": "WALMART CHILE S A",
        "bols_2014_2019": 192,
        "bols_2020_2025": 471,
        "bols_2014_2025": 663
    },
    {
        "name": "WALMART STORE",
        "bols_2014_2019": 323,
        "bols_2020_2025": 2,
        "bols_2014_2025": 325
    },
    {
        "name": "WALMART PUERTO RICO",
        "bols_2014_2019": 39,
        "bols_2020_2025": 242,
        "bols_2014_2025": 281
    },
    {
        "name": "WALMART PUERTO RICO INC",
        "bols_2014_2019": 158,
        "bols_2020_2025": 14,
        "bols_2014_2025": 172
    },
    {
        "name": "WALMART GLOBAL PROCUREMENT STORES I",
        "bols_2014_2019": 37,
        "bols_2020_2025": 91,
        "bols_2014_2025": 128
    },
    {
        "name": "WALMART PUERTO RICO MERCHANT NUMBER",
        "bols_2014_2019": 0,
        "bols_2020_2025": 119,
        "bols_2014_2025": 119
    },
    {
        "name": "WALMART GLOBAL PROCUREMENT STORES INCORPORATED",
        "bols_2014_2019": 72,
        "bols_2020_2025": 38,
        "bols_2014_2025": 110
    },
    {
        "name": "WALMART",
        "bols_2014_2019": 93,
        "bols_2020_2025": 0,
        "bols_2014_2025": 93
    },
    {
        "name": "WALMART GENERAL DELIVERY",
        "bols_2014_2019": 0,
        "bols_2020_2025": 72,
        "bols_2014_2025": 72
    },
    {
        "name": "WALMART CHILE SA",
        "bols_2014_2019": 2,
        "bols_2020_2025": 61,
        "bols_2014_2025": 63
    },
    {
        "name": "WALMART STORES",
        "bols_2014_2019": 46,
        "bols_2020_2025": 2,
        "bols_2014_2025": 48
    },
    {
        "name": "WALMART GLOBAL PROCUREMENT STORES",
        "bols_2014_2019": 39,
        "bols_2020_2025": 0,
        "bols_2014_2025": 39
    },
    {
        "name": "WALMART STORES INCORPORATED",
        "bols_2014_2019": 38,
        "bols_2020_2025": 0,
        "bols_2014_2025": 38
    },
    {
        "name": "WALMART PUERTO RICO LOTE",
        "bols_2014_2019": 0,
        "bols_2020_2025": 36,
        "bols_2014_2025": 36
    },
    {
        "name": "WALMART PUERTO RICO TAX ID",
        "bols_2014_2019": 0,
        "bols_2020_2025": 35,
        "bols_2014_2025": 35
    },
    {
        "name": "WALMART STORE INC",
        "bols_2014_2019": 18,
        "bols_2020_2025": 15,
        "bols_2014_2025": 33
    },
    {
        "name": "WALMART 725",
        "bols_2014_2019": 26,
        "bols_2020_2025": 0,
        "bols_2014_2025": 26
    }
]
check_1_names_home_depot Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-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_2014_2025 DESC
LIMIT 20
[
    {
        "name": "HOME DEPOT USA INC",
        "bols_2014_2019": 113009,
        "bols_2020_2025": 25273,
        "bols_2014_2025": 138282
    },
    {
        "name": "HOME DEPOT USA",
        "bols_2014_2019": 11037,
        "bols_2020_2025": 68850,
        "bols_2014_2025": 79887
    },
    {
        "name": "THE HOME DEPOT INC",
        "bols_2014_2019": 16,
        "bols_2020_2025": 13467,
        "bols_2014_2025": 13483
    },
    {
        "name": "HOME DEPOT OF CANADA INC",
        "bols_2014_2019": 2589,
        "bols_2020_2025": 3618,
        "bols_2014_2025": 6207
    },
    {
        "name": "THE HOME DEPOT",
        "bols_2014_2019": 610,
        "bols_2020_2025": 719,
        "bols_2014_2025": 1329
    },
    {
        "name": "HOME DEPOT CANADA INC",
        "bols_2014_2019": 1227,
        "bols_2020_2025": 12,
        "bols_2014_2025": 1239
    },
    {
        "name": "THE HOME DEPOT USA",
        "bols_2014_2019": 450,
        "bols_2020_2025": 645,
        "bols_2014_2025": 1095
    },
    {
        "name": "HOME DEPOT PRODUCT AUTHORITY LLC",
        "bols_2014_2019": 858,
        "bols_2020_2025": 77,
        "bols_2014_2025": 935
    },
    {
        "name": "THE HOME DEPOT USA DBA THE COMPANY",
        "bols_2014_2019": 216,
        "bols_2020_2025": 316,
        "bols_2014_2025": 532
    },
    {
        "name": "HOME DEPOT",
        "bols_2014_2019": 369,
        "bols_2020_2025": 39,
        "bols_2014_2025": 408
    },
    {
        "name": "THE HOME DEPOT USA INC",
        "bols_2014_2019": 328,
        "bols_2020_2025": 17,
        "bols_2014_2025": 345
    },
    {
        "name": "THE HOME DEPOT STORE",
        "bols_2014_2019": 1,
        "bols_2020_2025": 211,
        "bols_2014_2025": 212
    },
    {
        "name": "HOME DEPOT IDC",
        "bols_2014_2019": 191,
        "bols_2020_2025": 0,
        "bols_2014_2025": 191
    },
    {
        "name": "THE HOME DEPOT OF CANADA INC",
        "bols_2014_2019": 42,
        "bols_2020_2025": 88,
        "bols_2014_2025": 130
    },
    {
        "name": "HOME DEPOT USA DBA THE COMPANY",
        "bols_2014_2019": 3,
        "bols_2020_2025": 86,
        "bols_2014_2025": 89
    },
    {
        "name": "THE HOME DEPOT USA DBA",
        "bols_2014_2019": 41,
        "bols_2020_2025": 32,
        "bols_2014_2025": 73
    },
    {
        "name": "HOME DEPOT SUPPLY",
        "bols_2014_2019": 73,
        "bols_2020_2025": 0,
        "bols_2014_2025": 73
    },
    {
        "name": "HOME DEPOT DC",
        "bols_2014_2019": 2,
        "bols_2020_2025": 66,
        "bols_2014_2025": 68
    },
    {
        "name": "HOME DEPOT STORE",
        "bols_2014_2019": 13,
        "bols_2020_2025": 37,
        "bols_2014_2025": 50
    },
    {
        "name": "HOME DEPOT DBA YOUR OTHER WAREHOUSE",
        "bols_2014_2019": 44,
        "bols_2020_2025": 3,
        "bols_2014_2025": 47
    }
]
check_1_names_ikea Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'IKEA'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "IKEA SUPPLY AG",
        "bols_2014_2019": 204869,
        "bols_2020_2025": 759034,
        "bols_2014_2025": 963903
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC",
        "bols_2014_2019": 72575,
        "bols_2020_2025": 3030,
        "bols_2014_2025": 75605
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC L",
        "bols_2014_2019": 46546,
        "bols_2020_2025": 1664,
        "bols_2014_2025": 48210
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES",
        "bols_2014_2019": 28706,
        "bols_2020_2025": 5220,
        "bols_2014_2025": 33926
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC S",
        "bols_2014_2019": 31639,
        "bols_2020_2025": 19,
        "bols_2014_2025": 31658
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC P",
        "bols_2014_2019": 19003,
        "bols_2020_2025": 8,
        "bols_2014_2025": 19011
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC M",
        "bols_2014_2019": 18072,
        "bols_2020_2025": 20,
        "bols_2014_2025": 18092
    },
    {
        "name": "IKEA SUPPLY AG IKEA DISTRIBUTION SE",
        "bols_2014_2019": 10004,
        "bols_2020_2025": 7936,
        "bols_2014_2025": 17940
    },
    {
        "name": "IKEA WHOLESALE INC",
        "bols_2014_2019": 14182,
        "bols_2020_2025": 2682,
        "bols_2014_2025": 16864
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC W",
        "bols_2014_2019": 10388,
        "bols_2020_2025": 4,
        "bols_2014_2025": 10392
    },
    {
        "name": "IKEA SUPPLY AG C\/O IKEA DISTRIBUTIO",
        "bols_2014_2019": 4185,
        "bols_2020_2025": 3,
        "bols_2014_2025": 4188
    },
    {
        "name": "IKEA COMPONENTS SRO",
        "bols_2014_2019": 0,
        "bols_2020_2025": 4084,
        "bols_2014_2025": 4084
    },
    {
        "name": "IKEASUPPLY AG",
        "bols_2014_2019": 2,
        "bols_2020_2025": 3557,
        "bols_2014_2025": 3559
    },
    {
        "name": "IKEA IMS NA LLC",
        "bols_2014_2019": 3291,
        "bols_2020_2025": 10,
        "bols_2014_2025": 3301
    },
    {
        "name": "IKEA PURCHASING SERVICES US INC",
        "bols_2014_2019": 2167,
        "bols_2020_2025": 732,
        "bols_2014_2025": 2899
    },
    {
        "name": "IKEA FOOD SUPPLY US INC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 2815,
        "bols_2014_2025": 2815
    },
    {
        "name": "IKEA DISTIBUTION SERVICES INC",
        "bols_2014_2019": 2545,
        "bols_2020_2025": 151,
        "bols_2014_2025": 2696
    },
    {
        "name": "IKEA SUPPY AG",
        "bols_2014_2019": 1477,
        "bols_2020_2025": 743,
        "bols_2014_2025": 2220
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INC BA",
        "bols_2014_2019": 1864,
        "bols_2020_2025": 2,
        "bols_2014_2025": 1866
    },
    {
        "name": "IKEA DISTRIBUTION SERVICES INCORPOR",
        "bols_2014_2019": 0,
        "bols_2020_2025": 1274,
        "bols_2014_2025": 1274
    }
]
check_1_names_nike Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'NIKE'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "NIKE USA INC",
        "bols_2014_2019": 45,
        "bols_2020_2025": 129472,
        "bols_2014_2025": 129517
    },
    {
        "name": "NIKE CANADA CORP",
        "bols_2014_2019": 16721,
        "bols_2020_2025": 13367,
        "bols_2014_2025": 30085
    },
    {
        "name": "NIKE DE MEXICO S DE R L DE C V",
        "bols_2014_2019": 2241,
        "bols_2020_2025": 562,
        "bols_2014_2025": 2803
    },
    {
        "name": "NIKE INC",
        "bols_2014_2019": 1742,
        "bols_2020_2025": 333,
        "bols_2014_2025": 2075
    },
    {
        "name": "NIKE CANADA LTD",
        "bols_2014_2019": 36,
        "bols_2020_2025": 597,
        "bols_2014_2025": 633
    },
    {
        "name": "NIKE EUROPEAN OPERATIONS",
        "bols_2014_2019": 75,
        "bols_2020_2025": 127,
        "bols_2014_2025": 202
    },
    {
        "name": "NIKE DE CHILE LIMITADA",
        "bols_2014_2019": 41,
        "bols_2020_2025": 159,
        "bols_2014_2025": 200
    },
    {
        "name": "NIKE DE MEXICO S DE RL DE CV",
        "bols_2014_2019": 42,
        "bols_2020_2025": 149,
        "bols_2014_2025": 191
    },
    {
        "name": "NIKE AUSTRALIA PTY LTD",
        "bols_2014_2019": 14,
        "bols_2020_2025": 121,
        "bols_2014_2025": 135
    },
    {
        "name": "NIKE IHM INC",
        "bols_2014_2019": 83,
        "bols_2020_2025": 27,
        "bols_2014_2025": 110
    },
    {
        "name": "NIKE USA SHELBY",
        "bols_2014_2019": 89,
        "bols_2020_2025": 6,
        "bols_2014_2025": 95
    },
    {
        "name": "NIKE PHILIPPINES INC",
        "bols_2014_2019": 5,
        "bols_2020_2025": 80,
        "bols_2014_2025": 85
    },
    {
        "name": "NIKE RETAIL B V",
        "bols_2014_2019": 0,
        "bols_2020_2025": 78,
        "bols_2014_2025": 78
    },
    {
        "name": "NIKE NEW ZEALAND COMPANY",
        "bols_2014_2019": 1,
        "bols_2020_2025": 76,
        "bols_2014_2025": 77
    },
    {
        "name": "NIKE JAPAN GROUP LLC",
        "bols_2014_2019": 17,
        "bols_2020_2025": 51,
        "bols_2014_2025": 68
    },
    {
        "name": "NIKE KOREA LLC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 59,
        "bols_2014_2025": 59
    },
    {
        "name": "NIKE CANADA SCARBOROUGH",
        "bols_2014_2019": 56,
        "bols_2020_2025": 0,
        "bols_2014_2025": 56
    },
    {
        "name": "NIKE SALES MALAYSIA SDN BHD",
        "bols_2014_2019": 0,
        "bols_2020_2025": 49,
        "bols_2014_2025": 49
    },
    {
        "name": "NIKEL PRECISION GROUP LLC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 39,
        "bols_2014_2025": 39
    },
    {
        "name": "NIKE DO BRASIL COMERCIO E PARTICIPACOES LTDA",
        "bols_2014_2019": 31,
        "bols_2020_2025": 7,
        "bols_2014_2025": 38
    }
]
check_1_names_tesla Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'TESLA'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "TESLA INC",
        "bols_2014_2019": 531,
        "bols_2020_2025": 35754,
        "bols_2014_2025": 36285
    },
    {
        "name": "TESLA MOTORS INC",
        "bols_2014_2019": 4432,
        "bols_2020_2025": 324,
        "bols_2014_2025": 4756
    },
    {
        "name": "TESLA MOTORS",
        "bols_2014_2019": 1812,
        "bols_2020_2025": 33,
        "bols_2014_2025": 1845
    },
    {
        "name": "TESLA MOTOR INC",
        "bols_2014_2019": 1427,
        "bols_2020_2025": 1,
        "bols_2014_2025": 1428
    },
    {
        "name": "TESLA GIGAFACTORY",
        "bols_2014_2019": 133,
        "bols_2020_2025": 1070,
        "bols_2014_2025": 1203
    },
    {
        "name": "TESLA MOTORS NETHERLANDS B V",
        "bols_2014_2019": 232,
        "bols_2020_2025": 369,
        "bols_2014_2025": 601
    },
    {
        "name": "TESLA MOTORS INCORPORATED",
        "bols_2014_2019": 455,
        "bols_2020_2025": 0,
        "bols_2014_2025": 455
    },
    {
        "name": "TESLA MANUFACTURING BRANDENBURG SE",
        "bols_2014_2019": 0,
        "bols_2020_2025": 403,
        "bols_2014_2025": 403
    },
    {
        "name": "TESLA MOTORS PTPDOCK L30",
        "bols_2014_2019": 233,
        "bols_2020_2025": 0,
        "bols_2014_2025": 233
    },
    {
        "name": "TESLA MANUFACTURING BRANDENBURG SE TESLA",
        "bols_2014_2019": 0,
        "bols_2020_2025": 231,
        "bols_2014_2025": 231
    },
    {
        "name": "TESLA \/U2013 WAREHOUSE ON WHEELS",
        "bols_2014_2019": 0,
        "bols_2020_2025": 227,
        "bols_2014_2025": 227
    },
    {
        "name": "TESLA FREMONT POWERTRAIN DOCK FREMONT",
        "bols_2014_2019": 227,
        "bols_2020_2025": 0,
        "bols_2014_2025": 227
    },
    {
        "name": "TESLA MOTOR VEH SOUTH DOCK",
        "bols_2014_2019": 196,
        "bols_2020_2025": 0,
        "bols_2014_2025": 196
    },
    {
        "name": "TESLA ENERGY",
        "bols_2014_2019": 0,
        "bols_2020_2025": 187,
        "bols_2014_2025": 187
    },
    {
        "name": "TESLA GDC NETHERLANDS",
        "bols_2014_2019": 0,
        "bols_2020_2025": 173,
        "bols_2014_2025": 173
    },
    {
        "name": "TESLA MOTORS PT DOCK L",
        "bols_2014_2019": 173,
        "bols_2020_2025": 0,
        "bols_2014_2025": 173
    },
    {
        "name": "TESLA FREMONT",
        "bols_2014_2019": 8,
        "bols_2020_2025": 151,
        "bols_2014_2025": 159
    },
    {
        "name": "TESLA SAN BERNARDINO DC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 157,
        "bols_2014_2025": 157
    },
    {
        "name": "TESLA LIVERMORE DISCOVERY",
        "bols_2014_2019": 90,
        "bols_2020_2025": 55,
        "bols_2014_2025": 145
    },
    {
        "name": "TESLA MOTOR NPI TESLA MOTORS NPI PWR TRN DOCK FREMOND",
        "bols_2014_2019": 143,
        "bols_2020_2025": 0,
        "bols_2014_2025": 143
    }
]
check_1_names_apple Β· 16 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'APPLE INC') OR startsWith(upperUTF8(consignee_name), 'APPLE COMPUTER') OR startsWith(upperUTF8(consignee_name), 'APPLE OPERATIONS') OR startsWith(upperUTF8(consignee_name), 'APPLE SALES'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "APPLE INC",
        "bols_2014_2019": 5907,
        "bols_2020_2025": 950,
        "bols_2014_2025": 6854
    },
    {
        "name": "APPLE COMPUTER INC",
        "bols_2014_2019": 253,
        "bols_2020_2025": 133,
        "bols_2014_2025": 386
    },
    {
        "name": "APPLE COMPUTER C O INGRAM MICRO",
        "bols_2014_2019": 71,
        "bols_2020_2025": 0,
        "bols_2014_2025": 71
    },
    {
        "name": "APPLE COMPUTERS SERVICE OP OCN PO BOX",
        "bols_2014_2019": 27,
        "bols_2020_2025": 0,
        "bols_2014_2025": 27
    },
    {
        "name": "APPLE OPERATIONS MEXICO S A",
        "bols_2014_2019": 10,
        "bols_2020_2025": 0,
        "bols_2014_2025": 10
    },
    {
        "name": "APPLE COMPUTERS SERVICE OP OCN",
        "bols_2014_2019": 5,
        "bols_2020_2025": 4,
        "bols_2014_2025": 9
    },
    {
        "name": "APPLE COMPUTER C O OHL",
        "bols_2014_2019": 6,
        "bols_2020_2025": 0,
        "bols_2014_2025": 6
    },
    {
        "name": "APPLE INC APOGAR",
        "bols_2014_2019": 0,
        "bols_2020_2025": 4,
        "bols_2014_2025": 4
    },
    {
        "name": "APPLE COMPUTER APPLECARE",
        "bols_2014_2019": 2,
        "bols_2020_2025": 0,
        "bols_2014_2025": 2
    },
    {
        "name": "APPLE INCT",
        "bols_2014_2019": 0,
        "bols_2020_2025": 1,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE OPERATIONS MEXICO SA PROL PASEO DE LA REFORMA",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE COMPUTER INGRAM BRANCH",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE OPERATIONS MEXICO S A DE C V",
        "bols_2014_2019": 0,
        "bols_2020_2025": 1,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE SALES INTERNATIONAL",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE OPERATIONS MEXICO SA DE CV",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "APPLE COMPUTER DIRECT SHIP",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    }
]
check_1_names_intel Β· 11 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'INTEL CORPORATION') OR startsWith(upperUTF8(consignee_name), 'INTEL CORP') OR startsWith(upperUTF8(consignee_name), 'INTEL AMERICAS'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "INTEL CORP",
        "bols_2014_2019": 2448,
        "bols_2020_2025": 3446,
        "bols_2014_2025": 5892
    },
    {
        "name": "INTEL AMERICAS INC",
        "bols_2014_2019": 8,
        "bols_2020_2025": 13,
        "bols_2014_2025": 21
    },
    {
        "name": "INTEL AMERICAS",
        "bols_2014_2019": 7,
        "bols_2020_2025": 0,
        "bols_2014_2025": 7
    },
    {
        "name": "INTEL CORPOATION",
        "bols_2014_2019": 4,
        "bols_2020_2025": 0,
        "bols_2014_2025": 4
    },
    {
        "name": "INTEL CORP D2 GATE\/D2 DOCK",
        "bols_2014_2019": 0,
        "bols_2020_2025": 3,
        "bols_2014_2025": 3
    },
    {
        "name": "INTEL CORPORATE INVENTORY SHIRA WOMACK THE HIBBERT GROUP",
        "bols_2014_2019": 2,
        "bols_2020_2025": 0,
        "bols_2014_2025": 2
    },
    {
        "name": "INTEL CORPORATI ON TECHNOLOGY MANUFACTURING ENGINEERING 5CU0W CHANDLER",
        "bols_2014_2019": 2,
        "bols_2020_2025": 0,
        "bols_2014_2025": 2
    },
    {
        "name": "INTEL CORPORATIO",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "INTEL CORPORATIOON FAB",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "INTEL CORP C\/O RINCHEM",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    },
    {
        "name": "INTEL CORP GLOBAL SERVER SUPPL",
        "bols_2014_2019": 1,
        "bols_2020_2025": 0,
        "bols_2014_2025": 1
    }
]
check_1_names_costco Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'COSTCO'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "COSTCO WHOLESALE CORP",
        "bols_2014_2019": 133310,
        "bols_2020_2025": 17432,
        "bols_2014_2025": 150742
    },
    {
        "name": "COSTCO WHOLESALE CANADA LTD",
        "bols_2014_2019": 31065,
        "bols_2020_2025": 63981,
        "bols_2014_2025": 95046
    },
    {
        "name": "COSTCO WHOLESALE",
        "bols_2014_2019": 13435,
        "bols_2020_2025": 779,
        "bols_2014_2025": 14214
    },
    {
        "name": "COSTCO WHOLESALE COPRORATION",
        "bols_2014_2019": 1,
        "bols_2020_2025": 3754,
        "bols_2014_2025": 3755
    },
    {
        "name": "COSTCO WHOLESALE CANADA",
        "bols_2014_2019": 656,
        "bols_2020_2025": 1106,
        "bols_2014_2025": 1762
    },
    {
        "name": "COSTCO WHOLESALE CORPORTION",
        "bols_2014_2019": 1231,
        "bols_2020_2025": 294,
        "bols_2014_2025": 1525
    },
    {
        "name": "COSTCO WHOLESALE KOREA LTD",
        "bols_2014_2019": 311,
        "bols_2020_2025": 734,
        "bols_2014_2025": 1045
    },
    {
        "name": "COSTCO WHOLESALE US",
        "bols_2014_2019": 22,
        "bols_2020_2025": 969,
        "bols_2014_2025": 991
    },
    {
        "name": "COSTCO WHOLESALE JAPAN LTD",
        "bols_2014_2019": 180,
        "bols_2020_2025": 609,
        "bols_2014_2025": 789
    },
    {
        "name": "COSTCO WHOLESALE USA",
        "bols_2014_2019": 362,
        "bols_2020_2025": 290,
        "bols_2014_2025": 652
    },
    {
        "name": "COSTCO WHOLESALE DEPOT",
        "bols_2014_2019": 51,
        "bols_2020_2025": 550,
        "bols_2014_2025": 601
    },
    {
        "name": "COSTCO PRESIDENT TAIWAN INC",
        "bols_2014_2019": 343,
        "bols_2020_2025": 179,
        "bols_2014_2025": 522
    },
    {
        "name": "COSTCO WHOLESALE TAIWAN INC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 441,
        "bols_2014_2025": 441
    },
    {
        "name": "COSTCO WHOLESALE US MIDWEST REGION",
        "bols_2014_2019": 2,
        "bols_2020_2025": 413,
        "bols_2014_2025": 415
    },
    {
        "name": "COSTCO WHOLESALES CORP",
        "bols_2014_2019": 19,
        "bols_2020_2025": 391,
        "bols_2014_2025": 410
    },
    {
        "name": "COSTCO DEPOT",
        "bols_2014_2019": 230,
        "bols_2020_2025": 165,
        "bols_2014_2025": 395
    },
    {
        "name": "COSTCO WHOLESALE VENDOR",
        "bols_2014_2019": 363,
        "bols_2020_2025": 18,
        "bols_2014_2025": 381
    },
    {
        "name": "COSTCO WHOLESALE INC",
        "bols_2014_2019": 371,
        "bols_2020_2025": 0,
        "bols_2014_2025": 371
    },
    {
        "name": "COSTCO DISTRIBUTION CENTER",
        "bols_2014_2019": 16,
        "bols_2020_2025": 326,
        "bols_2014_2025": 342
    },
    {
        "name": "COSTCO DRY",
        "bols_2014_2019": 144,
        "bols_2020_2025": 145,
        "bols_2014_2025": 289
    }
]
check_1_names_toyota Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'TOYOTA MOTOR'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "TOYOTA MOTOR SALES USA",
        "bols_2014_2019": 5909,
        "bols_2020_2025": 3813,
        "bols_2014_2025": 9722
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING CANADA",
        "bols_2014_2019": 2241,
        "bols_2020_2025": 2588,
        "bols_2014_2025": 4829
    },
    {
        "name": "TOYOTA MOTOR SALES USA INC",
        "bols_2014_2019": 1265,
        "bols_2020_2025": 2851,
        "bols_2014_2025": 4116
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING CANADA INC",
        "bols_2014_2019": 932,
        "bols_2020_2025": 1635,
        "bols_2014_2025": 2567
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING KENTUCKY INC",
        "bols_2014_2019": 536,
        "bols_2020_2025": 1743,
        "bols_2014_2025": 2279
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING INDIANA INC",
        "bols_2014_2019": 473,
        "bols_2020_2025": 1373,
        "bols_2014_2025": 1846
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING",
        "bols_2014_2019": 792,
        "bols_2020_2025": 955,
        "bols_2014_2025": 1747
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING WEST VIRGINIA INC",
        "bols_2014_2019": 626,
        "bols_2020_2025": 969,
        "bols_2014_2025": 1595
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING TEXAS INC",
        "bols_2014_2019": 391,
        "bols_2020_2025": 961,
        "bols_2014_2025": 1352
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING KENTUCKY",
        "bols_2014_2019": 1192,
        "bols_2020_2025": 105,
        "bols_2014_2025": 1297
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC",
        "bols_2014_2019": 424,
        "bols_2020_2025": 770,
        "bols_2014_2025": 1194
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING INDIANA",
        "bols_2014_2019": 879,
        "bols_2020_2025": 24,
        "bols_2014_2025": 903
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING WEST VI",
        "bols_2014_2019": 868,
        "bols_2020_2025": 3,
        "bols_2014_2025": 871
    },
    {
        "name": "TOYOTA MOTOR CORP",
        "bols_2014_2019": 683,
        "bols_2020_2025": 153,
        "bols_2014_2025": 836
    },
    {
        "name": "TOYOTA MOTOR MFG CANADA INC",
        "bols_2014_2019": 781,
        "bols_2020_2025": 19,
        "bols_2014_2025": 800
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING ALABAMA INC",
        "bols_2014_2019": 228,
        "bols_2020_2025": 502,
        "bols_2014_2025": 730
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING MISSISSI",
        "bols_2014_2019": 640,
        "bols_2020_2025": 1,
        "bols_2014_2025": 641
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING CANADA I",
        "bols_2014_2019": 600,
        "bols_2020_2025": 5,
        "bols_2014_2025": 605
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING ALABAMA",
        "bols_2014_2019": 392,
        "bols_2020_2025": 130,
        "bols_2014_2025": 522
    },
    {
        "name": "TOYOTA MOTOR MANUFACTURING TEXAS IN",
        "bols_2014_2019": 295,
        "bols_2020_2025": 200,
        "bols_2014_2025": 495
    }
]
check_1_names_mattel Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'MATTEL'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "MATTEL INC",
        "bols_2014_2019": 25248,
        "bols_2020_2025": 1749,
        "bols_2014_2025": 26997
    },
    {
        "name": "MATTEL IMPORT SERVICES CORP",
        "bols_2014_2019": 10486,
        "bols_2020_2025": 16408,
        "bols_2014_2025": 26894
    },
    {
        "name": "MATTEL IMPORT SERVICES LLC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 13708,
        "bols_2014_2025": 13708
    },
    {
        "name": "MATTEL INCORPORATED",
        "bols_2014_2019": 6798,
        "bols_2020_2025": 1045,
        "bols_2014_2025": 7843
    },
    {
        "name": "MATTEL CANADA INC",
        "bols_2014_2019": 175,
        "bols_2020_2025": 774,
        "bols_2014_2025": 949
    },
    {
        "name": "MATTEL DE MEXICO SA DE CV",
        "bols_2014_2019": 576,
        "bols_2020_2025": 0,
        "bols_2014_2025": 576
    },
    {
        "name": "MATTEL DE MEXICO S A DE C V",
        "bols_2014_2019": 555,
        "bols_2020_2025": 20,
        "bols_2014_2025": 575
    },
    {
        "name": "MATTEL DO BRASIL LTDA",
        "bols_2014_2019": 13,
        "bols_2020_2025": 303,
        "bols_2014_2025": 316
    },
    {
        "name": "MATTEL EUROPA B V UKDC MATCH ROMAN",
        "bols_2014_2019": 1,
        "bols_2020_2025": 181,
        "bols_2014_2025": 182
    },
    {
        "name": "MATTEL PTY LTD",
        "bols_2014_2019": 24,
        "bols_2020_2025": 103,
        "bols_2014_2025": 127
    },
    {
        "name": "MATTEL EUROPA BV",
        "bols_2014_2019": 66,
        "bols_2020_2025": 54,
        "bols_2014_2025": 120
    },
    {
        "name": "MATTEL IMPORT SEVICES CORP",
        "bols_2014_2019": 85,
        "bols_2020_2025": 30,
        "bols_2014_2025": 115
    },
    {
        "name": "MATTEL IMPORT SERVICES",
        "bols_2014_2019": 1,
        "bols_2020_2025": 110,
        "bols_2014_2025": 111
    },
    {
        "name": "MATTEL EUROPA B V FRDC",
        "bols_2014_2019": 3,
        "bols_2020_2025": 101,
        "bols_2014_2025": 104
    },
    {
        "name": "MATTEL EUROPA B V CZDC",
        "bols_2014_2019": 5,
        "bols_2020_2025": 83,
        "bols_2014_2025": 88
    },
    {
        "name": "MATTEL CANADA",
        "bols_2014_2019": 1,
        "bols_2020_2025": 80,
        "bols_2014_2025": 81
    },
    {
        "name": "MATTEL TOYS",
        "bols_2014_2019": 45,
        "bols_2020_2025": 22,
        "bols_2014_2025": 67
    },
    {
        "name": "MATTEL EUROPA B V WEDC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 64,
        "bols_2014_2025": 64
    },
    {
        "name": "MATTEL OYUNCAKCILIK TIC LTD",
        "bols_2014_2019": 2,
        "bols_2020_2025": 60,
        "bols_2014_2025": 62
    },
    {
        "name": "MATTEL EUROPA B V EUDC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 61,
        "bols_2014_2025": 61
    }
]
check_1_names_starbucks Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'STARBUCKS'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY",
        "bols_2014_2019": 9345,
        "bols_2020_2025": 64,
        "bols_2014_2025": 9409
    },
    {
        "name": "STARBUCKS COFFEE COMPANY",
        "bols_2014_2019": 5648,
        "bols_2020_2025": 173,
        "bols_2014_2025": 5821
    },
    {
        "name": "STARBUCKS CORP",
        "bols_2014_2019": 2733,
        "bols_2020_2025": 63,
        "bols_2014_2025": 2796
    },
    {
        "name": "STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 746,
        "bols_2020_2025": 228,
        "bols_2014_2025": 974
    },
    {
        "name": "STARBUCKS COFFEE CO",
        "bols_2014_2019": 864,
        "bols_2020_2025": 0,
        "bols_2014_2025": 864
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY C\/O STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 834,
        "bols_2020_2025": 0,
        "bols_2014_2025": 834
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY JOINTLY AND SEVERALLY WITH STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 677,
        "bols_2020_2025": 0,
        "bols_2014_2025": 677
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY C STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 96,
        "bols_2020_2025": 0,
        "bols_2014_2025": 96
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY C O STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 88,
        "bols_2020_2025": 0,
        "bols_2014_2025": 88
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY JO INTLY AND SEVERALLY WITH STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 65,
        "bols_2020_2025": 0,
        "bols_2014_2025": 65
    },
    {
        "name": "STARBUCKS COFFEE",
        "bols_2014_2019": 49,
        "bols_2020_2025": 0,
        "bols_2014_2025": 49
    },
    {
        "name": "STARBUCKS 2401 S UTAH",
        "bols_2014_2019": 49,
        "bols_2020_2025": 0,
        "bols_2014_2025": 49
    },
    {
        "name": "STARBUCKS COFFE TRADE COMPANY C\/O STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 38,
        "bols_2020_2025": 0,
        "bols_2014_2025": 38
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY C\/ STARBUCKS MANUFACTURING CORP",
        "bols_2014_2019": 29,
        "bols_2020_2025": 0,
        "bols_2014_2025": 29
    },
    {
        "name": "STARBUCKS MANUFACTURING EMEA B V AS AGENT OF HACO ASIA PACIFIC SDN BHD",
        "bols_2014_2019": 29,
        "bols_2020_2025": 0,
        "bols_2014_2025": 29
    },
    {
        "name": "STARBUCKS DISTRIBUTION CENTER",
        "bols_2014_2019": 0,
        "bols_2020_2025": 22,
        "bols_2014_2025": 22
    },
    {
        "name": "STARBUCKS COFFEE SEATTLE",
        "bols_2014_2019": 21,
        "bols_2020_2025": 0,
        "bols_2014_2025": 21
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY JO MANUFACTURING CORP",
        "bols_2014_2019": 18,
        "bols_2020_2025": 0,
        "bols_2014_2025": 18
    },
    {
        "name": "STARBUCKS COFFEE TRADE COMPANY JOINTLY AND SEVERALLY WITH STARBUCKMANUFACTURING CORP",
        "bols_2014_2019": 17,
        "bols_2020_2025": 0,
        "bols_2014_2025": 17
    },
    {
        "name": "STARBUCKS COFFEE TRADE CO",
        "bols_2014_2019": 11,
        "bols_2020_2025": 2,
        "bols_2014_2025": 13
    }
]
check_1_names_sephora Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'SEPHORA'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "SEPHORA USA INC",
        "bols_2014_2019": 1738,
        "bols_2020_2025": 1775,
        "bols_2014_2025": 3513
    },
    {
        "name": "SEPHORA USA LLC",
        "bols_2014_2019": 2789,
        "bols_2020_2025": 283,
        "bols_2014_2025": 3072
    },
    {
        "name": "SEPHORA INC",
        "bols_2014_2019": 303,
        "bols_2020_2025": 12,
        "bols_2014_2025": 315
    },
    {
        "name": "SEPHORA USA",
        "bols_2014_2019": 252,
        "bols_2020_2025": 0,
        "bols_2014_2025": 252
    },
    {
        "name": "SEPHORA BEAUTY CANADA INC",
        "bols_2014_2019": 3,
        "bols_2020_2025": 87,
        "bols_2014_2025": 90
    },
    {
        "name": "SEPHORA UTAH DISTRIBUTION CENTER",
        "bols_2014_2019": 71,
        "bols_2020_2025": 0,
        "bols_2014_2025": 71
    },
    {
        "name": "SEPHORA MEXICO S DE R L DE C V",
        "bols_2014_2019": 46,
        "bols_2020_2025": 0,
        "bols_2014_2025": 46
    },
    {
        "name": "SEPHORA DISTRIBUTION CTR",
        "bols_2014_2019": 42,
        "bols_2020_2025": 2,
        "bols_2014_2025": 44
    },
    {
        "name": "SEPHORA MEXICO S DE RL DE CV",
        "bols_2014_2019": 1,
        "bols_2020_2025": 29,
        "bols_2014_2025": 30
    },
    {
        "name": "SEPHORA UTAH DIST CENTER",
        "bols_2014_2019": 30,
        "bols_2020_2025": 0,
        "bols_2014_2025": 30
    },
    {
        "name": "SEPHORA MARYLAND DIST CENTER",
        "bols_2014_2019": 25,
        "bols_2020_2025": 0,
        "bols_2014_2025": 25
    },
    {
        "name": "SEPHORA LOGISTICS",
        "bols_2014_2019": 3,
        "bols_2020_2025": 21,
        "bols_2014_2025": 24
    },
    {
        "name": "SEPHORA UTAH DC",
        "bols_2014_2019": 22,
        "bols_2020_2025": 1,
        "bols_2014_2025": 23
    },
    {
        "name": "SEPHORA DISTRIBUTION CENTER",
        "bols_2014_2019": 18,
        "bols_2020_2025": 0,
        "bols_2014_2025": 18
    },
    {
        "name": "SEPHORA BEAUTY CANADA",
        "bols_2014_2019": 0,
        "bols_2020_2025": 13,
        "bols_2014_2025": 13
    },
    {
        "name": "SEPHORA MARYLAND DIST CENTER 4622MERCEDES",
        "bols_2014_2019": 11,
        "bols_2020_2025": 0,
        "bols_2014_2025": 11
    },
    {
        "name": "SEPHORA 4622 MERCEDES",
        "bols_2014_2019": 10,
        "bols_2020_2025": 0,
        "bols_2014_2025": 10
    },
    {
        "name": "SEPHORA UDC",
        "bols_2014_2019": 9,
        "bols_2020_2025": 0,
        "bols_2014_2025": 9
    },
    {
        "name": "SEPHORA PERRYMAN DC",
        "bols_2014_2019": 6,
        "bols_2020_2025": 1,
        "bols_2014_2025": 7
    },
    {
        "name": "SEPHORA USA SEPHORA PRUDENTIAL CENTER STORE",
        "bols_2014_2019": 7,
        "bols_2020_2025": 0,
        "bols_2014_2025": 7
    }
]
check_1_names_lululemon Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'LULULEMON'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "LULULEMON USA INC",
        "bols_2014_2019": 5316,
        "bols_2020_2025": 3210,
        "bols_2014_2025": 8526
    },
    {
        "name": "LULULEMON ATHLETICA CANADA INC",
        "bols_2014_2019": 2287,
        "bols_2020_2025": 4066,
        "bols_2014_2025": 6353
    },
    {
        "name": "LULULEMON ATHLETICA USA INC",
        "bols_2014_2019": 2609,
        "bols_2020_2025": 18,
        "bols_2014_2025": 2627
    },
    {
        "name": "LULULEMON TILBURY DC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 174,
        "bols_2014_2025": 174
    },
    {
        "name": "LULULEMON ATHLETICA",
        "bols_2014_2019": 47,
        "bols_2020_2025": 67,
        "bols_2014_2025": 114
    },
    {
        "name": "LULULEMON ATHLITICA USA INC",
        "bols_2014_2019": 81,
        "bols_2020_2025": 0,
        "bols_2014_2025": 81
    },
    {
        "name": "LULULEMON COLUMBUS US DC",
        "bols_2014_2019": 1,
        "bols_2020_2025": 39,
        "bols_2014_2025": 40
    },
    {
        "name": "LULULEMON HK LIMITED",
        "bols_2014_2019": 4,
        "bols_2020_2025": 26,
        "bols_2014_2025": 30
    },
    {
        "name": "LULULEMON ATLETHICA CANADA INC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 27,
        "bols_2014_2025": 27
    },
    {
        "name": "LULULEMON ATHLETICA CANADA",
        "bols_2014_2019": 26,
        "bols_2020_2025": 0,
        "bols_2014_2025": 26
    },
    {
        "name": "LULULEMON ATHLETICA CH GMBH C O BL ECKMANN NEDERLAND B V CONRADWEG",
        "bols_2014_2019": 0,
        "bols_2020_2025": 24,
        "bols_2014_2025": 24
    },
    {
        "name": "LULULEMON ATHLETICA UK LTD",
        "bols_2014_2019": 0,
        "bols_2020_2025": 23,
        "bols_2014_2025": 23
    },
    {
        "name": "LULULEMON ATHLETICA 35TORDC",
        "bols_2014_2019": 0,
        "bols_2020_2025": 20,
        "bols_2014_2025": 20
    },
    {
        "name": "LULULEMON ATHLETHICA CANADA INC",
        "bols_2014_2019": 15,
        "bols_2020_2025": 0,
        "bols_2014_2025": 15
    },
    {
        "name": "LULULEMON CA RETAIL MILTON",
        "bols_2014_2019": 0,
        "bols_2020_2025": 13,
        "bols_2014_2025": 13
    },
    {
        "name": "LULULEMON ATHLETICA AUSTRALIA PTY LTD",
        "bols_2014_2019": 11,
        "bols_2020_2025": 1,
        "bols_2014_2025": 12
    },
    {
        "name": "LULULEMON USA INCORPORATED",
        "bols_2014_2019": 8,
        "bols_2020_2025": 4,
        "bols_2014_2025": 12
    },
    {
        "name": "LULULEMON ATHLETICA INC",
        "bols_2014_2019": 7,
        "bols_2020_2025": 5,
        "bols_2014_2025": 12
    },
    {
        "name": "LULULEMON ATHLETICA CH GMBH",
        "bols_2014_2019": 5,
        "bols_2020_2025": 6,
        "bols_2014_2025": 11
    },
    {
        "name": "LULULEMON ATHLETICA TRADE SHANGH",
        "bols_2014_2019": 3,
        "bols_2020_2025": 5,
        "bols_2014_2025": 8
    }
]
check_1_names_pfizer Β· 20 rows 
SELECT consignee_name AS name,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_2014_2019,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_2020_2025,
  uniqExact(bill_of_lading) AS bols_2014_2025
FROM bols
WHERE actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
  AND (startsWith(upperUTF8(consignee_name), 'PFIZER'))
GROUP BY name
ORDER BY bols_2014_2025 DESC
LIMIT 20
[
    {
        "name": "PFIZER INC",
        "bols_2014_2019": 307,
        "bols_2020_2025": 279,
        "bols_2014_2025": 586
    },
    {
        "name": "PFIZER PHARMACEUTICALS LLC",
        "bols_2014_2019": 388,
        "bols_2020_2025": 189,
        "bols_2014_2025": 576
    },
    {
        "name": "PFIZER CONSUMER HEALTHCARE",
        "bols_2014_2019": 145,
        "bols_2020_2025": 19,
        "bols_2014_2025": 164
    },
    {
        "name": "PFIZER CANADA INC",
        "bols_2014_2019": 93,
        "bols_2020_2025": 24,
        "bols_2014_2025": 117
    },
    {
        "name": "PFIZER PHARMACEUTICAL LLC",
        "bols_2014_2019": 74,
        "bols_2020_2025": 38,
        "bols_2014_2025": 112
    },
    {
        "name": "PFIZER PHARMACEUTICALS LLC BA",
        "bols_2014_2019": 90,
        "bols_2020_2025": 13,
        "bols_2014_2025": 103
    },
    {
        "name": "PFIZER PHARMACEUTICALS LLC VB",
        "bols_2014_2019": 37,
        "bols_2020_2025": 60,
        "bols_2014_2025": 97
    },
    {
        "name": "PFIZER MEMPHIS DC",
        "bols_2014_2019": 79,
        "bols_2020_2025": 0,
        "bols_2014_2025": 79
    },
    {
        "name": "PFIZER SA DE CV",
        "bols_2014_2019": 9,
        "bols_2020_2025": 66,
        "bols_2014_2025": 75
    },
    {
        "name": "PFIZER PHARMCEUTICALS LLC",
        "bols_2014_2019": 43,
        "bols_2020_2025": 14,
        "bols_2014_2025": 57
    },
    {
        "name": "PFIZER S A DE C V",
        "bols_2014_2019": 44,
        "bols_2020_2025": 11,
        "bols_2014_2025": 55
    },
    {
        "name": "PFIZER PHARMACEUTICALS LLC TO VIATR",
        "bols_2014_2019": 0,
        "bols_2020_2025": 43,
        "bols_2014_2025": 43
    },
    {
        "name": "PFIZER PFE COLOMBIA SAS",
        "bols_2014_2019": 0,
        "bols_2020_2025": 36,
        "bols_2014_2025": 36
    },
    {
        "name": "PFIZER CORP",
        "bols_2014_2019": 23,
        "bols_2020_2025": 2,
        "bols_2014_2025": 25
    },
    {
        "name": "PFIZER IRELAND PHARMACEUTICALS",
        "bols_2014_2019": 6,
        "bols_2020_2025": 19,
        "bols_2014_2025": 25
    },
    {
        "name": "PFIZER BIOTECH CORP",
        "bols_2014_2019": 20,
        "bols_2020_2025": 5,
        "bols_2014_2025": 25
    },
    {
        "name": "PFIZER FREE ZONE PANAMA S DE RL C\/O J CAIN & CO INC",
        "bols_2014_2019": 24,
        "bols_2020_2025": 0,
        "bols_2014_2025": 24
    },
    {
        "name": "PFIZER FREE ZONE PANAMA S DE RL",
        "bols_2014_2019": 22,
        "bols_2020_2025": 2,
        "bols_2014_2025": 24
    },
    {
        "name": "PFIZER PHARMACUETICALS LLC",
        "bols_2014_2019": 17,
        "bols_2020_2025": 4,
        "bols_2014_2025": 21
    },
    {
        "name": "PFIZER PHARMACEUTICALS LLC C\/O",
        "bols_2014_2019": 20,
        "bols_2020_2025": 0,
        "bols_2014_2025": 20
    }
]
check_2_volume_walmart Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 120723,
        "bols_post": 182609,
        "suppliers_pre": 3403,
        "suppliers_post": 1427
    }
]
check_2b_hs_walmart Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "551342",
        "bols": 101044
    },
    {
        "hs_code": "200570",
        "bols": 31460
    },
    {
        "hs_code": "847439",
        "bols": 15606
    },
    {
        "hs_code": "130120",
        "bols": 15514
    },
    {
        "hs_code": "551332",
        "bols": 5443
    },
    {
        "hs_code": "950349",
        "bols": 4016
    },
    {
        "hs_code": "270730",
        "bols": 3695
    },
    {
        "hs_code": "551312",
        "bols": 3668
    }
]
check_3_yearly_walmart Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 6388,
        "suppliers": 118
    },
    {
        "year": 2015,
        "bols": 4929,
        "suppliers": 184
    },
    {
        "year": 2016,
        "bols": 1849,
        "suppliers": 206
    },
    {
        "year": 2017,
        "bols": 437,
        "suppliers": 42
    },
    {
        "year": 2018,
        "bols": 26115,
        "suppliers": 946
    },
    {
        "year": 2019,
        "bols": 81006,
        "suppliers": 2813
    },
    {
        "year": 2020,
        "bols": 24697,
        "suppliers": 525
    },
    {
        "year": 2021,
        "bols": 32546,
        "suppliers": 626
    },
    {
        "year": 2022,
        "bols": 29553,
        "suppliers": 579
    },
    {
        "year": 2023,
        "bols": 34568,
        "suppliers": 508
    },
    {
        "year": 2024,
        "bols": 32042,
        "suppliers": 430
    },
    {
        "year": 2025,
        "bols": 29203,
        "suppliers": 399
    }
]
check_4a_yoy_left_walmart Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 118,
        "stayed": 65,
        "left_count": 53
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 184,
        "stayed": 54,
        "left_count": 130
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 206,
        "stayed": 16,
        "left_count": 190
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 42,
        "stayed": 14,
        "left_count": 28
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 946,
        "stayed": 575,
        "left_count": 371
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 2813,
        "stayed": 336,
        "left_count": 2477
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 525,
        "stayed": 300,
        "left_count": 225
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 626,
        "stayed": 332,
        "left_count": 294
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 579,
        "stayed": 320,
        "left_count": 259
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 508,
        "stayed": 309,
        "left_count": 199
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 430,
        "stayed": 281,
        "left_count": 149
    }
]
check_4b_yoy_added_walmart Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 184,
        "added_count": 119
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 206,
        "added_count": 152
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 42,
        "added_count": 26
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 946,
        "added_count": 932
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 2813,
        "added_count": 2238
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 525,
        "added_count": 189
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 626,
        "added_count": 326
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 579,
        "added_count": 247
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 508,
        "added_count": 188
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 430,
        "added_count": 121
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 399,
        "added_count": 118
    }
]
check_5_sets_walmart Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('WALMART INC','WALMART STORES INC','WALMART GLOBAL LOGISTICS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 237,
        "pre_late_n": 3184,
        "pre_stayed": 104,
        "pre_left": 133,
        "pre_added": 3080,
        "post_early_n": 851,
        "post_late_n": 548,
        "post_stayed": 257,
        "post_left": 594,
        "post_added": 291
    }
]
check_2_volume_home_depot Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 124053,
        "bols_post": 107568,
        "suppliers_pre": 1877,
        "suppliers_post": 1684
    }
]
check_2b_hs_home_depot Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "940320",
        "bols": 10296
    },
    {
        "hs_code": "950510",
        "bols": 8518
    },
    {
        "hs_code": "841451",
        "bols": 7839
    },
    {
        "hs_code": "732111",
        "bols": 7534
    },
    {
        "hs_code": "391810",
        "bols": 6739
    },
    {
        "hs_code": "940510",
        "bols": 6723
    },
    {
        "hs_code": "950590",
        "bols": 5936
    },
    {
        "hs_code": "841810",
        "bols": 5520
    }
]
check_3_yearly_home_depot Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 32943,
        "suppliers": 838
    },
    {
        "year": 2015,
        "bols": 2819,
        "suppliers": 360
    },
    {
        "year": 2016,
        "bols": 1580,
        "suppliers": 221
    },
    {
        "year": 2017,
        "bols": 2653,
        "suppliers": 287
    },
    {
        "year": 2018,
        "bols": 48085,
        "suppliers": 800
    },
    {
        "year": 2019,
        "bols": 35978,
        "suppliers": 681
    },
    {
        "year": 2020,
        "bols": 1139,
        "suppliers": 33
    },
    {
        "year": 2021,
        "bols": 20337,
        "suppliers": 731
    },
    {
        "year": 2022,
        "bols": 3357,
        "suppliers": 158
    },
    {
        "year": 2023,
        "bols": 7289,
        "suppliers": 137
    },
    {
        "year": 2024,
        "bols": 8419,
        "suppliers": 75
    },
    {
        "year": 2025,
        "bols": 67027,
        "suppliers": 1078
    }
]
check_4a_yoy_left_home_depot Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 838,
        "stayed": 185,
        "left_count": 653
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 360,
        "stayed": 88,
        "left_count": 272
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 221,
        "stayed": 123,
        "left_count": 98
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 287,
        "stayed": 121,
        "left_count": 166
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 800,
        "stayed": 464,
        "left_count": 336
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 681,
        "stayed": 6,
        "left_count": 675
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 33,
        "stayed": 13,
        "left_count": 20
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 731,
        "stayed": 69,
        "left_count": 662
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 158,
        "stayed": 84,
        "left_count": 74
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 137,
        "stayed": 59,
        "left_count": 78
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 75,
        "stayed": 51,
        "left_count": 24
    }
]
check_4b_yoy_added_home_depot Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 360,
        "added_count": 175
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 221,
        "added_count": 133
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 287,
        "added_count": 164
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 800,
        "added_count": 679
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 681,
        "added_count": 217
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 33,
        "added_count": 27
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 731,
        "added_count": 718
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 158,
        "added_count": 89
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 137,
        "added_count": 53
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 75,
        "added_count": 16
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 1078,
        "added_count": 1027
    }
]
check_5_sets_home_depot Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('HOME DEPOT USA INC','HOME DEPOT USA','THE HOME DEPOT INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 1013,
        "pre_late_n": 1017,
        "pre_stayed": 309,
        "pre_left": 704,
        "pre_added": 708,
        "post_early_n": 751,
        "post_late_n": 1102,
        "post_stayed": 238,
        "post_left": 513,
        "post_added": 864
    }
]
check_2_volume_ikea Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 323961,
        "bols_post": 763728,
        "suppliers_pre": 2287,
        "suppliers_post": 3251
    }
]
check_2b_hs_ikea Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "630492",
        "bols": 371037
    },
    {
        "hs_code": "480269",
        "bols": 66973
    },
    {
        "hs_code": "930119",
        "bols": 63921
    },
    {
        "hs_code": "520527",
        "bols": 47771
    },
    {
        "hs_code": "701391",
        "bols": 44348
    },
    {
        "hs_code": "170290",
        "bols": 23059
    },
    {
        "hs_code": "180610",
        "bols": 16298
    },
    {
        "hs_code": "940161",
        "bols": 15885
    }
]
check_3_yearly_ikea Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 32756,
        "suppliers": 794
    },
    {
        "year": 2015,
        "bols": 38068,
        "suppliers": 822
    },
    {
        "year": 2016,
        "bols": 38123,
        "suppliers": 709
    },
    {
        "year": 2017,
        "bols": 40241,
        "suppliers": 758
    },
    {
        "year": 2018,
        "bols": 77059,
        "suppliers": 1123
    },
    {
        "year": 2019,
        "bols": 97902,
        "suppliers": 1242
    },
    {
        "year": 2020,
        "bols": 97466,
        "suppliers": 1259
    },
    {
        "year": 2021,
        "bols": 125189,
        "suppliers": 1400
    },
    {
        "year": 2022,
        "bols": 139556,
        "suppliers": 1701
    },
    {
        "year": 2023,
        "bols": 135096,
        "suppliers": 1491
    },
    {
        "year": 2024,
        "bols": 119611,
        "suppliers": 1449
    },
    {
        "year": 2025,
        "bols": 147464,
        "suppliers": 1256
    }
]
check_4a_yoy_left_ikea Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 794,
        "stayed": 517,
        "left_count": 277
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 822,
        "stayed": 496,
        "left_count": 326
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 709,
        "stayed": 469,
        "left_count": 240
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 758,
        "stayed": 570,
        "left_count": 188
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 1123,
        "stayed": 833,
        "left_count": 290
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 1242,
        "stayed": 899,
        "left_count": 343
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 1259,
        "stayed": 983,
        "left_count": 276
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 1400,
        "stayed": 1078,
        "left_count": 322
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 1701,
        "stayed": 1124,
        "left_count": 577
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 1491,
        "stayed": 1039,
        "left_count": 452
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 1449,
        "stayed": 884,
        "left_count": 565
    }
]
check_4b_yoy_added_ikea Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 822,
        "added_count": 305
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 709,
        "added_count": 213
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 758,
        "added_count": 289
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 1123,
        "added_count": 553
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 1242,
        "added_count": 409
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 1259,
        "added_count": 360
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 1400,
        "added_count": 417
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 1701,
        "added_count": 623
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 1491,
        "added_count": 367
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 1449,
        "added_count": 410
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 1256,
        "added_count": 372
    }
]
check_5_sets_ikea Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('IKEA SUPPLY AG','IKEA DISTRIBUTION SERVICES INC','IKEA DISTRIBUTION SERVICES INC L')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 1099,
        "pre_late_n": 1532,
        "pre_stayed": 529,
        "pre_left": 570,
        "pre_added": 1003,
        "post_early_n": 1676,
        "post_late_n": 1821,
        "post_stayed": 831,
        "post_left": 845,
        "post_added": 990
    }
]
check_2_volume_nike Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 1862,
        "bols_post": 129928,
        "suppliers_pre": 59,
        "suppliers_post": 753
    }
]
check_2b_hs_nike Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "640411",
        "bols": 23040
    },
    {
        "hs_code": "640399",
        "bols": 8843
    },
    {
        "hs_code": "640299",
        "bols": 5846
    },
    {
        "hs_code": "640391",
        "bols": 5702
    },
    {
        "hs_code": "640419",
        "bols": 5368
    },
    {
        "hs_code": "611030",
        "bols": 3618
    },
    {
        "hs_code": "640219",
        "bols": 3012
    },
    {
        "hs_code": "292143",
        "bols": 2862
    }
]
check_3_yearly_nike Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 1584,
        "suppliers": 21
    },
    {
        "year": 2015,
        "bols": 202,
        "suppliers": 17
    },
    {
        "year": 2016,
        "bols": 23,
        "suppliers": 14
    },
    {
        "year": 2017,
        "bols": 12,
        "suppliers": 7
    },
    {
        "year": 2018,
        "bols": 20,
        "suppliers": 7
    },
    {
        "year": 2019,
        "bols": 21,
        "suppliers": 4
    },
    {
        "year": 2020,
        "bols": 59,
        "suppliers": 13
    },
    {
        "year": 2021,
        "bols": 38747,
        "suppliers": 400
    },
    {
        "year": 2022,
        "bols": 90520,
        "suppliers": 606
    },
    {
        "year": 2023,
        "bols": 14,
        "suppliers": 5
    },
    {
        "year": 2024,
        "bols": 683,
        "suppliers": 22
    },
    {
        "year": 2025,
        "bols": 10,
        "suppliers": 4
    }
]
check_4a_yoy_left_nike Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 21,
        "stayed": 3,
        "left_count": 18
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 17,
        "stayed": 0,
        "left_count": 17
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 14,
        "stayed": 1,
        "left_count": 13
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 7,
        "stayed": 2,
        "left_count": 5
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 7,
        "stayed": 4,
        "left_count": 3
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 4,
        "stayed": 3,
        "left_count": 1
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 13,
        "stayed": 8,
        "left_count": 5
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 400,
        "stayed": 270,
        "left_count": 130
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 606,
        "stayed": 4,
        "left_count": 602
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 5,
        "stayed": 2,
        "left_count": 3
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 22,
        "stayed": 2,
        "left_count": 20
    }
]
check_4b_yoy_added_nike Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 17,
        "added_count": 14
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 14,
        "added_count": 14
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 7,
        "added_count": 6
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 7,
        "added_count": 5
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 4,
        "added_count": 0
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 13,
        "added_count": 10
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 400,
        "added_count": 392
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 606,
        "added_count": 336
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 5,
        "added_count": 1
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 22,
        "added_count": 20
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 4,
        "added_count": 2
    }
]
check_5_sets_nike Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('NIKE USA INC','NIKE INC','NIKE EUROPEAN OPERATIONS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 35,
        "pre_late_n": 7,
        "pre_stayed": 0,
        "pre_left": 35,
        "pre_added": 7,
        "post_early_n": 405,
        "post_late_n": 24,
        "post_stayed": 9,
        "post_left": 396,
        "post_added": 15
    }
]
check_2_volume_tesla Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 6774,
        "bols_post": 36111,
        "suppliers_pre": 327,
        "suppliers_post": 1287
    }
]
check_2b_hs_tesla Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "847439",
        "bols": 1444
    },
    {
        "hs_code": "870829",
        "bols": 1170
    },
    {
        "hs_code": "(blank)",
        "bols": 1069
    },
    {
        "hs_code": "850760",
        "bols": 1002
    },
    {
        "hs_code": "841520",
        "bols": 959
    },
    {
        "hs_code": "850650",
        "bols": 906
    },
    {
        "hs_code": "870899",
        "bols": 857
    },
    {
        "hs_code": "441299",
        "bols": 704
    }
]
check_3_yearly_tesla Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 2036,
        "suppliers": 122
    },
    {
        "year": 2015,
        "bols": 2499,
        "suppliers": 216
    },
    {
        "year": 2016,
        "bols": 1332,
        "suppliers": 129
    },
    {
        "year": 2017,
        "bols": 148,
        "suppliers": 22
    },
    {
        "year": 2018,
        "bols": 476,
        "suppliers": 25
    },
    {
        "year": 2019,
        "bols": 286,
        "suppliers": 6
    },
    {
        "year": 2020,
        "bols": 249,
        "suppliers": 30
    },
    {
        "year": 2021,
        "bols": 310,
        "suppliers": 30
    },
    {
        "year": 2022,
        "bols": 581,
        "suppliers": 53
    },
    {
        "year": 2023,
        "bols": 22122,
        "suppliers": 880
    },
    {
        "year": 2024,
        "bols": 1602,
        "suppliers": 41
    },
    {
        "year": 2025,
        "bols": 11260,
        "suppliers": 640
    }
]
check_4a_yoy_left_tesla Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 122,
        "stayed": 78,
        "left_count": 44
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 216,
        "stayed": 90,
        "left_count": 126
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 129,
        "stayed": 7,
        "left_count": 122
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 22,
        "stayed": 5,
        "left_count": 17
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 25,
        "stayed": 6,
        "left_count": 19
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 6,
        "stayed": 4,
        "left_count": 2
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 30,
        "stayed": 5,
        "left_count": 25
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 30,
        "stayed": 10,
        "left_count": 20
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 53,
        "stayed": 37,
        "left_count": 16
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 880,
        "stayed": 26,
        "left_count": 854
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 41,
        "stayed": 19,
        "left_count": 22
    }
]
check_4b_yoy_added_tesla Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 216,
        "added_count": 138
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 129,
        "added_count": 39
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 22,
        "added_count": 15
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 25,
        "added_count": 20
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 6,
        "added_count": 0
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 30,
        "added_count": 26
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 30,
        "added_count": 25
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 53,
        "added_count": 43
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 880,
        "added_count": 843
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 41,
        "added_count": 15
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 640,
        "added_count": 621
    }
]
check_5_sets_tesla Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('TESLA INC','TESLA MOTORS INC','TESLA MOTORS')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 260,
        "pre_late_n": 25,
        "pre_stayed": 4,
        "pre_left": 256,
        "pre_added": 21,
        "post_early_n": 55,
        "post_late_n": 662,
        "post_stayed": 13,
        "post_left": 42,
        "post_added": 649
    }
]
check_2_volume_apple Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 6160,
        "bols_post": 1083,
        "suppliers_pre": 87,
        "suppliers_post": 95
    }
]
check_2b_hs_apple Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "330720",
        "bols": 2037
    },
    {
        "hs_code": "850650",
        "bols": 621
    },
    {
        "hs_code": "850440",
        "bols": 372
    },
    {
        "hs_code": "950890",
        "bols": 367
    },
    {
        "hs_code": "851830",
        "bols": 354
    },
    {
        "hs_code": "851390",
        "bols": 282
    },
    {
        "hs_code": "854442",
        "bols": 250
    },
    {
        "hs_code": "852010",
        "bols": 242
    }
]
check_3_yearly_apple Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 978,
        "suppliers": 14
    },
    {
        "year": 2015,
        "bols": 827,
        "suppliers": 22
    },
    {
        "year": 2016,
        "bols": 1827,
        "suppliers": 41
    },
    {
        "year": 2017,
        "bols": 937,
        "suppliers": 26
    },
    {
        "year": 2018,
        "bols": 805,
        "suppliers": 37
    },
    {
        "year": 2019,
        "bols": 789,
        "suppliers": 17
    },
    {
        "year": 2020,
        "bols": 266,
        "suppliers": 32
    },
    {
        "year": 2021,
        "bols": 75,
        "suppliers": 17
    },
    {
        "year": 2022,
        "bols": 20,
        "suppliers": 12
    },
    {
        "year": 2023,
        "bols": 249,
        "suppliers": 24
    },
    {
        "year": 2024,
        "bols": 109,
        "suppliers": 24
    },
    {
        "year": 2025,
        "bols": 365,
        "suppliers": 25
    }
]
check_4a_yoy_left_apple Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 14,
        "stayed": 8,
        "left_count": 6
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 22,
        "stayed": 12,
        "left_count": 10
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 41,
        "stayed": 14,
        "left_count": 27
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 26,
        "stayed": 17,
        "left_count": 9
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 37,
        "stayed": 12,
        "left_count": 25
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 17,
        "stayed": 10,
        "left_count": 7
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 32,
        "stayed": 7,
        "left_count": 25
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 17,
        "stayed": 2,
        "left_count": 15
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 12,
        "stayed": 6,
        "left_count": 6
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 24,
        "stayed": 6,
        "left_count": 18
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 24,
        "stayed": 5,
        "left_count": 19
    }
]
check_4b_yoy_added_apple Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 22,
        "added_count": 14
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 41,
        "added_count": 29
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 26,
        "added_count": 12
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 37,
        "added_count": 20
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 17,
        "added_count": 5
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 32,
        "added_count": 22
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 17,
        "added_count": 10
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 12,
        "added_count": 10
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 24,
        "added_count": 18
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 24,
        "added_count": 18
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 25,
        "added_count": 20
    }
]
check_5_sets_apple Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('APPLE INC','APPLE COMPUTER INC')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 28,
        "pre_late_n": 42,
        "pre_stayed": 11,
        "pre_left": 17,
        "pre_added": 31,
        "post_early_n": 42,
        "post_late_n": 44,
        "post_stayed": 7,
        "post_left": 35,
        "post_added": 37
    }
]
check_2_volume_intel Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('INTEL CORP')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 2448,
        "bols_post": 3446,
        "suppliers_pre": 135,
        "suppliers_post": 176
    }
]
check_2b_hs_intel Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('INTEL CORP')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "846410",
        "bols": 719
    },
    {
        "hs_code": "903141",
        "bols": 586
    },
    {
        "hs_code": "851590",
        "bols": 270
    },
    {
        "hs_code": "848620",
        "bols": 243
    },
    {
        "hs_code": "842710",
        "bols": 233
    },
    {
        "hs_code": "850699",
        "bols": 221
    },
    {
        "hs_code": "903082",
        "bols": 192
    },
    {
        "hs_code": "950629",
        "bols": 179
    }
]
check_3_yearly_intel Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('INTEL CORP')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 493,
        "suppliers": 54
    },
    {
        "year": 2015,
        "bols": 284,
        "suppliers": 41
    },
    {
        "year": 2016,
        "bols": 25,
        "suppliers": 8
    },
    {
        "year": 2017,
        "bols": 141,
        "suppliers": 24
    },
    {
        "year": 2018,
        "bols": 520,
        "suppliers": 48
    },
    {
        "year": 2019,
        "bols": 986,
        "suppliers": 62
    },
    {
        "year": 2020,
        "bols": 623,
        "suppliers": 46
    },
    {
        "year": 2021,
        "bols": 194,
        "suppliers": 21
    },
    {
        "year": 2022,
        "bols": 93,
        "suppliers": 15
    },
    {
        "year": 2023,
        "bols": 730,
        "suppliers": 68
    },
    {
        "year": 2024,
        "bols": 1177,
        "suppliers": 86
    },
    {
        "year": 2025,
        "bols": 629,
        "suppliers": 69
    }
]
check_4a_yoy_left_intel Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('INTEL CORP')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 54,
        "stayed": 25,
        "left_count": 29
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 41,
        "stayed": 5,
        "left_count": 36
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 8,
        "stayed": 3,
        "left_count": 5
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 24,
        "stayed": 18,
        "left_count": 6
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 48,
        "stayed": 27,
        "left_count": 21
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 62,
        "stayed": 28,
        "left_count": 34
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 46,
        "stayed": 13,
        "left_count": 33
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 21,
        "stayed": 7,
        "left_count": 14
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 15,
        "stayed": 11,
        "left_count": 4
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 68,
        "stayed": 35,
        "left_count": 33
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 86,
        "stayed": 36,
        "left_count": 50
    }
]
check_4b_yoy_added_intel Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('INTEL CORP')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 41,
        "added_count": 16
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 8,
        "added_count": 3
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 24,
        "added_count": 21
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 48,
        "added_count": 30
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 62,
        "added_count": 35
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 46,
        "added_count": 18
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 21,
        "added_count": 8
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 15,
        "added_count": 8
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 68,
        "added_count": 57
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 86,
        "added_count": 51
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 69,
        "added_count": 33
    }
]
check_5_sets_intel Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('INTEL CORP')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 70,
        "pre_late_n": 83,
        "pre_stayed": 24,
        "pre_left": 46,
        "pre_added": 59,
        "post_early_n": 54,
        "post_late_n": 119,
        "post_stayed": 24,
        "post_left": 30,
        "post_added": 95
    }
]
check_2_volume_costco Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 146746,
        "bols_post": 21965,
        "suppliers_pre": 2322,
        "suppliers_post": 615
    }
]
check_2b_hs_costco Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "262060",
        "bols": 18553
    },
    {
        "hs_code": "940161",
        "bols": 15933
    },
    {
        "hs_code": "940140",
        "bols": 6759
    },
    {
        "hs_code": "940360",
        "bols": 6559
    },
    {
        "hs_code": "630140",
        "bols": 4001
    },
    {
        "hs_code": "170240",
        "bols": 3630
    },
    {
        "hs_code": "950300",
        "bols": 3584
    },
    {
        "hs_code": "842860",
        "bols": 3496
    }
]
check_3_yearly_costco Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 58458,
        "suppliers": 1179
    },
    {
        "year": 2015,
        "bols": 49894,
        "suppliers": 1115
    },
    {
        "year": 2016,
        "bols": 5879,
        "suppliers": 212
    },
    {
        "year": 2017,
        "bols": 6875,
        "suppliers": 220
    },
    {
        "year": 2018,
        "bols": 8684,
        "suppliers": 243
    },
    {
        "year": 2019,
        "bols": 16957,
        "suppliers": 548
    },
    {
        "year": 2020,
        "bols": 6863,
        "suppliers": 176
    },
    {
        "year": 2021,
        "bols": 6756,
        "suppliers": 231
    },
    {
        "year": 2022,
        "bols": 977,
        "suppliers": 110
    },
    {
        "year": 2023,
        "bols": 1052,
        "suppliers": 152
    },
    {
        "year": 2024,
        "bols": 3961,
        "suppliers": 154
    },
    {
        "year": 2025,
        "bols": 2373,
        "suppliers": 98
    }
]
check_4a_yoy_left_costco Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 1179,
        "stayed": 596,
        "left_count": 583
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 1115,
        "stayed": 123,
        "left_count": 992
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 212,
        "stayed": 110,
        "left_count": 102
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 220,
        "stayed": 103,
        "left_count": 117
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 243,
        "stayed": 94,
        "left_count": 149
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 548,
        "stayed": 85,
        "left_count": 463
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 176,
        "stayed": 67,
        "left_count": 109
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 231,
        "stayed": 42,
        "left_count": 189
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 110,
        "stayed": 32,
        "left_count": 78
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 152,
        "stayed": 41,
        "left_count": 111
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 154,
        "stayed": 50,
        "left_count": 104
    }
]
check_4b_yoy_added_costco Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 1115,
        "added_count": 519
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 212,
        "added_count": 89
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 220,
        "added_count": 110
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 243,
        "added_count": 140
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 548,
        "added_count": 454
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 176,
        "added_count": 91
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 231,
        "added_count": 164
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 110,
        "added_count": 68
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 152,
        "added_count": 120
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 154,
        "added_count": 113
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 98,
        "added_count": 48
    }
]
check_5_sets_costco Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('COSTCO WHOLESALE CORP','COSTCO WHOLESALE','COSTCO WHOLESALE COPRORATION')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 1698,
        "pre_late_n": 697,
        "pre_stayed": 185,
        "pre_left": 1513,
        "pre_added": 512,
        "post_early_n": 340,
        "post_late_n": 202,
        "post_stayed": 51,
        "post_left": 289,
        "post_added": 151
    }
]
check_2_volume_toyota Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 15593,
        "bols_post": 14553,
        "suppliers_pre": 59,
        "suppliers_post": 51
    }
]
check_2b_hs_toyota Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, 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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "401019",
        "bols": 5243
    },
    {
        "hs_code": "900130",
        "bols": 3744
    },
    {
        "hs_code": "441600",
        "bols": 2168
    },
    {
        "hs_code": "391910",
        "bols": 1606
    },
    {
        "hs_code": "981800",
        "bols": 1452
    },
    {
        "hs_code": "392310",
        "bols": 1078
    },
    {
        "hs_code": "841830",
        "bols": 939
    },
    {
        "hs_code": "271019",
        "bols": 797
    }
]
check_3_yearly_toyota Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 2594,
        "suppliers": 19
    },
    {
        "year": 2015,
        "bols": 2485,
        "suppliers": 24
    },
    {
        "year": 2016,
        "bols": 2770,
        "suppliers": 19
    },
    {
        "year": 2017,
        "bols": 2603,
        "suppliers": 25
    },
    {
        "year": 2018,
        "bols": 2690,
        "suppliers": 20
    },
    {
        "year": 2019,
        "bols": 2451,
        "suppliers": 22
    },
    {
        "year": 2020,
        "bols": 2098,
        "suppliers": 25
    },
    {
        "year": 2021,
        "bols": 2124,
        "suppliers": 18
    },
    {
        "year": 2022,
        "bols": 2205,
        "suppliers": 16
    },
    {
        "year": 2023,
        "bols": 2648,
        "suppliers": 21
    },
    {
        "year": 2024,
        "bols": 2348,
        "suppliers": 16
    },
    {
        "year": 2025,
        "bols": 3137,
        "suppliers": 22
    }
]
check_4a_yoy_left_toyota Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 19,
        "stayed": 12,
        "left_count": 7
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 24,
        "stayed": 14,
        "left_count": 10
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 19,
        "stayed": 12,
        "left_count": 7
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 25,
        "stayed": 11,
        "left_count": 14
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 20,
        "stayed": 12,
        "left_count": 8
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 22,
        "stayed": 17,
        "left_count": 5
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 25,
        "stayed": 12,
        "left_count": 13
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 18,
        "stayed": 11,
        "left_count": 7
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 16,
        "stayed": 11,
        "left_count": 5
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 21,
        "stayed": 11,
        "left_count": 10
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 16,
        "stayed": 13,
        "left_count": 3
    }
]
check_4b_yoy_added_toyota Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 24,
        "added_count": 12
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 19,
        "added_count": 5
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 25,
        "added_count": 13
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 20,
        "added_count": 9
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 22,
        "added_count": 10
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 25,
        "added_count": 8
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 18,
        "added_count": 6
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 16,
        "added_count": 5
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 21,
        "added_count": 10
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 16,
        "added_count": 5
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 22,
        "added_count": 9
    }
]
check_5_sets_toyota Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  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 KENTUCKY','TOYOTA MOTOR MANUFACTURING MISSISSIPPI INC','TOYOTA MOTOR MANUFACTURING INDIANA','TOYOTA MOTOR MANUFACTURING WEST VI','TOYOTA MOTOR CORP','TOYOTA MOTOR MANUFACTURING ALABAMA INC','TOYOTA MOTOR MANUFACTURING MISSISSI','TOYOTA MOTOR MANUFACTURING ALABAMA','TOYOTA MOTOR MANUFACTURING TEXAS IN')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 31,
        "pre_late_n": 30,
        "pre_stayed": 14,
        "pre_left": 17,
        "pre_added": 16,
        "post_early_n": 31,
        "post_late_n": 25,
        "post_stayed": 14,
        "post_left": 17,
        "post_added": 11
    }
]
check_2_volume_mattel Β· 1 rows 
SELECT
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-12-31')) AS bols_pre,
  uniqExactIf(bill_of_lading, actual_arrival_date >= toDate('2020-01-01') AND actual_arrival_date <= toDate('2025-12-31')) AS bols_post,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2019-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.')) AS suppliers_pre,
  uniqExactIf(upperUTF8(trimBoth(shipper_name)), actual_arrival_date >= toDate('2020-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.')) AS suppliers_post
FROM bols
WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
[
    {
        "bols_pre": 42531,
        "bols_post": 32910,
        "suppliers_pre": 477,
        "suppliers_post": 352
    }
]
check_2b_hs_mattel Β· 8 rows 
SELECT if(hs_code = '', '(blank)', hs_code) AS hs_code, uniqExact(bill_of_lading) AS bols
FROM bols
WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY hs_code ORDER BY bols DESC LIMIT 8
[
    {
        "hs_code": "950300",
        "bols": 37056
    },
    {
        "hs_code": "950349",
        "bols": 11265
    },
    {
        "hs_code": "950210",
        "bols": 6942
    },
    {
        "hs_code": "845430",
        "bols": 4939
    },
    {
        "hs_code": "940171",
        "bols": 2679
    },
    {
        "hs_code": "871500",
        "bols": 1269
    },
    {
        "hs_code": "950100",
        "bols": 888
    },
    {
        "hs_code": "950291",
        "bols": 546
    }
]
check_3_yearly_mattel Β· 12 rows 
SELECT toYear(actual_arrival_date) AS year,
  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 suppliers
FROM bols
WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
  AND actual_arrival_date >= toDate('2014-01-01') AND actual_arrival_date <= toDate('2025-12-31')
GROUP BY year ORDER BY year
[
    {
        "year": 2014,
        "bols": 7090,
        "suppliers": 129
    },
    {
        "year": 2015,
        "bols": 7961,
        "suppliers": 203
    },
    {
        "year": 2016,
        "bols": 7368,
        "suppliers": 170
    },
    {
        "year": 2017,
        "bols": 8002,
        "suppliers": 204
    },
    {
        "year": 2018,
        "bols": 6242,
        "suppliers": 161
    },
    {
        "year": 2019,
        "bols": 5868,
        "suppliers": 107
    },
    {
        "year": 2020,
        "bols": 6431,
        "suppliers": 121
    },
    {
        "year": 2021,
        "bols": 5791,
        "suppliers": 150
    },
    {
        "year": 2022,
        "bols": 7303,
        "suppliers": 168
    },
    {
        "year": 2023,
        "bols": 6333,
        "suppliers": 151
    },
    {
        "year": 2024,
        "bols": 5807,
        "suppliers": 130
    },
    {
        "year": 2025,
        "bols": 1265,
        "suppliers": 66
    }
]
check_4a_yoy_left_mattel Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT p.y AS year_from, p.y + 1 AS year_to,
  uniqExact(p.shipper) AS suppliers_from,
  uniqExactIf(p.shipper, c.shipper != '') AS stayed,
  uniqExact(p.shipper) - uniqExactIf(p.shipper, c.shipper != '') AS left_count
FROM base AS p
LEFT JOIN base AS c ON p.shipper = c.shipper AND c.y = p.y + 1
WHERE p.y >= 2014 AND p.y <= 2024
GROUP BY p.y ORDER BY p.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_from": 129,
        "stayed": 91,
        "left_count": 38
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_from": 203,
        "stayed": 93,
        "left_count": 110
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_from": 170,
        "stayed": 99,
        "left_count": 71
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_from": 204,
        "stayed": 100,
        "left_count": 104
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_from": 161,
        "stayed": 80,
        "left_count": 81
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_from": 107,
        "stayed": 62,
        "left_count": 45
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_from": 121,
        "stayed": 74,
        "left_count": 47
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_from": 150,
        "stayed": 87,
        "left_count": 63
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_from": 168,
        "stayed": 103,
        "left_count": 65
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_from": 151,
        "stayed": 90,
        "left_count": 61
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_from": 130,
        "stayed": 59,
        "left_count": 71
    }
]
check_4b_yoy_added_mattel Β· 11 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
)
SELECT c.y - 1 AS year_from, c.y AS year_to,
  uniqExact(c.shipper) AS suppliers_to,
  uniqExactIf(c.shipper, p.shipper = '') AS added_count
FROM base AS c
LEFT JOIN base AS p ON c.shipper = p.shipper AND p.y = c.y - 1
WHERE c.y >= 2015 AND c.y <= 2025
GROUP BY c.y ORDER BY c.y
[
    {
        "year_from": 2014,
        "year_to": 2015,
        "suppliers_to": 203,
        "added_count": 112
    },
    {
        "year_from": 2015,
        "year_to": 2016,
        "suppliers_to": 170,
        "added_count": 77
    },
    {
        "year_from": 2016,
        "year_to": 2017,
        "suppliers_to": 204,
        "added_count": 105
    },
    {
        "year_from": 2017,
        "year_to": 2018,
        "suppliers_to": 161,
        "added_count": 61
    },
    {
        "year_from": 2018,
        "year_to": 2019,
        "suppliers_to": 107,
        "added_count": 27
    },
    {
        "year_from": 2019,
        "year_to": 2020,
        "suppliers_to": 121,
        "added_count": 59
    },
    {
        "year_from": 2020,
        "year_to": 2021,
        "suppliers_to": 150,
        "added_count": 76
    },
    {
        "year_from": 2021,
        "year_to": 2022,
        "suppliers_to": 168,
        "added_count": 81
    },
    {
        "year_from": 2022,
        "year_to": 2023,
        "suppliers_to": 151,
        "added_count": 48
    },
    {
        "year_from": 2023,
        "year_to": 2024,
        "suppliers_to": 130,
        "added_count": 40
    },
    {
        "year_from": 2024,
        "year_to": 2025,
        "suppliers_to": 66,
        "added_count": 7
    }
]
check_5_sets_mattel Β· 1 rows 
WITH base AS (
  SELECT toYear(actual_arrival_date) AS y, upperUTF8(trimBoth(shipper_name)) AS shipper
  FROM bols
  WHERE consignee_name IN ('MATTEL INC','MATTEL IMPORT SERVICES CORP','MATTEL IMPORT SERVICES LLC','MATTEL INCORPORATED')
    AND actual_arrival_date >= toDate('2014-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.')
  GROUP BY y, shipper
),
pre_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2014, 2015)),
pre_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2018, 2019)),
post_early AS (SELECT DISTINCT shipper FROM base WHERE y IN (2020, 2021)),
post_late  AS (SELECT DISTINCT shipper FROM base WHERE y IN (2024, 2025))
SELECT
  (SELECT count() FROM pre_early) AS pre_early_n,
  (SELECT count() FROM pre_late) AS pre_late_n,
  (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_stayed,
  (SELECT count() FROM pre_early) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_left,
  (SELECT count() FROM pre_late) - (SELECT count() FROM pre_early INNER JOIN pre_late USING shipper) AS pre_added,
  (SELECT count() FROM post_early) AS post_early_n,
  (SELECT count() FROM post_late) AS post_late_n,
  (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_stayed,
  (SELECT count() FROM post_early) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_left,
  (SELECT count() FROM post_late) - (SELECT count() FROM post_early INNER JOIN post_late USING shipper) AS post_added
[
    {
        "pre_early_n": 241,
        "pre_late_n": 188,
        "pre_stayed": 67,
        "pre_left": 174,
        "pre_added": 121,
        "post_early_n": 197,
        "post_late_n": 137,
        "post_stayed": 65,
        "post_left": 132,
        "post_added": 72
    }
]