Use Cases

Use Cases

Concrete scenarios Blinkql is designed for. Example queries use DuckDB SQL; table names refer to files imported into Blinkql.

---

1. Calculating OEE from an MES export

Who: Production or process engineer

Situation: IIoT counters log one row per machine per minute: planned minutes, run minutes, ideal cycle time, total count and good count. For 40 machines, one month is about 1.7 million rows, beyond what Excel can open in a single sheet.

Today: The file gets split, pivot tables time out, and OEE formulas are copied across sheets by hand.

With Blinkql: Drop the file, pick the OEE template (or write the SQL), and get a ranked table and chart of the worst-performing machines in seconds.

WITH daily AS (
  SELECT
    machine_id,
    shift_date,
    SUM(run_min) / SUM(planned_min)                        AS availability,
    SUM(ideal_cycle_s * total_count) / 60.0 / SUM(run_min) AS performance,
    SUM(good_count) / SUM(total_count)                     AS quality
  FROM production_log
  GROUP BY machine_id, shift_date
)
SELECT *, availability * performance * quality AS oee
FROM daily
ORDER BY oee ASC;

---

2. Downtime Pareto and MTTR for maintenance reviews

Who: Maintenance manager

Situation: A breakdown log from an Andon system or CMMS lists machine, failure reason, start time and end time. The weekly review needs to know which reasons cause most of the downtime and which machines take longest to fix.

With Blinkql: A Pareto chart (bars by downtime, cumulative percentage line) and an MTTR table, both from SQL.

-- Pareto: downtime by reason with cumulative %
WITH by_reason AS (
  SELECT reason, SUM(date_diff('minute', start_ts, end_ts)) AS downtime_min
  FROM breakdowns
  GROUP BY reason
)
SELECT
  reason,
  downtime_min,
  ROUND(100.0 * SUM(downtime_min) OVER (ORDER BY downtime_min DESC)
        / SUM(downtime_min) OVER (), 1) AS cumulative_pct
FROM by_reason
ORDER BY downtime_min DESC;

-- Mean time to repair per machine
SELECT
  machine_id,
  COUNT(*)                                   AS breakdowns,
  AVG(date_diff('minute', start_ts, end_ts)) AS mttr_min
FROM breakdowns
GROUP BY machine_id
ORDER BY mttr_min DESC;

---

3. Shift reports when the night shift crosses midnight

Who: Shift supervisor or plant HR

Situation: The night shift runs 22:00 to 06:00. Grouping by calendar date splits each night shift across two days, so output and attendance numbers look wrong. The data also contains employee information that shouldn't be uploaded anywhere.

With Blinkql: Assign each event to its production day and shift, then report. The data never leaves the supervisor's laptop.

SELECT
  CAST(event_ts - INTERVAL 6 HOUR AS DATE) AS production_day,  -- production day starts at 06:00
  CASE
    WHEN hour(event_ts) >= 6  AND hour(event_ts) < 14 THEN 'A'
    WHEN hour(event_ts) >= 14 AND hour(event_ts) < 22 THEN 'B'
    ELSE 'C'
  END AS shift,
  SUM(good_count) AS output
FROM production_log
GROUP BY ALL
ORDER BY production_day, shift;

---

4. Reconciling ERP and MES exports

Who: Stores or inventory controller, plant accountant

Situation: The ERP says how much material stores issued; the MES says how much production consumed. The two exports come from different systems, and a mismatch means loss, scrap that wasn't recorded, or a data-entry error.

Today: VLOOKUPs across two large sheets, which are slow and fragile.

With Blinkql: A full outer join that surfaces every part where the two systems disagree, largest variance first.

