U.S.-Mexico Border Truck Crossings at Laredo, TX

I wanted to explore how truck crossing and trade volumes vary over time at the Laredo, Texas port of entry, which serves as one of the busiest inland trade hubs between the United States and Mexico.

This analysis details my first data exploration project, documenting my query progression, data verification steps, and seasonal peak discoveries.

---

1. Data Structuring and Query Formulation

I started by looking at the data available in the SQLite database. The first table, entries, is comprised of raw records from the Border Crossing Entry Data.

The raw date column in the data source had dates formatted like 2025 Dec 01 12:00:00 AM. I needed to extract the year and month substrings and format the numeric crossings. The crossing count was originally stored in text format with commas (e.g., 2,459), so I stripped the commas and cast the numbers to integers.

The following query filters the data specifically for inbound truck crossings at the Port of Laredo in Texas:

SELECT
	SUBSTR("Date", 1, 4) AS year,
	SUBSTR("Date", 5, 5) AS month,
	REPLACE(Crossings, ',', '') AS cross,
	CAST(REPLACE(Crossings, ',', '') AS INTEGER) AS num_crossings
FROM
	entries
WHERE
	State = "Texas"
	AND "Port Name" = "Laredo"
	AND Measure = "Trucks"
ORDER BY num_crossings DESC
;

---

2. Verifying Data Integrity

I had access to a separate summary table named crossings that contained the total annual crossings by crossing type. However, that summary table only went up to 2024:

Description	2022	2023	2024
Pedestrians	2,490,687	2,550,209	2,954,326
Personal Vehicles	4,552,930	4,908,368	5,088,431
Trucks	2,799,601	2,936,130	3,026,632
Vehicle Passengers	8,778,931	9,512,794	9,912,909

To ensure that my queries on the raw entries table were accurate and that the source data was reliable, I wrote a query utilizing a Common Table Expression (CTE) to sum the total crossings in entries per year:

WITH crossings_data AS (
SELECT
	SUBSTR("Date", 1, 4) AS year,
	SUBSTR("Date", 5, 5) AS month,
	REPLACE(Crossings, ',', '') AS cross,
	CAST(REPLACE(Crossings, ',', '') AS INTEGER) AS num_crossings
FROM
	entries
WHERE
	State = "Texas"
	AND "Port Name" = "Laredo"
	AND Measure = "Trucks"
)

SELECT
	year,
	SUM(num_crossings) as total_crossings
FROM
	crossings_data
GROUP BY year
;

This query returned the following annual totals:

Year	Total Crossings (Raw Sum)	Summary Total (Expected)	Verification
2022	2,799,601	2,799,601	Matches ✓
2023	2,936,130	2,936,130	Matches ✓
2024	3,026,632	3,026,632	Matches ✓

Seeing that the sums matched the official totals exactly gave me confidence that the raw source data was sound and that my query logic was correct.

---

3. Annual Trends and Time Series

Next, I wanted to map the monthly crossings over time to see the progression. I started by grouping the data by year:

SELECT
	CAST(SUBSTR("Date", 1, 4) as INTEGER) AS year,
	SUBSTR("Date", 5, 5) AS month,
	CAST(REPLACE(Crossings, ',', '') AS INTEGER) AS num_crossings
FROM
	entries
WHERE
	State = "Texas"
	AND "Port Name" = "Laredo"
	AND Measure = "Trucks"
	AND year BETWEEN 2022 AND 2026
GROUP BY year, month
;

I cast the year substring to an integer (CAST(SUBSTR("Date", 1, 4) as INTEGER)) so I could write a clean range limit (year BETWEEN 2022 AND 2026) in the WHERE clause instead of chaining multiple OR statements.

A query summing the totals by year shows the overall annual growth:

SELECT
	SUBSTR("Date", 1, 4) AS year_str,
	SUM(CAST(REPLACE(Crossings, ',', '') AS INTEGER)) AS num_crossings
FROM entries
WHERE
	State = 'Texas'
	AND "Port Name" = 'Laredo'
	AND Measure = 'Trucks'
