Search⌘ K
AI Features
Home
Courses
Forward Deployed Engineer for AI Systems

SQL and API Tasks: Queries, Pagination, and Aggregation

Maintain record identity in SQL queries and paginated API tasks to ensure accurate reporting. Define the grain of each output row, construct queries that prevent double counting, and verify data completeness. Apply correct filters, choose appropriate joins, and handle pagination according to defined contracts. Identify common pitfalls and use practical examples to produce accurate, reliable results across SQL and API workflows.

We'll cover the following...

Order O1 loses 40, but a join gives it two matching rows and the report says 80. Every addition is correct. What went wrong is that the query lost track of what should contribute.

SQL reports and paginated API totals fail in the same way. Both need you to keep each record’s identity intact and to show that every qualifying record was included exactly once.

Start with the grain

Grain means what one output row represents: one order, one customer or one region. Before writing a query, work through this:

  1. State the output grain and what qualifies a record.

  2. Identify the entities that contribute and their keys.

  3. Predict which joins will multiply rows.

  4. Resolve the current record before filtering on current status.

  5. Aggregate, then hand-check a tricky example.

For an API, state the paging contract before writing the loop.

One public preparation guide reports an assessment with implementation, SQL and paginated-API tasks, and another mentions a “regional losses from late deliveries” theme. These are reported possibilities, not guaranteed questions. The tasks below are practice contracts built from those themes.

This lesson runs SQL through Python’s built-in sqlite3 module, so every example runs in the code widget. The SQL used here works the same way in PostgreSQL.

The task: Late-delivery losses by region

Contract:

  • Output one row per region in customers, including regions with no qualifying orders.

  • An order qualifies if it was placed in [2026-07-01, 2026-10-01) and its latest delivery event says late.

  • “Latest” means the greatest event_time. When two events share a time, the larger event_id wins.

  • An order with no events has unknown status, so it doesn’t qualify.

  • Report the count of qualifying orders and their total loss. A region with ...