Counting Order Edits by Day | Dataford Interview Questions

Staffline Solutions uses its order operations data to monitor how frequently orders are changed. Write a PostgreSQL query that returns the number of edits made to orders during the last seven calendar days, grouped by the day on which each edit occurred.

For the sample data, assume the query runs on 2026-08-29. In production, use the database server's current date.

Requirements

Join order_edits to orders using the order identifier.
Include edits from today and the six preceding calendar days, excluding older records and future records.
Group by the calendar date extracted from edited_at and return one row per day with the edit count.
Sort the results chronologically by day.

Schema

orders

Column	Type	Description
order_idPK	INTEGER	Unique identifier for the order
order_status	VARCHAR(30)	Current processing status of the order
created_at	TIMESTAMP	Timestamp when the order was created

order_edits

Column	Type	Description
edit_idPK	INTEGER	Unique identifier for the edit event
order_id	INTEGER	References the edited order
edit_type	VARCHAR(40)	Category of change made to the order
editor_name	VARCHAR(100)	Staff member who made the edit
edited_at	TIMESTAMP	Timestamp when the edit occurred

Your question is Counting Order Edits by Day. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem