# Identifying Product Availability Bottlenecks Using SQL Aggregation: A DuckDB Approach

> Identify product availability bottlenecks using SQL aggregation in DuckDB. Analyze hourly stock data to find SKUs with low stock ratios and improve order fulfillment.

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

---

**Calculate availability percentages by aggregating hourly stock snapshots in DuckDB to surface SKUs with the lowest in-stock ratios, revealing which products act as bottlenecks for multi-item orders.**

The **Bambu Lab Store Filament Tracker** (`nelsonjchen/bbl-tracker-public-db`) publishes a public stream of hourly inventory snapshots as Parquet files. By treating this time-series data as a single analytical table in DuckDB, you can identify product availability bottlenecks using SQL aggregation. This article demonstrates how to calculate stock availability percentages, isolate scarce SKUs, and build persistent databases for deeper analysis.

## Understanding the Data Architecture

The repository stores stock history in a public Cloudflare R2 bucket at `https://db-public.bbltracker.com`. Each hour, a new Parquet shard is appended with a deterministic filename pattern: `YYYY-MM-DD-HH00.parquet`.

The [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) file indexes all available shards, enabling scripts to discover valid date ranges without guessing URLs. According to the repository's [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md), each Parquet row contains:

- `timestamp` – The snapshot capture time
- `product_name` – Base product (e.g., "PLA Matte")
- `variant_name` – Color or material variant (e.g., "White")
- `stock` – Current inventory count
- `max_quantity` – Purchase limit (cap) for that SKU
- `region` – Geographic market (e.g., "us", "eu")

## The SQL Aggregation Strategy for Bottleneck Detection

Identifying bottlenecks requires calculating how often each SKU is actually available for purchase. The core metric is **availability percentage**: the ratio of snapshots showing `stock > 0` divided by total snapshots for that SKU.

The aggregation logic follows three steps:

1. **Normalize identifiers**: Concatenate `product_name` and `variant_name` into a `full_name` to uniquely identify each SKU.
2. **Compute availability metrics**: Group by `full_name` and calculate:
   - `total_snapshots`: Count of all observations
   - `in_stock_snapshots`: Count where `stock > 0`
   - `availability_pct`: `(in_stock_snapshots / total_snapshots) * 100`
   - `max_stock_seen`: Peak inventory level observed
   - `cap_seen`: Purchase limit for context
3. **Filter and sort**: Exclude SKUs with zero availability (discontinued items) and order by `availability_pct` ascending. The lowest percentages represent your bottlenecks—items that are frequently out of stock and likely to block multi-SKU orders.

## Implementing the Query in DuckDB

The repository's [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) demonstrates an end-to-end implementation. It fetches the latest four Parquet files (representing 24 hours of data), runs the aggregation CTEs, and prints the 15 most constrained SKUs.

```python
import duckdb

# Build URLs for the last 24h (4 snapshots at 6-hour intervals)

base = "https://db-public.bbltracker.com"
recent = [
    f"{base}/2026-02-16-0000.parquet",
    f"{base}/2026-02-16-0600.parquet",
    f"{base}/2026-02-16-1200.parquet",
    f"{base}/2026-02-16-1800.parquet",
]

query = f"""
WITH subset AS (
    SELECT
        timestamp,
        product_name || ' - ' || variant_name AS full_name,
        stock,
        max_quantity
    FROM read_parquet({recent})
    WHERE region = 'us'
),
stats AS (
    SELECT
        full_name,
        COUNT(*) AS total_snapshots,
        SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS in_stock_snapshots,
        MAX(stock)        AS max_stock_seen,
        MAX(max_quantity) AS cap_seen
    FROM subset
    GROUP BY full_name
)
SELECT
    full_name,
    ROUND(in_stock_snapshots::FLOAT / total_snapshots * 100, 1) AS availability_pct,
    max_stock_seen,
    cap_seen
FROM stats
WHERE availability_pct > 0
ORDER BY availability_pct ASC
LIMIT 15;
"""

con = duckdb.connect()
df = con.execute(query).df()
print(df)

```

