# Calculating Purchase Window Overlaps for Multiple Products

> Learn to calculate purchase window overlaps for multiple products using DuckDB on Parquet shards. Find simultaneous stock availability efficiently.

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

---

**Use DuckDB to query deterministic 6-hour Parquet shards from the Bambu Lab Store Filament Tracker, filtering for `stock > 0` and grouping by timestamp to identify when all target SKUs are simultaneously available.**

The `nelsonjchen/bbl-tracker-public-db` repository maintains a time-series database of Bambu Lab filament stock levels, stored as Parquet files in deterministic 6-hour shards. Calculating purchase window overlaps for multiple products involves analyzing these snapshots to find exact timestamps when two or more items are concurrently in stock. This guide demonstrates how to query the public dataset using DuckDB to identify these multi-item availability windows.

## Understanding the Parquet Shard Structure

The database stores stock snapshots in **deterministic 6-hour shards** using a predictable naming convention (e.g., `2026-02-16-0000.parquet`). Each file contains rows with UTC timestamps, product family, variant name, current stock levels, region codes (such as `us`), and other metadata. You can compute exact URLs for any date range without directory listings, or reference the remote [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) to discover available shards.

## Querying Purchase Window Overlaps with DuckDB

DuckDB can ingest remote Parquet files directly via HTTP without downloading them first, making it ideal for analyzing these distributed shards.

### Basic Overlap Detection

To find when specific products are simultaneously in stock, filter for rows where `stock > 0` and count distinct products per timestamp:

```sql
WITH in_stock AS (
    SELECT
        timestamp,
        product_name || ' - ' || variant_name AS full_name
    FROM read_parquet(
        'https://db-public.bbltracker.com/2026-02-01-0000.parquet',
        'https://db-public.bbltracker.com/2026-02-01-0600.parquet'
        -- Add additional shard URLs as needed
    )
    WHERE region = 'us'
      AND stock > 0
)
SELECT
    timestamp,
    COUNT(DISTINCT full_name) AS products_in_stock
FROM in_stock
WHERE full_name IN (
    'PLA Matte White',
    'PLA Matte Black',
    'PLA Wood Rosewood',
    'PETG HF Black'
)
GROUP BY timestamp
HAVING COUNT(DISTINCT full_name) = 4
ORDER BY timestamp;

```

This returns every UTC timestamp where all four target SKUs were concurrently available.

### Aggregating Continuous Windows

Raw timestamps reflect 30- or 60-minute sampling intervals. To convert these into human-readable continuous windows, use window functions to detect gaps and group consecutive periods:

```sql
WITH in_stock AS (
    -- Same CTE as above
    SELECT timestamp, product_name || ' - ' || variant_name AS sku
    FROM read_parquet('https://db-public.bbltracker.com/2026-02-01-0000.parquet')
    WHERE region = 'us' AND stock > 0
),
overlaps AS (
    SELECT
        timestamp,
        LAG(timestamp) OVER (ORDER BY timestamp) AS prev_ts,
        CASE
            WHEN timestamp - LAG(timestamp) OVER (ORDER BY timestamp) = INTERVAL '30 minutes'
            THEN 0 ELSE 1
        END AS new_window
    FROM in_stock
    WHERE sku IN ('PLA Matte White', 'PLA Matte Black', 'PLA Wood Rosewood', 'PETG HF Black')
    GROUP BY timestamp
    HAVING COUNT(DISTINCT sku) = 4
),
grouped AS (
    SELECT timestamp, SUM(new_window) OVER (ORDER BY timestamp) AS grp
    FROM overlaps
)
SELECT
    MIN(timestamp) AS window_start,
    MAX(timestamp) AS window_end,
    COUNT(*) * 30 AS window_minutes
FROM grouped
GROUP BY grp
ORDER BY window_start;

```

This produces rows showing start time, end time, and duration for each continuous availability window.

## Automating Analysis with Python

For dynamic date ranges and automated SKU monitoring, use the Python `duckdb` module to build URL lists programmatically:

```python
import duckdb
from datetime import datetime, timedelta

def build_shard_urls(days: int = 7):
    """Generate URLs for the last N days of 6-hour shards."""
    base = "https://db-public.bbltracker.com"
    urls = []
    now = datetime.utcnow()
    for i in range(days * 4):  # 4 shards per day

        ts = now - timedelta(hours=6 * i)
        urls.append(f"{base}/{ts.strftime('%Y-%m-%d-%H%M')}.parquet")
    return sorted(urls)

# Define target SKUs

target_skus = {
    "PLA Matte White",
    "PLA Matte Black", 
    "PLA Wood Rosewood",
    "PETG HF Black"
}

# Build query

urls = build_shard_urls()
url_list = ", ".join(f"'{u}'" for u in urls)

query = f"""
WITH in_stock AS (
    SELECT
        timestamp,
        product_name || ' - ' || variant_name AS sku
    FROM read_parquet({url_list})
    WHERE region = 'us' AND stock > 0
)
SELECT
    timestamp,
    COUNT(DISTINCT sku) AS sku_count
FROM in_stock
WHERE sku IN ({', '.join(f"'{s}'" for s in target_skus)})
GROUP BY timestamp
HAVING COUNT(DISTINCT sku) = {len(target_skus)}
ORDER BY timestamp;
"""

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

```

