Identifying Product Availability Bottlenecks Using SQL Aggregation: A DuckDB Approach

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 file indexes all available shards, enabling scripts to discover valid date ranges without guessing URLs. According to the repository's 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 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.

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 script solves this by merging historical shards into a single local DuckDB database.

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:

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 for a complete working example that identifies the 15 most constrained SKUs over a 24-hour window.
• Build persistent databases with 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 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 script to merge multiple Parquet shards into a single local DuckDB database file. This script reads the 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}