# How to Filter Bambu Lab Stock Data by Region (US, EU, UK, AU, CA) in SQL Queries

> Learn to filter Bambu Lab stock data by region US EU UK AU CA in SQL queries using WHERE clauses on the region column in DuckDB. Access public Parquet data easily.

- Repository: [Nelson Chen/bbl-tracker-public-db](https://github.com/nelsonjchen/bbl-tracker-public-db)
- Tags: how-to-guide
- Published: 2026-03-08

---

**You can filter Bambu Lab store inventory by region using standard SQL `WHERE` clauses on the `region` column in DuckDB queries against the public Parquet dataset hosted at `db-public.bbltracker.com`.**

The `nelsonjchen/bbl-tracker-public-db` repository provides a streaming dataset of hourly Parquet files tracking Bambu Lab store filament availability across global markets. Each record includes a **`region`** column containing lowercase string codes that identify specific markets, allowing you to isolate inventory data for the United States, European Union, United Kingdom, Australia, or Canada using straightforward SQL predicates.

## Understanding the Dataset Schema

According to the repository's [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md), the dataset schema defines the `region` column as a `STRING` type storing region codes. This column is present in every hourly Parquet file available in the public database.

The repository organizes data through three core components:

- **[`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py)** – Handles manifest discovery by downloading [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) to identify available Parquet files, then demonstrates region filtering using `read_parquet`.
- **[`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py)** – Utility script for merging multiple Parquet files into a single DuckDB database file, useful for analyzing regional trends across extended time periods.
- **[`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md)** – Documents the complete schema including the `region` column specification.

## Single-Region Filtering

To isolate data for a specific market, apply an equality predicate against the `region` column. This approach works for any of the five supported regions: `us`, `eu`, `uk`, `au`, or `ca`.

```sql
SELECT
    timestamp,
    product_name,
    variant_name,
    stock,
    max_quantity
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE region = 'us'
ORDER BY timestamp DESC
LIMIT 20;

```

*The `WHERE region = 'us'` clause restricts results to United States inventory snapshots.*

## Multi-Region Filtering with IN Clauses

When analyzing inventory across multiple territories simultaneously, use the `IN` operator to specify a list of region codes. This eliminates the need for multiple `OR` conditions and produces cleaner, more maintainable queries.

```sql
SELECT
    region,
    product_name,
    COUNT(*) AS snapshots,
    SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock,
    ROUND(100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) / COUNT(*), 1) AS availability_pct
FROM read_parquet([
    'https://db-public.bbltracker.com/2026-02-15-1800.parquet',
    'https://db-public.bbltracker.com/2026-02-16-0000.parquet'
])
WHERE region IN ('us', 'eu')
GROUP BY region, product_name
ORDER BY availability_pct ASC
LIMIT 15;

```

*This example calculates stock availability percentages across both US and EU markets.*

## Querying All Five Regions

For comprehensive regional analysis covering all available markets (US, EU, UK, AU, and CA), expand the `IN` clause to include all five region codes:

```sql
SELECT
    region,
    COUNT(DISTINCT product_name) AS distinct_products,
    AVG(stock) AS avg_stock
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0600.parquet')
WHERE region IN ('us', 'eu', 'uk', 'au', 'ca')
GROUP BY region;

```

*This aggregation provides a per-region inventory health check, showing distinct product counts and average stock levels.*

## Dynamic Region Filtering in Python

The [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) file demonstrates how to programmatically construct region filters. Below is an adapted version that accepts dynamic region lists:

```python
import duckdb

def query_region(parquet_urls, regions):
    # Build the IN-list for SQL

    region_list = ",".join(f"'{r}'" for r in regions)
    sql = f"""
        SELECT *
        FROM read_parquet({parquet_urls})
        WHERE region IN ({region_list})
    """
    return duckdb.query(sql).df()

# Example usage:

urls = [
    "https://db-public.bbltracker.com/2026-02-16-0000.parquet",
    "https://db-public.bbltracker.com/2026-02-16-0600.parquet"
]
df = query_region(urls, ["us", "eu", "uk"])
print(df.head())

```

*This function safely parameterizes region selection by constructing the SQL `IN` clause from a Python list.*

## Summary

- The **`region`** column in `nelsonjchen/bbl-tracker-public-db` contains lowercase string codes (`us`, `eu`, `uk`, `au`, `ca`) enabling direct SQL filtering.
- Use **`WHERE region = 'us'`** for single-market analysis or **`WHERE region IN ('us', 'eu', ...)`** for multi-region queries.
- DuckDB's **`read_parquet`** function can query remote URLs directly without downloading files locally.
- Reference **[`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py)** for implementation patterns including manifest discovery and dynamic SQL generation.
- For large-scale historical analysis, use **[`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py)** to merge multiple Parquet files into a single DuckDB database before applying regional filters.

## Frequently Asked Questions

### What region codes does the Bambu Lab tracker dataset support?

The dataset supports five region codes as strings: `us` (United States), `eu` (European Union), `uk` (United Kingdom), `au` (Australia), and `ca` (Canada). These values are stored in the `region` column of every Parquet file, as documented in the repository's [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md) schema table.

### Can I filter by multiple regions in a single SQL query?

Yes. Use the `IN` operator with a list of region codes: `WHERE region IN ('us', 'eu', 'uk')`. This approach is more efficient than chaining multiple `OR` conditions and works seamlessly with DuckDB's `read_parquet` function when querying both local and remote Parquet files.

### How do I query regional data without downloading the entire dataset?

DuckDB can query Parquet files directly over HTTP using `read_parquet('https://db-public.bbltracker.com/...')`. As implemented in [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py), you can apply `WHERE region = 'us'` predicates to these remote reads, ensuring only filtered results are transferred to your local environment rather than the complete dataset.

### Is the region filter case-sensitive?

Yes, the region filter is case-sensitive because the `region` column contains lowercase strings (`us`, `eu`, etc.). Queries using uppercase values like `WHERE region = 'US'` will return zero results. Always use lowercase region codes when writing filters against this dataset.