Knowledge base index — Demo coffee shop#

An online shop selling coffee, tea and equipment across Europe and the US. Two years of history.

Sources#

• sources/shop/ — SQLite database with the shop's customers, products, orders and order lines. Four tables, all in sources/shop/tables/.

Where to look#

• glossary.md — what "active customer", "revenue", "AOV" etc. mean here
• metrics/ — revenue and active customers, with reference SQL
• examples/ — verified questions and queries; reuse them when a question matches
• rules.md — personal data and default filters

Quick map#

Glossary#

Revenue#

Money from completed sales. See metrics/revenue.md. Always excludes cancelled and refunded orders.

Order value / AOV (average order value)#

Revenue per order: revenue ÷ number of distinct revenue-counting orders.

Active customer#

A customer with at least one revenue-counting order in the last 90 days. See metrics/active-customers.md.

New customer#

A customer whose customers.created_at falls in the period asked about.

Segment#

customers.segment: consumer, small_business or enterprise. "B2B" means small_business + enterprise.

Channel#

orders.channel: web, mobile or marketplace. "Online store" means web + mobile.

Region#

Countries are ISO codes in customers.country. "Europe" / "EU market" means every country except US. "DACH" is DE only in this shop (no AT/CH customers yet).

Rules#

Personal data#

• customers.email is personal data. Do not select it unless the user explicitly asks for contact details. Prefer customers.name or customers.id to identify customers.

Default filters#

• Revenue and sales numbers exclude orders with status IN ('cancelled', 'refunded').
• Order counts include every status unless the question says "completed" or "successful" (then use status = 'delivered').

Dates#

• orders.ordered_at is a UTC timestamp stored as text (YYYY-MM-DD HH:MM:SS). Use SQLite date functions: date(ordered_at), strftime('%Y-%m', ordered_at), date('now', '-30 days').
• "Last month" means the previous full calendar month, not the last 30 days.

Active customers#

Definition: distinct customers with at least one order in the last 90 days whose status is not cancelled or refunded.

SELECT COUNT(DISTINCT o.customer_id) AS active_customers
FROM orders o
WHERE o.status NOT IN ('cancelled', 'refunded')
  AND o.ordered_at >= datetime('now', '-90 days');

Revenue#

Definition: sum of quantity × unit_price over order lines whose order is not cancelled or refunded. order_items.unit_price is the price actually charged (discount already applied), so do not apply discount_pct again. Currency: EUR.

SELECT ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue_eur
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status NOT IN ('cancelled', 'refunded');

Common mistakes:

• Using products.unit_price (list price) instead of order_items.unit_price.
• Counting orders after joining to order_items without COUNT(DISTINCT o.id).

question: What is the cancellation rate by sales channel?
query: |
  SELECT channel,
         COUNT(*) AS orders,
         SUM(status = 'cancelled') AS cancelled,
         ROUND(100.0 * SUM(status = 'cancelled') / COUNT(*), 1) AS cancellation_rate_pct
  FROM orders
  GROUP BY channel
  ORDER BY cancellation_rate_pct DESC
verified_by: demo

question: Which product categories bring in the most revenue?
query: |
  SELECT p.category,
         ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue_eur,
         ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
               / SUM(SUM(oi.quantity * oi.unit_price)) OVER (), 1) AS share_pct
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status NOT IN ('cancelled', 'refunded')
  GROUP BY p.category
  ORDER BY revenue_eur DESC
verified_by: demo

question: What was our revenue per month over the last 12 months?
query: |
  SELECT strftime('%Y-%m', o.ordered_at) AS month,
         ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue_eur
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status NOT IN ('cancelled', 'refunded')
    AND o.ordered_at >= date('now', 'start of month', '-11 months')
  GROUP BY month
  ORDER BY month
notes: Includes the current, partial month.
verified_by: demo

question: Who are our top 10 customers by revenue?
query: |
  SELECT c.id, c.name, c.country,
         ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue_eur,
         COUNT(DISTINCT o.id) AS orders
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status NOT IN ('cancelled', 'refunded')
  GROUP BY c.id, c.name, c.country
  ORDER BY revenue_eur DESC
  LIMIT 10
verified_by: demo

Source: shop#

• Engine: SQLite (read-only connection through the fastdata SQLite connector)
• Dialect notes: no ILIKE (use LIKE, case-insensitive for ASCII); dates are text, use date() / strftime(); no FULL OUTER JOIN on old SQLite versions.
• Freshness: demo data, regenerated by fastdata init --demo.
• Tables: customers, products, orders, order_items.

customers#

One row per customer account.

Referenced by orders.customer_id.

order_items#

One row per product line in an order.

Line revenue = quantity * unit_price.

orders#

One row per order (checkout).

Status lifecycle: placed → shipped → delivered. Cancelled and refunded orders do not count as revenue.

products#

Product catalog with list prices.