WITH issued AS (
  SELECT part_code, SUM(qty) AS qty_issued
  FROM erp_material_issues
  GROUP BY part_code
),
consumed AS (
  SELECT part_code, SUM(qty) AS qty_consumed
  FROM mes_material_consumption
  GROUP BY part_code
),
recon AS (
  SELECT
    COALESCE(i.part_code, c.part_code) AS part_code,
    COALESCE(i.qty_issued, 0)          AS qty_issued,
    COALESCE(c.qty_consumed, 0)        AS qty_consumed
  FROM issued i
  FULL OUTER JOIN consumed c ON i.part_code = c.part_code
)
SELECT *, qty_issued - qty_consumed AS variance
FROM recon
WHERE qty_issued <> qty_consumed
ORDER BY abs(qty_issued - qty_consumed) DESC;

---

5. Validating a data-migration file before go-live

Who: Implementation, onboarding or support engineer

Situation: Before importing a client's 300,000-row asset master into a new system, the file needs checking for duplicate keys, missing mandatory fields and references to locations that don't exist. The file is client data covered by an NDA, so it can't go into third-party tools.

With Blinkql: One-click profiling plus a few checks, entirely on the engineer's machine.

-- Column profile: types, min/max, distinct counts, null percentage
SUMMARIZE asset_master;

-- Duplicate keys that would fail the import
SELECT asset_tag, COUNT(*) AS occurrences
FROM asset_master
GROUP BY asset_tag
HAVING COUNT(*) > 1;

-- Assets pointing at locations that don't exist
SELECT a.*
FROM asset_master a
ANTI JOIN locations l ON a.location_code = l.location_code;

---

6. Inspecting a remote Parquet file without downloading it

Who: Data engineer

Situation: A multi-gigabyte Parquet file sits in object storage. Before wiring it into a pipeline, the engineer wants its schema, row-group layout and a quick distribution of one column.

With Blinkql: Query the URL directly. DuckDB-wasm reads the file footer and only the byte ranges it needs. (The host must allow CORS and HTTP range requests.)

-- Schema
DESCRIBE SELECT * FROM 'https://data.example.com/exports/events.parquet';

-- Row groups, sizes and compression from the file footer
SELECT * FROM parquet_metadata('https://data.example.com/exports/events.parquet');

-- Reads only the event_type column
SELECT event_type, COUNT(*) AS n
FROM 'https://data.example.com/exports/events.parquet'
GROUP BY event_type
ORDER BY n DESC;

---

7. Incident triage on a log export

Who: Backend or support engineer

Situation: A customer sends a large JSON-lines log export from their environment. The contract doesn't allow uploading it to a hosted log platform, and the file is too big for a text editor.

With Blinkql: Find the error spike and the slowest endpoints in minutes.

-- Errors per minute
SELECT
  date_trunc('minute', ts)                AS minute,
  COUNT(*) FILTER (WHERE level = 'error') AS errors,
  COUNT(*)                                AS total
FROM read_json('app-logs.jsonl')
GROUP BY minute
ORDER BY minute;

-- Slowest endpoints by p95 latency
SELECT
  endpoint,
  COUNT(*)                         AS requests,
  quantile_cont(latency_ms, 0.95)  AS p95_ms
FROM read_json('app-logs.jsonl')
GROUP BY endpoint
ORDER BY p95_ms DESC
LIMIT 20;

---

8. Teaching SQL with real data

Who: Trainers, colleges, bootcamps, team leads onboarding analysts

Situation: Lab machines are locked down, installing a database takes days of coordination, and toy datasets don't prepare learners for real work.

With Blinkql: Share one link. Built-in sample datasets (manufacturing telemetry, breakdowns, shift data) load instantly, and exercises can be shared as query links that learners open and run.

---

Pattern: share the analysis, not the data

A quality head writes a rejection-analysis query (defect rate by machine, part and defect code) and sends the Blinkql link to five plants. Each plant opens the link, maps it to its own export and runs it locally. Everyone uses the same logic, and no plant's data is ever sent anywhere.

---

Have a use case we haven't covered? Share it in Discussions. The best ones become built-in templates.