Scenario: Brightlane's operations team is preparing a January 2024 month-end reconciliation and needs a daily breakdown of order activity for that month only.
Task: Write a query to return each calendar day in January 2024 on which at least one order was placed, the number of orders placed on that day, and the total orders revenue.
Assumptions:
- The
orderstable holds one row per placed order, with the placement timestamp stored inordered_atand the order amount stored intotal_amount. - A calendar day is identified by its date and covers every order placed within that day.
- The result covers only
ordersplaced within January 2024 — that is, on or after January 1, 2024 and on or before January 31, 2024. Days within the window with no recorded orders do not appear in the result.
Output:
- One row per calendar day in January 2024 with at least one placed order.
- Columns in this order:
day,order_count,daily_revenue.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
DATE_TRUNC('day', ordered_at)::date AS DAY,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_revenue
FROM
orders
WHERE
DATE_TRUNC('month', ordered_at)::date = '2024-01-01'::date
GROUP BY
DATE_TRUNC('day', ordered_at)The shape
The WHERE clamps the data to a single calendar month before any grouping runs, then date_trunc('day', ordered_at) collapses every surviving timestamp to its calendar day. The aggregate returns one row per day inside January 2024 that had at least one order, with the count and the day's revenue.
Clause by clause
SELECT date_trunc('day', ordered_at)::date AS day, COUNT(*) AS order_count, SUM(total_amount) AS daily_revenuereturns one row per day with three columns. The::datecast normalises the truncated timestamp to a plain date.COUNT(*)counts the day's orders andSUM(total_amount)adds up the dollar amounts.FROM ordersreads every placed order before the filter runs.WHERE date_trunc('month', ordered_at)::date = '2024-01-01'::datekeeps only orders whose month-truncated date equals January 1, 2024. Every order placed at any time during January 2024 truncates to that same value, so the filter selects exactly the January 2024 rows and discards everything else before grouping starts.GROUP BY date_trunc('day', ordered_at)repeats the day-truncation as the grouping key. Two orders placed at different times on January 5, 2024 both truncate to2024-01-05and land in the same day's row.
Why filter before grouping and not after
The WHERE runs before GROUP BY in SQL's evaluation order, which is what makes this filter cheap and correct. Non-January rows are discarded before the aggregator ever sees them, so no group is ever built for a February or March day, and the result naturally contains only days in the window. A filter applied after grouping would have to run against an aggregated result and is a different construct entirely. Here the question is "which raw rows belong in scope," and that question is exactly what WHERE answers.
The trap
Days inside January 2024 with no recorded orders do not appear in the result. GROUP BY only produces a row for a value that actually exists in the underlying rows, so January 5 and January 12 appear because they have orders, but January 6 and January 11 are absent because nothing was placed on those days. The output of six rows for January 2024 is not a one-row-per-day-of-the-month report; it is a one-row-per-day-with-activity report. A full calendar view would need a separately-generated date spine joined to this result, which is a different problem.
You practiced restricting orders to a single calendar month before truncating to day, so the report only contains days within the targeted reconciliation window.