Convert the resulting timestamps to your local timezone and merge consecutive rows to identify exact purchase windows (e.g., "2026-02-02 02:00 – 08:00 UTC").

## Command-Line Overlap Checks

For quick ad-hoc checks without Python, use the DuckDB CLI directly:

```bash
duckdb -c "
WITH s AS (
  SELECT timestamp, product_name||' - '||variant_name AS sku
  FROM read_parquet('https://db-public.bbltracker.com/2026-02-01-0000.parquet')
  WHERE region='us' AND stock>0
)
SELECT timestamp
FROM s
WHERE sku IN ('PLA Matte White','PLA Matte Black','PLA Wood Rosewood','PETG HF Black')
GROUP BY timestamp
HAVING COUNT(DISTINCT sku)=4
ORDER BY timestamp;
"

```

## Real-World Bottleneck Analysis

The repository demonstrates a practical application of this technique in [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md). In the documented scenario, analyzing a four-item cart revealed that **Black PETG** was the limiting factor preventing a complete purchase. By excluding that SKU, the analysis uncovered a **16-hour overlap window** for the remaining three items【https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md#L34】. This same methodology applies to any combination of products—simply adjust the SKU list and threshold count to match your target set.

## Key Repository Files

| File | Purpose | Link |
|------|---------|------|
| [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md) | Documents the real-world overlap window scenario and bottleneck analysis methodology. | [README.md](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/README.md) |
| [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) | Demonstrates agentic queries for calculating availability percentages and identifying bottlenecks. | [script.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/script.py) |
| [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) | Utility to merge multiple Parquet shards into a single DuckDB file for offline analysis. | [reconstruct_db.py](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/master/reconstruct_db.py) |
| [`manifest.json`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/manifest.json) | Remote index of all available Parquet shards; use to discover files covering specific date ranges. | [manifest.json](https://db-public.bbltracker.com/manifest.json) |

## Summary

- The Bambu Lab Store Filament Tracker uses **deterministic 6-hour Parquet shards** that allow direct URL construction for any date range without directory listings.
- **DuckDB** can query these remote Parquet files via HTTP using `read_parquet()`, eliminating the need for intermediate downloads.
- To calculate purchase window overlaps, filter for `stock > 0`, group by timestamp, and use `HAVING COUNT(DISTINCT sku) = N` to find moments when all target products are available.
- Aggregate consecutive timestamps using window functions to convert discrete snapshots into continuous availability windows measured in minutes.
- The repository provides working examples in [`script.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/script.py) and documents a real 16-hour overlap case in [`README.md`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/README.md).

## Frequently Asked Questions

### What is the snapshot interval for stock data in the Parquet files?

The Bambu Lab Store Filament Tracker captures stock snapshots at **30- or 60-minute intervals**, depending on the specific product and time period. Each Parquet shard covers a 6-hour window and contains multiple snapshots, allowing you to detect purchase window overlaps at the granularity of these sampling intervals.

### Can I calculate overlaps for specific regions only?

Yes. Each row in the Parquet files includes a `region` column (such as `us`, `eu`, or `cn`). To calculate purchase window overlaps for a specific region, add `WHERE region = 'us'` (or your target region) to your DuckDB queries. This ensures you only count stock availability for the regional store you intend to purchase from.

### How do I analyze overlaps for more than two products?

To find when three or more products are simultaneously in stock, modify the `HAVING` clause in your SQL query to match your target count. For example, use `HAVING COUNT(DISTINCT sku) = 4` when analyzing four specific products. The query will return only those timestamps where all four distinct SKUs have `stock > 0`, indicating a complete purchase window overlap for your entire cart.

### Do I need to download the Parquet files before querying them?

No. DuckDB supports **remote Parquet reading** via HTTP, allowing you to query files directly from `https://db-public.bbltracker.com/` without downloading them first. This approach minimizes bandwidth usage and enables fast ad-hoc analysis across multiple 6-hour shards. For offline analysis, you can use [`reconstruct_db.py`](https://github.com/nelsonjchen/bbl-tracker-public-db/blob/main/reconstruct_db.py) to merge shards into a local DuckDB file.