Changes between Version 16 and Version 17 of AnalyticalStatisticalQuerying

Timestamp:

09/30/25 21:44:46 (13 days ago)

Author:

222077

Comment:

--

AnalyticalStatisticalQuerying

@@ -275 +275 @@
 Replace correlated subquery for `AVG` with a window function or single aggregation to avoid repeated scans.
 Indexing opportunities:
-   - `ticket(account_id, date_purchased)` for fast filtering.
-   - Foreign key indexes on `ticket.trip_id`, `trip.route_id`, and `route.{from_location_id, to_location_id, transport_organizer_id}`.
+`ticket(account_id, date_purchased)` for fast filtering.
+Foreign key indexes on `ticket.trip_id`, `trip.route_id`, and `route.{from_location_id, to_location_id, transport_organizer_id}`.
 
 === Conclusion (Rewritten SQL) ===
@@ -381 +381 @@
 Replace cross join with `stats` by using a window function (`AVG() OVER ()`) to avoid scanning `destinations` twice.
 Indexing opportunities:
-   - `ticket(gets_off_location_id, date_purchased)`
-   - `student_ticket.ticket_id`, `child_ticket.ticket_id`
-   - Foreign keys on `trip.route_id`, `trip.transport_organizer_id`.
+ `ticket(gets_off_location_id, date_purchased)`
+ `student_ticket.ticket_id`, `child_ticket.ticket_id`
+ Foreign keys on `trip.route_id`, `trip.transport_organizer_id`.
 
 === Conclusion (Rewritten SQL) ===
@@ -474 +474 @@
 The `max_tickets` CTE is redundant unless we need to filter only top seller(s).
 Indexing opportunities:
-   - `ticket.trip_id`, `trip.route_id`, `ticket.payment_id`
-   - Foreign keys: `route.from_location_id`, `route.to_location_id`, `route.transport_organizer_id`
+`ticket.trip_id`, `trip.route_id`, `ticket.payment_id`
+ Foreign keys: `route.from_location_id`, `route.to_location_id`, `route.transport_organizer_id`
 
 === Conclusion (Rewritten SQL) ===