Optimising the realtime queries to use ranking

[ANIALLATOR114]

Summary by Sourcery

Optimize real-time schedule queries by implementing ranking to select the most recent entries for each trip and increase the batch size for schedule processing to enhance performance.

Enhancements:

• Optimize the retrieval of real-time schedules by using ranking to select the most recent entries for each trip, improving query efficiency.
• Increase the batch size for processing schedules from 200 to 400, potentially improving performance by reducing the number of iterations.

[sourcery-ai[bot]]

Reviewer's Guide by Sourcery

This pull request optimizes realtime queries by implementing ranking instead of using MAX aggregation, and increases the batch size for processing schedules. The changes aim to improve query performance and efficiency in handling realtime data.

Sequence Diagram

sequenceDiagram
    participant C as Client
    participant R as RealtimeRepo
    participant DB as Database
    C->>R: get_realtime_schedules_for_trips(trips)
    R->>DB: Execute ranked subqueries (stop times & trips)
    R->>DB: Execute main query with top-ranked rows
    R->>R: Process results concurrently
    R-->>C: Return realtime schedules

File-Level Changes

Change	Details	Files
Implement ranking for realtime queries instead of using MAX aggregation	Introduce subqueries to rank rows by created_at within each trip_id Use func.rank() with partition_by and order_by clauses for both stop times and trips Filter only the top-ranked rows in the main query Execute queries concurrently using asyncio.gather()	SimplyTransport/domain/realtime/realtime_schedule/repo.py
Increase batch size for processing schedules	Change chunk size from 200 to 400 in the chunk_list function	SimplyTransport/timescale/services/delays_service.py

---

Tips and commands

#### Interacting with Sourcery - **Trigger a new review:** Comment `@sourcery-ai review` on the pull request. - **Continue discussions:** Reply directly to Sourcery's review comments. - **Generate a GitHub issue from a review comment:** Ask Sourcery to create an issue from a review comment by replying to it. #### Customizing Your Experience Access your [dashboard](https://app.sourcery.ai) to: - Enable or disable review features such as the Sourcery-generated pull request summary, the reviewer's guide, and others. - Change the review language. - Add, remove or edit custom review instructions. - Adjust other review settings. #### Getting Help - [Contact our support team](mailto:support@sourcery.ai) for questions or feedback. - Visit our [documentation](https://docs.sourcery.ai) for detailed guides and information. - Keep in touch with the Sourcery team by following us on [X/Twitter](https://x.com/SourceryAI), [LinkedIn](https://www.linkedin.com/company/sourcery-ai/) or [GitHub](https://github.com/sourcery-ai).