pettiBuild a monthly report from SQLite data
Turn six individual orders into two monthly totals. Define exactly which records count before trusting the report.
1. Define the report
This exercise counts orders whose status is paid and sums their integer USD cents by order month. It excludes pending and refunded orders. It is not a full accounting report: taxes, fees, refund dates and recognized revenue are outside this small schema.
Open orders in Data and confirm that dates look like 2026-09-01. All values in this fixture use the same ISO calendar-date format. That makes the first seven characters a usable month key.
2. Run the summary
Choose Query and Run. No unlock or editing license is required.
SELECT substr(ordered_at, 1, 7) AS month,
count(*) AS paid_orders,
sum(total_cents) AS paid_total_cents
FROM orders
WHERE status = 'paid'
GROUP BY substr(ordered_at, 1, 7)
ORDER BY month;3. Reconcile the answer
- 2026-09: 2 paid orders, 7000 cents ($70.00).
- 2026-10: 2 paid orders, 7490 cents ($74.90).
- Combined: 4 paid orders, 14490 cents ($144.90).
September is orders 101 and 103. October is orders 105 and 106. Order 102 is pending, and order 104 is refunded. Checking the underlying rows catches mistakes that a plausible-looking total can hide.
4. Adapt it to your own database
Use your actual date column and status rules. Unix timestamps, mixed formats and timestamps with time zones need deliberate conversion; taking the first seven characters is not a universal date solution. Decide which business time zone determines the month.
Keep monetary arithmetic in the database’s documented units. Here, summing integer cents keeps the stored amounts exact. Do not sum several currencies together or treat a formatted text amount as a number without validating it.
Share a reproducible result
Keep the SQL, source snapshot name, filter definition and units beside the numbers. For a table export, return to Data and use the documented CSV export scopes; exporting Entire Table is not the same as exporting this grouped query. Copy the small query result if you need these two rows in a report.
For repeatable optional workflows, explore the bundled Analytics Workbook through plugins and workspaces. A useful one-off report does not require enabling a plugin.
Updated