DuckDB's `read_parquet` function accepts a list of URLs, allowing the query to run entirely in-memory without downloading files to disk. The `subset` CTE filters to a specific region and normalizes names, while the `stats` CTE performs the bottleneck aggregation.

## Building a Persistent Database for Deeper Analysis

For analyses spanning weeks or months, repeatedly fetching remote Parquet files becomes inefficient. The [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script solves this by merging historical shards into a single local DuckDB database.

```python
import duckdb, urllib.request, json, os
from datetime import datetime, timedelta, timezone

BASE = "https://db-public.bbltracker.com"
MANIFEST = f"{BASE}/manifest.json"
DB_FILE = "bambu_stock.duckdb"

# Fetch manifest and filter to last 30 days

manifest = json.loads(urllib.request.urlopen(MANIFEST).read())
all_files = sorted(manifest["files"].keys())
cutoff = (datetime.now(timezone.utc) - timedelta(days=30)).strftime("%Y-%m-%d")
recent = [f for f in all_files if f >= cutoff]
urls = [f"{BASE}/{f}" for f in recent]

# Build persistent database

if os.path.exists(DB_FILE):
    os.remove(DB_FILE)

con = duckdb.connect(DB_FILE)
con.execute(f"""
    CREATE TABLE stock_history AS
    SELECT * FROM read_parquet({urls});
""")
print("Rows imported:", con.execute("SELECT count(*) FROM stock_history").fetchone()[0])
con.close()

```

The resulting `bambu_stock.duckdb` file can be uploaded to AI code interpreters (ChatGPT, Claude) or queried locally with complex window functions, joins, and time-series aggregations that go beyond the simple bottleneck detection query.

## Querying Specific Time Windows

For ad-hoc investigations of specific restock events or shortages, you can query individual Parquet files directly without Python overhead:

```sql
SELECT *
FROM read_parquet('https://db-public.bbltracker.com/2026-02-16-0000.parquet')
WHERE product_name = 'PLA Matte' AND variant_name = 'White';

```

Replace the URL with any `YYYY-MM-DD-HH00.parquet` filename to inspect inventory at a specific hour. This is useful for correlating stock levels with external events like sales announcements or supply chain disruptions.

## Summary

- **Aggregate availability percentages** by grouping normalized product-variant names and calculating the ratio of in-stock snapshots to total observations.
- **Use DuckDB's `read_parquet`** to query remote URLs directly, enabling zero-install analysis of the public dataset.
- **Reference [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py)** for a complete working example that identifies the 15 most constrained SKUs over a 24-hour window.
- **Build persistent databases** with [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) when analyzing multi-week trends or uploading to AI code interpreters.
- **Filter by region** and exclude zero-availability items to focus on bottlenecks that actually affect live inventory.

## Frequently Asked Questions

### How do I identify which products are most frequently out of stock?

Calculate the **availability percentage** for each SKU by dividing the count of snapshots where `stock > 0` by the total number of snapshots for that SKU. Sort the results in ascending order. The items with the lowest percentages spend the most time out of stock and represent your primary bottlenecks. The [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) file in the repository implements this exact logic using DuckDB CTEs.

### Can I analyze more than 24 hours of data without downloading hundreds of files?

Yes. Use the [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) script to merge multiple Parquet shards into a single local DuckDB database file. This script reads the [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json), filters to your desired date range (e.g., the last 30 days), and imports all matching URLs into a persistent table. Once built, you can run complex time-series queries locally or upload the `.duckdb` file to AI assistants for visualization.

### What is the difference between `stock` and `max_quantity` in the dataset?

The `stock` column represents the real-time inventory count for that SKU at the time of the snapshot. The `max_quantity` column indicates the per-order purchase limit (cap) imposed by the store, which often drops to 0 or 1 during high-demand periods. When identifying bottlenecks, focus on `stock` to determine actual availability, but consider `max_quantity` for context on purchasing constraints.

### Do I need to install a database server to run these queries?

No. The examples use **DuckDB**, an embedded analytical database that runs entirely in-process. You can query remote Parquet files directly via HTTP using `read_parquet(['https://...'])` without downloading data to disk or setting up a server. Simply install the `duckdb` Python package or use the DuckDB CLI to execute SQL against the public dataset immediately.