import { Knex } from 'knex';
export interface RawDailyRow {
day: string;
order_count: string;
gross_cents: string;
refunded_cents: string;
}
export async function fetchDailyOrderTotals(
db: Knex,
from: Date,
to: Date
): Promise<RawDailyRow[]> {
const rows = await db('orders')
.select(db.raw("DATE_TRUNC('day', created_at)::date AS day"))
.count('* AS order_count')
.select(db.raw('COALESCE(SUM(total_cents), 0) AS gross_cents'))
.select(
db.raw(
"COALESCE(SUM(total_cents) FILTER (WHERE status = 'refunded'), 0) AS refunded_cents"
)
)
// half-open interval [from, to): safe for day bucketing at midnight
.where('created_at', '>=', from)
.andWhere('created_at', '<', to)
.groupByRaw("DATE_TRUNC('day', created_at)")
.orderBy('day', 'asc');
return rows as unknown as RawDailyRow[];
}
import { RawDailyRow } from './orderReport.queries';
export interface DailyReportRow {
day: string;
orderCount: number;
grossRevenue: string;
refundedAmount: string;
averageOrderValue: string;
}
function centsToDecimal(cents: number): string {
return (cents / 100).toFixed(2);
}
function toISODate(d: Date): string {
return d.toISOString().slice(0, 10);
}
export function buildDateSpine(from: Date, to: Date): string[] {
const days: string[] = [];
const cursor = new Date(Date.UTC(from.getUTCFullYear(), from.getUTCMonth(), from.getUTCDate()));
const end = new Date(Date.UTC(to.getUTCFullYear(), to.getUTCMonth(), to.getUTCDate()));
while (cursor < end) {
days.push(toISODate(cursor));
cursor.setUTCDate(cursor.getUTCDate() + 1);
}
return days;
}
export function formatDailyReport(
rows: RawDailyRow[],
from: Date,
to: Date
): DailyReportRow[] {
const byDay = new Map<string, RawDailyRow>();
for (const row of rows) {
byDay.set(row.day.slice(0, 10), row);
}
return buildDateSpine(from, to).map((day) => {
const raw = byDay.get(day);
const count = raw ? Number(raw.order_count) : 0;
const gross = raw ? Number(raw.gross_cents) : 0;
const refunded = raw ? Number(raw.refunded_cents) : 0;
const avg = count > 0 ? Math.round(gross / count) : 0;
return {
day,
orderCount: count,
grossRevenue: centsToDecimal(gross),
refundedAmount: centsToDecimal(refunded),
averageOrderValue: centsToDecimal(avg),
};
});
}
import { Knex } from 'knex';
import { fetchDailyOrderTotals } from './orderReport.queries';
import { DailyReportRow, formatDailyReport } from './orderReport.format';
export interface DailyReport {
from: string;
to: string;
rows: DailyReportRow[];
totalGross: string;
}
const MAX_RANGE_DAYS = 366;
const DAY_MS = 24 * 60 * 60 * 1000;
function assertValidRange(from: Date, to: Date): void {
if (isNaN(from.getTime()) || isNaN(to.getTime())) {
throw new Error('Invalid date range');
}
if (from >= to) {
throw new Error('`from` must be before `to`');
}
if (to.getTime() - from.getTime() > MAX_RANGE_DAYS * DAY_MS) {
throw new Error(`Range exceeds ${MAX_RANGE_DAYS} days`);
}
}
export class OrderReportService {
constructor(private readonly db: Knex) {}
async dailyReport(from: Date, to: Date): Promise<DailyReport> {
assertValidRange(from, to);
const raw = await fetchDailyOrderTotals(this.db, from, to);
const rows = formatDailyReport(raw, from, to);
const totalCents = raw.reduce((sum, r) => sum + Number(r.gross_cents), 0);
return {
from: from.toISOString(),
to: to.toISOString(),
rows,
totalGross: (totalCents / 100).toFixed(2),
};
}
}
This snippet shows how a daily sales report is assembled in a Node service: the raw aggregation lives in a query-builder layer, the rounding and shaping of money live in a formatter, and a thin service ties them together. Separating these concerns keeps SQL testable, keeps currency math out of the query, and makes the HTTP-facing shape stable even if the storage changes.
In orderReport.queries.ts, fetchDailyOrderTotals uses Knex to group orders by calendar day. DATE_TRUNC('day', created_at) collapses timestamps into day buckets, and the aggregate columns are built explicitly so the result set is predictable: COUNT(*) for order volume, SUM(total_cents) for gross revenue, and a filtered SUM that only counts refunded rows via FILTER (WHERE status = 'refunded'). Working in integer cents throughout avoids floating-point drift — a classic reporting bug where summed dollars slowly disagree with the ledger. The query is parameterized on a half-open [from, to) interval, which is the safe way to bucket dates because it never double-counts or misses rows at midnight boundaries. whereBetween is deliberately avoided since it is inclusive on both ends.
The raw rows come back as RawDailyRow, a shape that mirrors the database exactly (all cents, all strings from SUM). Postgres returns SUM as a string to preserve precision, so Number(row.gross_cents) conversion is done in one place rather than scattered across the codebase.
In orderReport.format.ts, formatDailyReport turns raw rows into DailyReportRow records meant for JSON output. centsToDecimal divides by 100 and fixes two decimals as a string, keeping the money representation exact for transport. formatDailyReport also computes an averageOrderValue guarded against division by zero, and fills gaps: buildDateSpine generates every day in the range so days with no orders still appear as zero rows rather than silently vanishing — important for charts and for spotting outages.
In OrderReportService, dailyReport orchestrates the flow: validate the range, run the query, format, and return a DailyReport with a summary total. assertValidRange rejects inverted or oversized windows before touching the database, which protects against accidental full-table scans. Because each layer has a single responsibility, the SQL can be unit-tested against a fixture database, the formatter can be tested with plain objects, and the service can be tested with a stubbed query function. This layering is the pattern to reach for whenever a report needs both correct aggregation and a stable, presentation-ready contract.
Related snips
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
export type Settled<R> =
| { status: 'fulfilled'; value: R }
| { status: 'rejected'; reason: unknown };
export interface ConcurrencyOptions {
limit: number;Simple concurrency limiter for batch operations
# BAD: N+1 query problem
@users = User.all
@users.each do |user|
puts user.posts.count # Fires query for each user!
endActiveRecord query optimization and N+1 prevention
import { randomBytes, createHash } from "crypto";
import jwt from "jsonwebtoken";
import { RefreshTokenStore } from "./store";
const ACCESS_SECRET = process.env.ACCESS_SECRET!;
const ACCESS_TTL = "15m";JWT access + refresh token rotation (conceptual)
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def change
add_column :accounts, :settings, :jsonb, null: false, default: {}Postgres JSONB Partial Index for Feature Flags
Share this code
Here's the card — post it anywhere.
