SQL for Delivery Volume | Dataford Interview Questions

Write a SQL query to find the total number of packages delivered per day over the last month.

Treat “last month” as the previous calendar month relative to the database date. Include every calendar day in that month, including days with no deliveries, and count each package once per delivery date.

Output

Return one row per calendar day with delivery_date and packages_delivered.
Include zero-delivery days and order by delivery_date ascending.

Schema

packages

Column	Type	Description
package_idPK	INT	Unique package identifier
tracking_number	VARCHAR(30)	FedEx tracking number
service_level	VARCHAR(30)	Package service level

package_scans

Column	Type	Description
scan_idPK	INT	Unique package scan identifier
package_id	INT	Package associated with the scan
scan_type	VARCHAR(20)	Operational scan type
scanned_at	TIMESTAMP	Timestamp when the scan occurred

Your question is SQL for Delivery Volume. 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