Reporting databases are usually shaped as a star: one fact table in the middle, with a row per event (a sale, a ticket, a visit) and the numbers you add up, and dimension tables around it that describe the who, what and when (customers, products, dates).
The first decision is the grain: what exactly one row of the fact table is. For sales it's usually one order line. Every number in the fact must be true at that grain, and every report question becomes "join the fact to the dimensions you need, filter, and group".
A date dimension is the classic example: one row per day with ready-made columns (year, month, month name, weekday, weekend flag), so reports never repeat date functions and days with no sales still exist to join to.
Facts store keys and measures; dimensions store descriptions. Here you'll build them as views over Kestrel's tables, which is how many teams start before materializing them into tables.
CREATE VIEW fact_sales AS
SELECT oi.item_id, o.order_id, o.order_date, o.customer_id, oi.product_id,
oi.quantity, oi.quantity * oi.unit_price AS revenue
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.status <> 'cancelled';
A sales fact at order-line grain.