Calculating Product Availability Percentage Over Time Using DuckDB

Use DuckDB's read_parquet function to query hourly snapshot files from Cloudflare R2, calculate the ratio of in-stock observations to total observations, and identify product availability bottlenecks over any time window.

The nelsonjchen/bbl-tracker-public-db repository maintains a public dataset of Bambu Lab store stock snapshots captured every hour and stored as Parquet files. By leveraging DuckDB's native ability to query Parquet files directly over HTTP, you can calculate product availability percentages without downloading entire datasets locally, enabling rapid analysis of stock trends and supply constraints.

Understanding the Dataset Architecture

Before writing queries, you need to understand how the data is organized and discovered.

Parquet Files and Manifest Discovery

The dataset consists of hourly Parquet files stored on Cloudflare R2, each covering a 6-hour UTC slice with filenames like 2026-02-16-0000.parquet. Rather than hardcoding URLs, the repository provides a manifest.json file that enumerates all available files with row counts.

In script.py, the manifest discovery logic fetches this index to programmatically determine which files to query:

import requests

# Fetch manifest from Cloudflare R2

manifest_url = "https://db-public.bbltracker.com/manifest.json"
manifest = requests.get(manifest_url).json()

# Select the 4 most recent files (~24 hours of data)

recent_files = manifest["files"][-4:]
base_url = "https://db-public.bbltracker.com"
urls = [f"{base_url}/{f['name']}" for f in recent_files]

Querying Product Availability with DuckDB

The core analysis happens within a single DuckDB SQL pipeline that reads Parquet files directly from remote URLs using read_parquet().

The Core SQL Pipeline

The availability calculation logic resides in lines 40-68 of script.py. The query uses Common Table Expressions (CTEs) to progressively transform the data:

import duckdb

query = f"""
WITH subset AS (
    SELECT
        timestamp,
        product_name || ' - ' || variant_name AS full_name,
        stock,
        max_quantity,
        region
    FROM read_parquet({urls})
    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
    FROM subset
    GROUP BY full_name
)
SELECT
    full_name,
    ROUND((in_stock_snapshots::FLOAT / total_snapshots) * 100, 1) AS availability_pct,
    max_stock_seen,
    total_snapshots
FROM stats
ORDER BY availability_pct ASC
LIMIT 15;
"""

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

This query calculates the availability percentage using the formula:

ROUND((in_stock_snapshots::FLOAT / total_snapshots) * 100, 1)

Filtering by Region and Time Range

The region column allows you to isolate specific markets. The example filters to region = 'us', but you can substitute any supported region code. For custom time windows, modify the URL generation logic to select specific date ranges from the manifest rather than the most recent files.

Implementing Custom Availability Analysis

For scenarios requiring different time windows or regions, you can adapt the query pattern.

Pure SQL Implementation

If you prefer using the DuckDB CLI or embedding SQL directly in other tools, use this standalone version:

-- Calculate availability for specific files
SELECT
    product_name || ' - ' || variant_name AS full_name,
    ROUND(
        100.0 * SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) / COUNT(*), 
        1
    ) AS availability_pct,
    MAX(stock) AS max_stock_seen
FROM read_parquet(
    'https://db-public.bbltracker.com/2026-02-16-0000.parquet',
    'https://db-public.bbltracker.com/2026-02-16-0600.parquet'
)
WHERE region = 'us'
GROUP BY full_name
ORDER BY availability_pct ASC
LIMIT 15;

Extended Time Window Analysis

For analyzing availability over weeks or months rather than hours, the repository includes reconstruct_db.py. This utility merges multiple Parquet files into a single local DuckDB database file, optimizing performance for large historical analyses while maintaining the same SQL calculation logic.

Summary

• DuckDB's read_parquet enables direct SQL queries against remote Parquet files on Cloudflare R2 without local downloads.
• The availability percentage formula in_stock_snapshots / total_snapshots * 100 identifies products with the lowest stock consistency.
• script.py demonstrates the complete pipeline: manifest discovery, URL generation, and CTE-based SQL analysis for the last 24 hours.
• reconstruct_db.py supports extended time window analysis by consolidating multiple Parquet files into a local database.
• Region-specific filtering via the region column allows market-specific availability calculations.

Frequently Asked Questions

How do I query a specific date range instead of the last 24 hours?

Modify the URL generation logic in your Python script to filter the manifest files by date prefix. Parse the filename timestamps (format: YYYY-MM-DD-HHMM.parquet) and select only those falling within your target range, then pass that filtered list to read_parquet().

Can I use this dataset without Python?

Yes. DuckDB supports multiple language bindings and a standalone CLI. You can execute the SQL queries directly in the DuckDB CLI or use the R, Julia, or Node.js DuckDB packages. The read_parquet function works identically across all implementations.

What does the availability percentage actually measure?

The percentage represents the proportion of hourly snapshots where a product had positive stock (stock > 0) relative to the total number of snapshots observed during the selected time window. A value of 75% means the product was available for purchase in 3 out of 4 hourly observations.

How do I analyze availability for regions other than the US?

Change the WHERE region = 'us' clause in the SQL query to your target region code. The dataset includes multiple region columns; verify the specific region identifiers available in the manifest or sample a Parquet file to see the distinct values in the region column.

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