Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 38 ยท Projects and Practice

Project: Sales Analytics

Build a sales analytics report step by step: revenue by month, top products per category, customer ranking, month-over-month growth and a final mart, ready for DuckDB.

In this project you act as the analyst of a small online shop. You will answer the questions a manager actually asks: how is revenue trending, which products carry each category, who are the best customers, and what is the growth from month to month. You will end with a clean summary table you could export to Parquet.

The real DuckDB cannot run in the browser here, so the queries are written in portable SQL against the site's sample shop database, and they run as they are. At the end, the DuckDB-specific shortcuts (QUALIFY, GROUP BY ALL, date_trunc, COPY) are shown read-only so you can translate. Tables available: categories, products, customers, orders, order_items.

Step 0: look at the data

SQL Orders by status ยท database: shop

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

Cancelled orders should not count as revenue, and pending ones have not been fulfilled yet, so we will define revenue as items on orders with status shipped or delivered. Write that rule down once; every query below uses it. In a real DuckDB project it would live in a view:

SQL
CREATE VIEW sales_lines AS
SELECT o.id AS order_id, o.customer_id, o.ordered_at::DATE AS order_date,
       oi.product_id, oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('shipped', 'delivered');

Step 1: monthly revenue

SQL Revenue per month ยท database: shop

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

Step 2: month-over-month growth

Layer a window function on top of the monthly summary. LAG reads the previous month:

SQL Growth with LAG ยท database: shop

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

The first month has no predecessor, so its growth is NULL. Check months with very few orders before drawing conclusions: a small sample gives wild percentages.

Step 3: best products per category

Rank products inside each category and keep the top two:

SQL Top 2 products by revenue per category ยท database: shop

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

In DuckDB you would drop the outer query with QUALIFY rnk <= 2, as seen in QUALIFY and named windows.

Step 4: customer value

Who are the best customers, and how much of the revenue do they represent?

SQL Top customers and their share of revenue ยท database: shop

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

cumulative_pct shows how quickly revenue concentrates in the top customers (the Pareto idea). NTILE(4) assigns spending quartiles, useful for segmenting a campaign.

Step 5: a mart to export

Combine what you built into one table with a row per month and country:

SQL The mart table ยท database: shop

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

In DuckDB you would finish by exporting it, partitioned by month if it is large:

SQL
COPY mart_monthly_country TO 'out/mart_monthly_country.parquet' (FORMAT parquet, COMPRESSION zstd);

The DuckDB version in one block

SQL
CREATE VIEW sales_lines AS
SELECT o.id AS order_id, o.customer_id, date_trunc('month', o.ordered_at::TIMESTAMP) AS month,
       oi.product_id, oi.quantity, oi.quantity * oi.unit_price AS line_total
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status IN ('shipped', 'delivered');

SELECT month,
       sum(line_total)                                        AS revenue,
       sum(line_total) / lag(sum(line_total)) OVER (ORDER BY month) - 1 AS growth
FROM sales_lines
GROUP BY ALL
ORDER BY month;

SELECT * FROM (SELECT category, product, sum(line_total) AS revenue FROM product_sales GROUP BY ALL)
QUALIFY rank() OVER (PARTITION BY category ORDER BY revenue DESC) <= 2;

GROUP BY ALL groups by every non-aggregated column in the select list, and the :: cast and date_trunc give real dates. Everything else is the SQL you just ran.

Extension ideas

  • Add a first purchase month per customer to build a cohort retention table (customers retained after N months).
  • Compute average order value and units per order next to revenue.
  • Flag products that never sold with a LEFT JOIN anti-pattern.
  • Add the data-quality checks from the previous lesson to this pipeline.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why define revenue with a status filter such as shipped or delivered?

    A business rule decides which rows count. Define it once and reuse it everywhere.
  2. 2. Which window function gives last month's value next to this month's?

    LAG reads a value from a previous row in the window order.
  3. 3. What does SUM(spent) OVER () provide?

    An empty OVER makes the whole result one window.
  4. 4. What does GROUP BY ALL do in DuckDB?

    It saves repeating the grouping columns.

Summary

  • Define business rules (what counts as revenue) once, in a view or CTE.
  • Monthly summaries plus LAG give growth; ranking windows give top-N per group.
  • SUM() OVER () turns totals into shares; NTILE segments customers.
  • Finish with a mart table and export it; DuckDB adds QUALIFY, GROUP BY ALL and COPY shortcuts.

Next: Project: Web Log Analysis