Analyzing Raw Order Files

Analyzing BookNest's Raw Order Files Directly

The pieces combine into analysis with no load step at all. This query answers a merchandising question, which genres come back most and how long they take to arrive, from three raw files in three formats: the Parquet 129 orders, the JSON Lines event log and the JSON catalog.

476-analysis.sql: returns and delivery times per genre from raw filesSQL

.timer on
SET TimeZone = 'UTC';
WITH lines AS (
  SELECT order_id, status, unnest(items, recursive := true) FROM 'files/orders.parquet'),
books AS (SELECT unnest(books, recursive := true) FROM 'data/books.json'),
delivery AS (
  SELECT order_id, epoch(max(ts) FILTER (type = 'order_delivered')
                         - min(ts) FILTER (type = 'order_placed')) / 86400 AS days
  FROM 'data/order_events.jsonl' GROUP BY order_id)
SELECT b.genre, sum(l.qty) AS units,
       round(100 * count(*) FILTER (l.status = 'returned')
             / count(*) FILTER (l.status IN ('delivered', 'returned')), 2) AS returned_pct,
       round(avg(d.days), 2) AS days_to_deliver
FROM lines l JOIN books b ON b.id = l.book_id JOIN delivery d USING (order_id)
WHERE l.status <> 'cancelled' GROUP BY ALL ORDER BY returned_pct DESC;

Output

┌─────────────────┬────────┬──────────────┬─────────────────┐
│      genre      │ units  │ returned_pct │ days_to_deliver │
│     varchar     │ int128 │    double    │     double      │
├─────────────────┼────────┼──────────────┼─────────────────┤
│ Technology      │  23659 │         4.31 │            6.21 │
│ Fiction         │  46255 │         4.26 │            6.22 │
...
│ Travel          │   4807 │         3.51 │            6.19 │
└─────────────────┴────────┴──────────────┴─────────────────┘
Run Time (s): real 0.657 user 0.897386 sys 0.246153

Returns run at 3.5 to 4.3 percent of delivered lines, and delivery takes about six days for every genre, as the sample generator's fixed lifecycle intervals dictate (Order Events as JSON Lines). The analysis took 0.6 to 0.7 s on the shared host, mostly parsing the 37 MB event log. Explore raw files in place like this, declare types once the questions settle, and promote recurring queries to Parquet extracts or dbt 37,942 models (Transforming Data with dbt).

MENU