GROUP BY year_str
ORDER BY year_str DESC;

---

4. Sorting and Identifying Monthly Peaks

When reviewing the raw results, I noticed they were ordered alphabetically by month name abbreviation (Apr, Aug, Dec, etc.) instead of chronologically. To sort them chronologically, I used a CASE statement inside a CTE to convert the month strings to two-digit numeric strings:

WITH staged_entries AS (
	SELECT 
		SUBSTR("Date", 1, 4) AS year_str,
		CASE TRIM(SUBSTR("Date", 5, 4))
			WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03'
			WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
			WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09'
			WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12'
		END	AS month_str,
		CAST(REPLACE(Crossings, ',', '') AS INTEGER) AS num_crossings
	FROM 
		entries
	WHERE
		State = 'Texas'
		AND "Port Name" = 'Laredo'
		AND Measure = 'Trucks'
)

With this structured timeline, I was able to run a query to check which year performed best for each month:

SELECT
	month_str,
	year_str,
	MAX(num_crossings) AS peak_volume
FROM staged_entries
GROUP BY month_str
;

This returned:

Month	Year	Peak Crossings
01 (Jan)	2024	244,595
02 (Feb)	2024	251,110
03 (Mar)	2026	266,808
04 (Apr)	2024	267,884
05 (May)	2024	261,745
06 (Jun)	2026	261,224
07 (Jul)	2024	260,600
08 (Aug)	2024	263,414
09 (Sep)	2023	244,489
10 (Oct)	2024	267,428
11 (Nov)	2023	245,343
12 (Dec)	2023	223,977

This shows that 2024 holds the record for peak volume for most months, except for September, November, and December (held by 2023), and March and June (surpassed in 2026).

---

5. Busiest Months (Seasonality Patterns)

Finally, I wanted to find the single busiest month in each calendar year. To do this, I constructed ISO dates and used the ROW_NUMBER() window function partitioned by year to rank the monthly volumes:

WITH staged_entries AS (
	SELECT 
		SUBSTR("Date", 1, 4) AS year_str,
		CASE TRIM(SUBSTR("Date", 5, 4))
			WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03'
			WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06'
			WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09'
			WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12'
		END	AS month_str,
		CAST(REPLACE(Crossings, ',', '') AS INTEGER) AS num_crossings
	FROM 
		entries
	WHERE
		State = 'Texas'
		AND "Port Name" = 'Laredo'
		AND Measure = 'Trucks'
),
cleaned_crossings AS (
	SELECT
		year_str,
		month_str,
		year_str || '-' || month_str || '-01' AS month_date,
		CAST(year_str AS INTEGER) AS year_num,
		num_crossings
	FROM staged_entries	
),
ranked_months AS (
	SELECT
		year_str,
		month_str,
		month_date,
		num_crossings,
		ROW_NUMBER() OVER(
			PARTITION BY year_str
			ORDER BY num_crossings DESC
		) AS rank_in_year
	FROM cleaned_crossings
)

SELECT
	year_str AS year,
	month_str AS month,
	num_crossings AS peak_crossings
FROM ranked_months
WHERE rank_in_year = 1
  AND year_str BETWEEN '2022' AND '2026'
ORDER BY year_str ASC
;

This produced these results:

Year	Peak Month	Peak Crossings
2022	March (03)	251,385
2023	March (03)	263,896
2024	April (04)	267,884
2025	March (03)	265,620
2026	March (03)	266,808

This query confirmed that March is historically the busiest month for inbound truck traffic, except for 2024 which peaked in April.

When I removed the date filters and queried the historical dataset going back to 1996, the pattern expanded:

• October and March consistently rank as the top months, with October holding the absolute majority of yearly peak volumes.
• This suggests that the end of the first quarter (Q1 spring run) and the start of the final quarter (Q4 pre-holiday stocking run) represent the busiest logistics seasons.

Note: The dataset used measures inbound crossings only (traffic flowing from Mexico into the United States).