Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 40 ยท Projects and Practice

Project: Sales Reporting Database in MariaDB

Turn an order database into business reports with MariaDB: monthly revenue, growth, running totals, top products per category, customer segments, summary tables and a reporting account.

Operational databases are built to record orders; the business wants answers: how much did we sell each month, which products drive revenue, who are the best customers, and who has gone quiet? In this project you write those reports on an e-commerce schema with customers, products, orders and order items, using joins, aggregates, window functions, CTEs and views. The data set is small so every result can be checked by eye, but each query is written the way it would be on millions of rows.

All queries run here. MariaDB-specific alternatives (DATE_FORMAT, LAST_DAY, WITH ROLLUP, events) are shown beside them.

The model

erDiagram
  CUSTOMERS ||--o{ ORDERS : places
  ORDERS ||--|{ ORDER_ITEMS : contains
  PRODUCTS ||--o{ ORDER_ITEMS : "sold in"
  CATEGORIES ||--o{ PRODUCTS : groups

Revenue lives in order_items (quantity * unit_price); orders adds the date, the customer and the status.

Two decisions shape every report:

  1. Which orders count? Cancelled orders are not revenue. Put that rule in one place, a view, so every report agrees.
  2. Which price? Use order_items.unit_price (the price at the time of sale), never products.price.
SQL Look at the data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Step 1: one view for "sales lines"

SQL
CREATE VIEW sales_lines AS
SELECT o.id AS order_id, o.customer_id, o.ordered_at,
       DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,
       p.id AS product_id, p.name AS product, p.category_id,
       i.quantity, i.unit_price,
       i.quantity * i.unit_price AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p    ON p.id = i.product_id
WHERE o.status <> 'cancelled';
SQL The sales_lines view ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The remaining steps each repeat this view definition in their editor (editors do not share state), so the queries stay runnable on their own.

Step 2: monthly revenue, growth and running total

SQL
WITH monthly AS (
  SELECT month, COUNT(DISTINCT order_id) AS orders, SUM(revenue) AS revenue
  FROM sales_lines
  GROUP BY month
)
SELECT month, orders, ROUND(revenue, 2) AS revenue,
       ROUND(100 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct,
       ROUND(SUM(revenue) OVER (ORDER BY month), 2) AS running_total
FROM monthly
ORDER BY month;
SQL Revenue per month with growth ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first month has NULL growth because there is no previous month. If a month had no orders at all it would be missing, not zero; join to a calendar built with a recursive CTE (or MariaDB's seq_1_to_24) when gaps matter.

Step 3: top products per category

SQL Best-selling product in each category ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This is the "top N per group" pattern: aggregate, rank in a CTE, filter outside. A tie at rank 2 would return more than two rows; use ROW_NUMBER() with a tie-breaker for exactly N.

Step 4: customer value and segments

Group customers into quartiles of lifetime spend with NTILE(4), and show how recently they ordered, a simplified RFM (recency, frequency, monetary) analysis:

SQL Customer lifetime value and quartiles ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Quartile 1 holds the best customers: the list for a loyalty offer. Customers with a high days_since_last and a high quartile are the "at risk" group worth a win-back campaign.

Step 5: repeat customers and cohorts

How many customers ordered more than once? And per signup month, what share came back?

SQL Repeat purchase rate ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(orders > 1) works because a true comparison is 1 in both MariaDB and the portable engine. For a cohort table, group the same data by strftime('%Y-%m', signed_up) (DATE_FORMAT(signed_up, '%Y-%m') in MariaDB) and by months since signup.

Step 6: subtotals with ROLLUP (MariaDB)

MariaDB can produce category subtotals and a grand total in one pass:

SQL
SELECT COALESCE(category, 'ALL CATEGORIES') AS category,
       COALESCE(month, 'ALL MONTHS')        AS month,
       ROUND(SUM(revenue), 2)               AS revenue
FROM sales_lines_with_category
GROUP BY category, month WITH ROLLUP;

The portable engine lacks ROLLUP; the equivalent is UNION ALL of the detail, subtotal and total queries, as in aggregate functions and GROUP BY.

Step 7: make it fast and repeatable

  1. Indexes. orders (status, ordered_at) for date-range filters, order_items (order_id) and (product_id) for joins (foreign keys), orders (customer_id, ordered_at) for per-customer history. Use EXPLAIN on each report; avoid YEAR(ordered_at) = ....
  2. Summary table. When reports scan millions of lines, keep a daily table and refresh it from an event:
SQL
CREATE TABLE daily_sales (
  day DATE PRIMARY KEY, orders INT NOT NULL, revenue DECIMAL(12,2) NOT NULL
);

CREATE EVENT refresh_daily_sales
ON SCHEDULE EVERY 1 HOUR
DO REPLACE INTO daily_sales
   SELECT DATE(ordered_at), COUNT(DISTINCT order_id), SUM(revenue)
   FROM sales_lines
   WHERE ordered_at >= CURDATE() - INTERVAL 3 DAY
   GROUP BY DATE(ordered_at);
  1. Replica for reports. Point BI tools at a replica so heavy queries do not slow checkout; set max_statement_time on the reporting account.
  2. Account. reporter gets SELECT on sales_lines and daily_sales only (users, grants and roles); customer e-mail addresses stay out of the view.
  3. Partitioning. For very large order tables, PARTITION BY RANGE (YEAR(ordered_at)) lets you drop a whole old year instantly and prunes scans, but the primary key must include the partition column.

Extensions to try

  • A product "affinity" report: which two products appear together in the same order most often (self-join on order_items).
  • Year-over-year comparison with LAG(revenue, 12) over monthly rows.
  • A dashboard query that returns the last 30 days with zero-filled gaps.
  • Add WITH SYSTEM VERSIONING to products and report revenue at the price valid on the order date.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should revenue use order_items.unit_price instead of products.price?

    Historic reports must not change when today's catalog prices change.
  2. 2. Which technique returns the top 2 products per category?

    Window functions cannot be filtered in WHERE directly, so rank in a CTE and filter outside.
  3. 3. What does NTILE(4) OVER (ORDER BY lifetime_value DESC) return for the best 25% of customers?

    Buckets are numbered from 1, and the first bucket gets the first (highest) rows in the ordering.
  4. 4. Why put the 'not cancelled' rule into a view?

    A single definition keeps reports consistent and easy to change.

Summary

  • Define "revenue" once (a view), using the sale-time price and excluding cancelled orders.
  • Monthly totals use aggregates plus LAG and running SUM; top-N and segments use RANK, ROW_NUMBER and NTILE in CTEs.
  • Index foreign keys and date filters; use summary tables, events and replicas when reports get heavy.
  • Give reporting users read-only access to views, not raw tables.

Next: Practice: MariaDB Exercises