SQL · Intermediate · 30 min
Daily revenue from changing delivery events
Deduplicate updates before calculating daily revenue.
Task
The delivery_events table contains repeated messages and status changes. Return daily revenue for deliveries whose latest version is completed. Use the day on that latest record; cancelled deliveries contribute nothing.
Requirements
- Choose the greatest version per delivery_id, regardless of arrival order.
- Duplicate copies of the same delivery and version are identical. Deduplicate them.
- Return day and revenue_cents, ordered by day. Use SQLite syntax. Do not change the input table.
- Each delivery has at least one event. Each version has one consistent status, day, and charge.
SQL windowsDeduplicationAggregation grain
Loading exercise…